Construindo cubos e relatórios

Data Warehousing if696 - Aula prática #3

Transcript

1. Construindo cubos e relatórios Ana Carolina Salgado Fábio Ávila
2. Using Excel As a Data Analysis Tool • Connect to Analysis Services databases • Create PivotTable tables and PivotChart charts • PowerPivot for Excel add-in extends the capabilities of Excel  Stores data locally as a tabular data model  Minimal memory footprint due to VertiPaq storage  Provides fast response times for very large data sets • PowerPivot for SharePoint enables users to collaborate on PowerPivot for Excel workbooks and share them
3. The PowerPivot Ribbon • Added to Excel on installation of PowerPivot for Excel add-in • Makes PowerPivot features available in Excel worksheets • PowerPivot window enables management of data connections, tables, measures, relationships, hierarchies, and perspectives
4. What Is a Tabular Data Model? • An in-memory database that uses xVelocity 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 xVelocity
5. Options for Creating Tabular Data Models • Tabular Data Models in PowerPivot 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 are additional features to support larger, more complex solutions: • Row-level security • Partitioning • DirectQuery mode • Deployment options
6. PowerPivot Technologies • PowerPivot 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 • PowerPivot for SharePoint  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
7. Importing Tables from a Data Source • Create data source connections in Excel PowerPivot window • Use a wide range of connection options including common third- party databases • Automatically add related tables • Filter out columns that are not required for analysis:  Improves PowerPivot performance  Simplifies user experience • Provide table aliases for ease of use
8. Using PivotTable Tables and PivotChart Charts • PivotTable tables show key measures:  Display data at multiple levels of granularity  Use formatting options for emphasis • PivotChart charts provide a visually intuitive breakdown of data
9. Using Slicers • Filter data at the click of a button:  PowerPivot calculates new values  PivotTable tables and PivotChart charts update automatically • Connect slicers to one or more PivotTable tables or PivotChart charts • Use vertical or horizontal slicers to suit layout
10. Using PowerPivot Gallery • Shows thumbnail previews of PowerPivot workbooks • Offers different viewing options:  Gallery  All Documents  Theater  Carousel • Click a workbook to open it in Internet Explorer
11. Data Sources • SQL Server Analysis Services data sources define connections to databases that contain data for the cube • Typically, a data warehouse or data mart
12. Data Source Views A data source view provides an abstraction layer that: • Combines data from one or more underlying data source • Creates a tabular model for cube measures and dimensions • Enhances the underlying data schemas with friendly names, queries, and calculated columns
13. Creating a Data Source • Use the Data Source Wizard in SQL Server Data Tools
14. Creating a Data Source View  Use the Data Source View Wizard in SQL Server Data Tools
15. Modifying a Data Source View  Create user-friendly names  Use named queries  Create relationships to improve performance
16. The Dimension Designer Add attributes by dragging from Data Source View Create hierarchies by dragging attributes here Right click dimension to view designer Browse dimension
17. Configuring Dimension Attributes • Dimension attributes can be set with a wizard or the Dimension Designer • Attributes can be renamed and organized into folders • Attributes can be used to provide detail or form hierarchies
18. What Are Hierarchies? • Dimensions are typically arranged into hierarchies • Attributes in natural hierarchies have a relationship (for example Year, Quarter, Month in the screenshot) • Attributes in non-natural hierarchies do not have a relationship (for example Marital Status – Gender)
19. Parent-Child Hierarchies  Self-referencing dimension  Almost always unbalanced  Can be write-enabled  Structure is calculated at run-time EmployeeID Name ManagerID Position 1 Michael Allen CEO 2 Paula Bento 1 CFO 25 Rob Barker 2 Snr. Accountant
20. Attribute Relationships  Attributes are related directly or indirectly to the key attribute  Most dimensions have levels and relationships between the levels  Attribute relationships are straightforward to create in Dimension Designer
21. Grouping Attributes • Group hierarchies which have no natural levels • Group by equal numbers or cluster the members • Define the number of groups • Define a naming template
22. Introducing Measures Measures: • Are values in a fact table • Are typically numeric and aggregated • Can be calculated from other measures
23. Measure Properties Measure properties include: • How measures are aggregated • The column in the data source view to which the measure is bound • The name that is displayed • The folder in which the measure will appear when users connect to the cube • The display format • Any MDX expressions that define the measure • The visibility of the measure
24. Aggregation Functions Aggregation functions can be: • Additive • Semiadditive • Nonadditive 20,000 15,999 4,000 31,123 16,000 ?,????
25. Introducing Measure Groups • Measures groups define collections of related measures  By default, a measure group is created for each fact table • A measure group defines:  The dimensions the measures can be aggregated by  The granularity at which the measures are related to the dimensions  How measure aggregations should be stored
26. Relationships Between Measure Groups and Dimensions Regular: When the key column for a dimension is joined directly to the fact table Reference: When the key column for a dimension is joined indirectly to the fact table Fact: Standard dimensions that are constructed from attribute columns in fact tables instead of dimension tables Many-to-many: When a single fact is joined to multiple dimension members
27. Options for Creating a Cube Use the Cube Wizard to create the cube • Create dimensions in the Cube Wizard or separately • Build a cube by using an existing data source and data source view or create the database schema after cube creation • Automatically build attributes and hierarchies or define them in the Cube Designer after cube creation
28. The Cube Designer • Use the Cube Designer to:  Set and modify cube properties  Add KPIs and actions  Change cube partitioning and storage  Create aggregations  Create perspectives  Add translations  Browse cube data
29. Types of Actions • Standard Actions • Dataset • Proprietary • Rowset • Statement • URL • Reporting Actions • Drillthrough Actions
30. Perspectives
31. Translations Language Translation Original Text
32. What Is a Report? • Report Definition Language (RDL)  Body  Data Source(s)  Dataset(s) <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> … </ReportItems> </Body> <DataSources> <DataSource Name="MyDataSource"> … </DataSource> </DataSources> <DataSets> <DataSet Name="MyDataset"> … </DataSet> </DataSets> </Report> Embedded data source definition or a reference to a shared data source Embedded dataset definition or a reference to a shared dataset
33. What Are Report Parts? • Report items stored on a report server that can be reused in multiple reports: • Charts • Gauges • Images • Maps • Parameters • Rectangles • Tables • Matrices • Lists • Report parts in reports use a unique ID to maintain a relationship to the instance of the report part on the server
34. Using Report Snapshots • Create a report history of snapshots – rendered reports for specific points in time • Create snapshots manually or on a scheduled basis
35. The Report Designer Interface Report Data Pane Toolbox Report Design Surface Preview Tab Report Body Grouping Pane Properties Pane Solution Explorer
36. Working with Data Sources • Defines a connection string for data required by the report • Specifies credentials for authentication • Can be shared or embedded  Shared: The same data source can be used by multiple reports  Embedded: The data source is specific to the report  You can convert an embedded data source to a shared data source
37. Working with Datasets • Defines a query used to retrieve data from a data source • Determines the fields in the report • Can be shared or embedded  Shared: The same dataset can be used by multiple reports  Embedded: The dataset is specific to the report  You can convert an embedded dataset to a shared dataset
38. Tablix Data Regions Matrix Table List
39. Adding Groups to a Tablix Data Region • Tables have row groups. Matrices have row and column groups • Drag fields from the Report Data pane to the Grouping pane to create parent or child groups • Use the shortcut menu for groups in the Grouping pane to add parent, child, or adjacent groups
40. Displaying Aggregated Values • Add a total in the Grouping pane • Add a row or column to the group, and use an expression to display an aggregation in a text box • Specify scope to aggregate a containing group =Sum(Fields!SalesAmount.Value) =Sum(Fields!SalesAmount.Value) & " of " & Sum(Fields!SalesAmount.Value, "Product_Category")
41. Enabling Interactive Drilldown Functionality • Hide child groups • Enable display toggling based on a report item in the parent group
42. Including Images in a Report • Add an Image from the Toolbox • Set a background image for the report body or a data region  Specify BackgroundRepeat property
43. Working with Charts • Select an appropriate chart type for your data • Specify the values to be plotted • Add category groups to define the data points for the chart series • Add series groups to show multiple series
44. Showing Key Values with Gauges • Use gauges to show key performance indicators (KPIs) • Choose an appropriate gauge style • Specify the field for the pointer value  You can add multiple pointers if required • Format the range and scale  Base the maximum value on a key business measure with which you want to compare the value
45. Displaying Geographical Data with Maps  Legends and scales to help viewers interpret the map • Add a map to a report to show geographic data • A map consists of one or more map layers • Each map layer can be used to define:  Spatial features  Analytical values • Add a Bing Maps layer to show geographic details
46. Overview of Parameters • Report Parameters  Values specified when rendering a report • By a user viewing a report interactively • In a report subscription • When embedding a subreport in a report • Dataset Parameters  Used in a WHERE clause to restrict query results  Usually mapped to a report parameter
47. Configuring Parameters • Name  The name of the parameter • Prompt  The label displayed in the user interface • Data type  Text, Boolean, Date/Time, Integer, or Float • Allowable Values  Blank, null, multiple values* • Visibility  Visible, Hidden, or Internal * When using multiple values to filter data, the WHERE clause should use the IN operator. For example: WHERE YEAR(SalesOrderDate)IN (@Year)
48. Available and Default Parameter Values • None • Specify values • Get values from a query  Usually requires that you add a dataset to the report SELECT DISTINCT YEAR(OrderDate) AS [Year] FROM FactResellerSales ORDER BY [Year] DESC
49. What Is MDX? SELECT { ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2010]), ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[CY 2011]) } ON COLUMNS FROM [Adventure Works] ([Measures].[Sales Amount] - [Measures].[Total Product Cost – Fact Reseller Sales]) / [Measures].[Sales Amount] MDX expression MDX statement MDX for security
50. Basic MDX Query Syntax SELECT { ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[2010].[Q1]), ([Measures].[Reseller Sales Amount], [Date].[Calendar Year].[2011].[Q2]) } ON COLUMNS FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] ) • SELECT, FROM, and WHERE clauses • Bracketed object names, and qualified names [ WITH <SELECT WITH clause> [ , <SELECT WITH clause> ... ] ] SELECT [ * | ( <SELECT query axis clause> [ , <SELECT query axis clause> ... ] ) ] FROM <SELECT subcube clause> [ <SELECT slicer axis clause> ] [ <SELECT cell property list clause> ] [ WHERE Set_Expression ]
51. The Calculations Tab of the Cube Designer Form View Script View Script Organizer Calculation Tools Script Editor Calculated Member Form Editor
52. Calculated Members
53. Using MDX in SQL Server Management Studio
54. Using MDX in SQL Server Reporting Services