Module 10 Implementing a Tabular Data Model with

Module 10 Implementing a Tabular Data Model with Microsoft Power. Pivot

Module Overview • Introduction to Tabular Data Models and Power. Pivot Technologies • Creating a Tabular Data Model by Using Power. Pivot for Excel • Sharing a Power. Pivot Workbook and Using Power. Pivot Gallery

Lesson 1: Introduction to Tabular Data Models and Power. Pivot Technologies • What Is a Tabular Data Model? • Options for Creating Tabular Data Models • Power. Pivot Technologies • Installing Power. Pivot for Excel • Installing Power. Pivot for Share. Point

What Is a Tabular Data Model? • An in-memory database that uses x. Velocity in-memory technologies • Based on the widely understood relational model • Quick and easy to create • Faster time to deployment • Easier to learn than multidimensional models, so has a lower barrier to entry • Scalability from desktop BI to organizational BI x. Velocity

Options for Creating Tabular Data Models • Tabular Data Models in Power. Pivot for Excel § Create a tabular data model in a Microsoft Excel workbook § Importing data automatically creates a tabular data model § The data is stored in the Excel workbook • Tabular Data Models in Microsoft SQL Server 2012 Analysis Services § Create a tabular data model by using SQL Server Data Tools § The data is stored in SQL Server 2012 Analysis Services § There additional features to support larger, more complex solutions: • Row-level security • Direct. Query mode • Partitioning • Deployment options

Power. Pivot Technologies • Power. Pivot for Excel § Sophisticated desktop data analysis solution § Increased autonomy for information workers § Fast query response times § DAX for custom measures and calculated columns § Diagram view for management of tables and relationships § Hierarchies and perspectives • Power. Pivot for Share. Point § Portal for sharing and collaboration § Gallery to browse and access workbooks and reports § Server-side processing enables users to open workbooks in a browser § Central management and security for workbooks

Installing Power. Pivot for Excel • Prerequisites § Microsoft Excel 2010 § Microsoft. NET Framework 4 § Microsoft Visual Studio 2010 Tools for. NET Runtime • Automatically upgrade existing Power. Pivot for Excel workbooks

Installing Power. Pivot for Share. Point • Prerequisites § Microsoft Share. Point Server 2010 Enterprise § Share. Point Server 2010 Service Pack 1 • Considerations § Server must be a domain member § Only one Power. Pivot for Share. Point instance per server § You must configure the server after installation § You might need to configure Kerberos for some implementations

Lesson 2: Creating a Tabular Data Model by Using Power. Pivot for Excel • The Power. Pivot Ribbon • Importing Tables from a Data Source • Refreshing Data • Adding Linked and Non-Linked Tables • Creating and Managing Table Relationships • Hierarchies • Using Pivot. Tables and Pivot. Charts • Using Slicers

The Power. Pivot Ribbon • Added to Excel on installation of Power. Pivot for Excel add-in • Makes Power. Pivot features available in Excel worksheets • Power. Pivot window enables management of data connections, tables, measures, relationships, hierarchies, and perspectives

Importing Tables from a Data Source • Create data source connections in Excel Power. Pivot window • Use a wide range of connection options including common thirdparty databases • Automatically add related tables • Filter out columns that are not required for analysis: § Improves Power. Pivot performance § Simplifies user experience • Provide table aliases for ease of use

Demonstration: Creating a Power. Pivot Workbook In this demonstration, you will see how to: • Create a Power. Pivot workbook • Create a Pivot. Table from a Power. Pivot data model

Refreshing Data • Refresh data as source data changes • Ensure up-to-date analysis • Refresh all data or individual tables

Adding Linked and Non-Linked Tables • Linked tables § A table in Power. Pivot linked to a table in an Excel worksheet § The table in Power. Pivot updates automatically as the linked table in Excel changes • Non-linked tables § You copy a range of cells in an Excel worksheet § You use the Paste To New Table option to add the table to the Power. Pivot window § The table does not update automatically if the data in the worksheet changes

Creating and Managing Table Relationships • Automatically recognize relationships based on foreign keys • Manually create relationships when they are not explicitly defined

Hierarchies • Create hierarchies in a Power. Pivot table by using the diagram view • Add hierarchies to Pivot. Table tables in Excel worksheets Product Category Product Subcategory Product Country State City

Demonstration: Creating a Hierarchy In this demonstration, you will see how to: • Create a hierarchy

Using Pivot. Tables and Pivot. Charts • Pivot. Table tables show key measures: § Display data at multiple levels of granularity § Use formatting options for emphasis • Pivot. Chart charts provide a visually intuitive breakdown of data

Using Slicers • Filter data at the click of a button: § Power. Pivot calculates new values § Pivot. Table tables and Pivot. Chart charts update automatically • Connect slicers to one or more Pivot. Table tables or Pivot. Chart charts • Use vertical or horizontal slicers to suit layout

Demonstration: Creating a Pivot. Table and a Slicer In this demonstration, you will see how to: • Create a Pivot. Table table • Create a slicer

Lesson 3: Sharing a Power. Pivot Workbook and Using Power. Pivot Gallery • Sharing Power. Pivot for Excel Workbooks • Using Power. Pivot Gallery

Sharing Power. Pivot for Excel Workbooks • Upload Power. Pivot for Excel workbooks to Power. Pivot Gallery on Share. Point: § Browse workbooks and reports in the gallery § View them in Windows Internet Explorer § Open them in Excel for further analysis • Use uploaded workbooks as data sources for Excel

Using Power. Pivot Gallery • Shows thumbnail previews of Power. Pivot workbooks • Offers different viewing options: § Gallery § All Documents § Theater § Carousel • Click a workbook to open it in Internet Explorer

Demonstration: Sharing a Power. Pivot for Excel Workbook to Power. Pivot Gallery In this demonstration, you will see how to: • Share a Power. Pivot for Excel workbook to Power. Pivot Gallery

Lab Scenario Some senior business analysts at Adventure Works Cycles wants to analyze data for reseller sales in Microsoft Excel. These experienced employees are experienced users and should be able to create their own analytical models, without relying on the IT department creating a cube. You plan to empower these users to use Power. Pivot to create and share tabular data models in Excel.

Lab 10: Using Power. Pivot for Excel • Exercise 1: Creating a Tabular Data Model by Using Power. Pivot for Excel • Exercise 2: Using a Tabular Data Model in Excel • Exercise 3: Sharing a Power. Pivot Workbook to Power. Pivot Gallery • Exercise 4: Using a Power. Pivot Workbook as a Data Source Logon information Virtual machine MIA-SQLBI User name ADVENTUREWORKSStudent Password Pa$$w 0 rd Estimated time: 60 minutes

Module Review and Takeaways • What are the differences between multidimensional data models and tabular data models? Why might you choose to create one rather than the other? • What extra functionality does a SQL Server Analysis Services tabular data model offer over a Power. Pivot for Excel tabular data model? • How can information workers use the Power. Pivot workbooks that individuals upload to Power. Pivot Gallery?