Creating Pivot Tables and Diagrams withMicrosoft Excel, Visio and SQL Server 2008CIS 3730Designing and Managing DataJ.G. ZhengFall 20101

OverviewUsing Excel 2007 to created a pivottable and chart based on SQL ServerdatabasesUsing Visio 2010 to created a pivotdiagram based on SQL Server databases2

SQL Server 2008 DatabaseDownload and attach the “miniDW”sample database from the courseschedule website Don’t forget to “Run as Administrator”when opening the Management StudioA view of aggregation results withgroups needs to be created first3

Sample Database StructureDimensiontableFact tableA measure“SalesAmount”4

The View with AggregationFact table in the centerCheck this button tobring out aggregationsettings.Create a view ofaggregation with groups.One such view alreadyexists in the sampledatabase. You can alsocreate additional views.“Sum” on themeasure column;and “Group By” onchosen dimensions.5

Complete SQL for the ViewSELECT SUM(dbo.SalesFact.SalesAmount) AS [Total Sales],DimDate.TimeYear, DimDate.TimeQuarter,DimDate.TimeMonth, DimProduct.Category,DimProduct.Brand, DimLocation.Region, DimLocation.StateFROM SalesFact INNER JOIN DimProduct ONSalesFact.ProductKey DimProduct.IDINNER JOIN DimLocation ONSalesFact.LocationKey DimLocation.IDINNER JOIN DimDate ONSalesFact.TimeKey DimDate.IDGROUP BY DimDate.TimeMonth, DimDate.TimeYear,DimDate.TimeQuarter, DimProduct.Brand,DimProduct.Category, DimLocation.Region, DimLocation.State;6

Excel 2007 Pivot Table and ChartExcel has rich analysis tools, and can be used as an dataanalysis front end to SQL Server databasesTask Creating a pivot table based on the “miniDW” databaseMore about pivot tables in Excel Z010205886.aspx7

Start a PivotTable TaskGo to the“Insert” tab andselect PivotTableChoose “external datasource” and click thebutton. Then skip toslide #11.8

Another Way to Select a Data SourceTo use an existing datasource, select “ExistingConnections”. Then goto slide #11.Access can also beused as a datasource.Go to the “Data” tab,select “Get ExternalData”. To create a newSQL Server data source ,choose “From SQLServer” and follow thesetup in slide #10.9

Create a New Data SourceSelect thedatabaseServer nameSelect the viewjust created.10

Import TableChoose an existingconnectionChoose PivotTable(or PivotChart)To create a new connection,click this button.11

Pivot Table DesignThese are thecolumns fromthe view.Click any where within thedesign panel to bring upthe setting panel on theright.This is for thechart, optional.12

More optionsand designsettings here.Drag ColumnsColumn LabelsFormat them toa money styleDrag a dimensioncolumn to “columnLabels” or “row labels”Row LabelsDrag the measure“Total Sales” here.Total sales by thechosen dimensions13

Drilling Up/DownClick on theseexpand/collapsebutton to adjust theview. A pivot table isnow ready. Changerow or column labelsto generate differenttotals and views.Putting more than one dimensioncolumns will arrange the data inhierarchical grouping levels,which enables drilling up/down.14

Pivot Diagram in Visio 2010Visio can also connect to the SQL Serverand display the pivot table in a dynamicdiagramTask Creating a pivot diagram based on the“miniDW” databaseFor more about the pivot diagram, visit -a-pivotdiagram-HA010357089.aspx15

Create a PivotDiagramChoose fromthe “Business”template groupand you will see“PivotDiagram”16

Connecting to the SQL ServerSelect this one tocreate a newconnection. Seeslide #10 and thengo to the next slide.Select an existingconnection. Goto the next slide.17

Choose a ConnectionSelect an existingconnection.Use all columnsand data18

Design PivotDiagramClick on the shape and thenchoose a dimension to addunder the root shape.The grand total isput here for you.For the “Total”, checkthe “Total Sales” only.19

PivotDiagramSubtotals by “Category”are displayed20

Adding More DimensionsDrag the “Pivot Node”shape to start anotherdifferent tree.Add more branches to seemore subtotals (drilldown). A pivot diagram isnow ready.21

What’s Next?In this tutorial, we only connect to the SQLServer database engine to get data fromviews.SQL Server 2008 Analysis Service providesadvanced and optimized OLAP services. Business Intelligence Development Studio willbe used to create such services.Excel and Visio can connect to SSAS and usethe OLAP cube.See separate tutorials and lectures.22

Excel 2007 Pivot Table and Chart Excel has rich analysis tools, and can be used as an data analysis front end to SQL Server databases Task Creating a pivot table based on the “miniDW” database More abou