Analysis Services en SQL Server 2008
16 likes1,920 views
The document provides an overview of analysis services in SQL Server 2008, focusing on data warehouse design, OLAP solutions, and data mining techniques. It discusses the structure of star and snowflake schemas, the components of fact and dimension tables, and the functionality of OLAP cubes for multidimensional analysis. Additionally, it covers enhancements for data mining and the use of Microsoft Office add-ins for improved data analysis and visualization.
Analysis Services en SQL Server 2008
- 1. Analysis Services in SQL Server® 2008Eduardo Castro SQL MVP,MCDBA , MCSE, MCAD, MCSD [email protected] Windows Costa Rica
- 2. Data Warehouse System ComponentsData WarehouseUser Data AccessData SourcesStagingAreaData Marts Data InputData Access
- 3. Understanding Data Warehouse DesignThe Star SchemaFact Table ComponentsDimension Table CharacteristicsThe Snowflake Schema
- 4. The Star SchemaEmployee_DimEmployeeKeyEmployeeID...Product_DimTime_DimProductKeyTimeKeyProductID...TheDate...Shipper_DimCustomer_DimShipperKeyCustomerKeyShipperID...CustomerID...Dimension TableFact TableSales_FactTimeKeyEmployeeKeyProductKeyCustomerKeyShipperKeySales AmountUnit Sales ...
- 5. Fact Table ComponentsMeasurestime_dim134 1/1/2000DimensionTablessales_fact Tablecustomer_dimForeign Keys201 ALFI Alfredscustomer_keyproduct_keytime_keyquantity_salesamount_salesproduct_dim2012513440010,789 25 123 ChaiThe grain of the sales_fact table is defined by the lowest level of detail stored in each dimension
- 6. Dimension Table CharacteristicsDescribes Business EntitiesContains Attributes That Provide Context to Numeric DataPresents Data Organized into Hierarchies
- 7. The Snowflake SchemaDefines Hierarchies by Using Multiple Dimension TablesIs More Normalized than a Single Table DimensionIs Supported within Analysis Services
- 8. Defining OLAP SolutionsOLAP Databases Common OLAP ApplicationsRelational Data Marts and OLAP CubesOLAP in SQL Server 2005
- 9. OLAP DatabasesOptimized Schema for Fast User QueriesRobust Calculation Engine for Numeric AnalysisConceptual, Intuitive Data Model Multidimensional View of DataDrill down and drill upPivot views of data
- 10. OLAP in SQL ServerMicrosoft Is One of Several OLAP VendorsAnalysis Services Is Bundled with Microsoft SQL Server 2005 and SQL Server 2008Analysis Services Include OLAP engine Data Mining technology
- 12. Cube MeasuresAre the Numeric Values of Principle InterestCorrespond to Fact Table FactsIntersect All Dimensions at All LevelsAre Aggregated at All Levels of Detail
- 13. Relational Data SourcesStar and Snowflake Schemas Are required to build a cube with Analysis ServicesFact TableContains measuresContains keys that join to dimension tablesDimension TablesMust exist in same database as fact tableContain primary keys that identify each member
- 14. Applying OLAP CubesDefining a CubeQuerying a CubeDefining a Cube SliceWorking with Dimensions and HierarchiesVisualizing Cube DimensionsConnecting to an OLAP Cube
- 15. Defining a CubeAtlantaChicagoMarket DimensionDenverGrapesCherriesDetroitMelonsApplesProducts DimensionQ4Q1Q2Q3Time Dimension
- 17. SQL Server 2008 Analysis Services EnhancementsMultidimensional Analysis with SQL Server Analysis ServicesData Mining with SQL Server Analysis Services
- 18. Multidimensional Analysis with SQL Server Analysis ServicesCube WizardDimension WizardThe Attribute Relationship DesignerThe Aggregation DesignerAMO Warnings
- 19. Cube WizardMore efficient interfaceCreate a cube based on a single de-normalized tableCreate a cube based on a data source that has only linked dimensions
- 20. Dimension WizardCreate dimensions more efficientlyAutomatically detect parent-child hierarchiesProvide safer default error configurationSet member properties while creating the dimension
- 21. The Attribute Relationship DesignerFlexible relationshipRigid relationshipManage Relationship through right-click options in the Attribute Relationship pane
- 22. The Aggregation DesignerNew Aggregation Designer:Aggregations designs are shown grouped by measure groupNew view available for manual aggregation designImproved Usage-Based Optimization Wizard:Ability to append new aggregations to an existing aggregation designAbility to modify storage settings for one or more partitions simultaneouslyImproved Aggregation Design Wizard
- 23. AMO WarningsUses familiar symbols such as the wavy underline and a yellow triangle with an exclamation pointNon-intrusive warning messages appear when you pause the mouse over a warningAvailable for logical errors in database design, when users depart from design best practices, and for non-optimal aggregation designs
- 24. Data Mining with SQL Server Analysis ServicesImproved Data Mining WizardSeparating Test and Training DataFiltering Model CasesCross Validation of Mining ModelsData Mining Add-Ins for Microsoft Office Systems
- 25. Data Mining EnhancementsImprove both short-term and long-term predictions with the Microsoft Time Series algorithm, which has been enhanced to support both ARTXP and ARIMA algorithmsEnable drillthrough on a mining structure to allow queries about the cases used for both training and testingCreate column aliases to make it easier to understand column content. In the Data Mining Designer, the alias appears in parentheses next to the column usage labelSeparate test and training dataImprove performance and analyze different scenarios by using Model Case FilteringCreate cross-validation reportsUtilize Data Mining Add-ins for Office
- 26. Separating Test and Training DataThree ways to partition data into training and test sets:The Data Mining WizardModifying the properties of the mining structure:If you did not create a test partition when you created the structure, you can modify the HouldoutMaxCases, HoldoutMaxPercent, and HoldoutSeed propertiesDMX statement, AMO, or XML DDL
- 27. Filtering Model CasesLimit the cases used in a model based on any attribute included in the model
- 28. Use to compare subsets of your data such as different regionsCross-Validation of Mining ModelsAdditional method to test and view mining model accuracyData is partitioned into cross-sections which are used to train and test models against each of the other cross sectionsOne portion of the data is used to test, the remaining data is used to train the modelTo define a cross-validation report, you must configure:The Fold Count, which specifies the number of folds or partitions that the data is broken into for testingThe Max Cases, which specifies the maximum number of cases to use (a value of 0 specifies that all cases will be used)The Target Attribute, which defines the column or attribute that you want to predictThe Target State, which defines a particular value that you want to analyze within the target attribute
- 29. Data Mining Add-Ins for Microsoft Office SystemData Mining Client for Excel – create, test, and manage data mining projects within Excel 2007Table Analysis Tools for Excel – use powerful analysis tools such as analyzing key influencers, highlighting exceptions, and forecasting for data stored in spreadsheetsData Mining Templates for Visio – render decision trees, regression trees, cluster diagrams, and dependency nets in diagrams created in Visio 2007Important: Data Mining Add-ins for Microsoft Office System will be available for SQL Server 2008 when it releases to manufacturing.
- 31. Optimizing StorageWhat Are Sparse Columns?How to Compress Data and Backups
- 32. What Are Sparse Columns?Eliminate limitation of 1024 columns.Efficient way to manage object models that frequently contain numerous NULL values.CREATE TABLE products (product_numint, item_numint, price decimal(7,2), ..., color char(5) SPARSE, width float SPARSE...)
- 33. How to Compress Data and BackupsData compression can be enabled on tables or viewsDifferent compression types can be configured on a per-partition basisThe following data compression types can be defined:Row compressionPage compressionBackup compression:Normally decreases time required to perform a backupManaged with Transact-SQL, Backup Task, Maintenance Plan Wizard, and Integration Services Backup Database task