1 / 31

SQL Server Accelerator for Business Intelligence (SSABI)

SQL Server Accelerator for Business Intelligence (SSABI). Deploying an Analytical Application. The Benefits Improved Information-Sharing and Decision-Making Higher Revenues and Lower Costs The Challenges Long Development Cycles Extensive Customization for Packaged Solutions.

umeko
Télécharger la présentation

SQL Server Accelerator for Business Intelligence (SSABI)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Accelerator for Business Intelligence(SSABI)

  2. Deploying an Analytical Application • The Benefits • Improved Information-Sharing and Decision-Making • Higher Revenues and Lower Costs • The Challenges • Long Development Cycles • Extensive Customization for Packaged Solutions

  3. Understanding the Benefits of the Accelerator for BI • Fast Deployment • Extensibility • Cost-Effectiveness • Flexibility • Reusability • Prescriptive Guidance

  4. Mapping the Analytical Application Development Process Develop Project Plan Gather Business Requirements Select Clients Select Data Model Configure Client Views Customize Data Model Configure Analytics Builder Workbook Create Analytical Application Load and Process Data Evaluate Deploy

  5. Introducing Components of the Accelerator for BI • Reference Data Models • Client Views • Analytics Builder Workbook

  6. Reference Data Models • Sales and Marketing Analytics • Retail Analytics • Additional Reference Data Models

  7. Client Views • Customizable Templates • Solution-specific KPIs • Report Collections by Business Role • Multiple Client Tools • ProClarity Analytic Platform 4.0 • Open API Supported by Many Third-Party Vendors

  8. Introducing the Analytics Builder Workbook • Front End to Analytics Builder • Data Model Design Interface • Hosted by Excel • Intended for a Single User • Design Features • Standard Data Model • One Worksheet per Design Component • Custom Toolbar • Data Entry Validation Routines

  9. Defining the Logical Architecture • Architecture Overview • Client Views • Analysis Services Database • SQL Server Databases • DTS Packages

  10. DTS DTS DTS Analytical Application Architecture Overview Source Systems Staging Db UI KPI’s Cubes ETL OLEDB for OLAP Subject MatterDb

  11. Client Views • Dynamic Configuration • Automatic Generation • Optional Usage • Extensible Content

  12. Analysis Services Database • OLAP Cubes • Facilitates Slice and Dice • Very Fast Query Performance • Predefined Structure • Supports the Client Views • Customizable

  13. SQL Server Databases – Subject Matter • Fact and Dimension Tables Support Cubes • Slowly Changing Dimension Support

  14. SQL Server Databases - Staging • Tables Support Fact and Dimension Loads • Stored Procedures for ETLM

  15. DTS Packages • Load Staging Database • Preconfigured Bulk Insert Package • Requires Cleansed and Organized Source Data • Load Subject Matter Database • Management of Inserts, Updates and Deletes • Supports Slowly Changing Dimensions • Process OLAP Database • Management of Updates to Dimensions and Cubes • Implements Monthly Partitioning

  16. Installing the Accelerator for BI • Minimum Requirements • Installation Procedures

  17. Minimum Requirements Hardware • Intel Pentium 166 Mhz • 256 MB RAM • 3 GB hard disk • VGA monitor • Mouse Software • Microsoft Windows 2000, any edition (SP2) or Microsoft Windows XP • Microsoft SQL Server 2000, Enterprise Edition or Developer Edition (SP2) • Microsoft SQL Server 2000 Analysis Services, Enterprise Edition or Developer Edition (SP2) • Microsoft Excel 2002 (Office XP) • Microsoft Word 2002 • Office XP SP1 • Windows Scripting Host version 5.6

  18. Installation Procedures • Installing the Software • Use Local Administrator Credentials • Start SQL Server and MSSQLServerOLAPService • Launch the Executable • Configuring Permissions on Folders and File Shares • Set Permissions for SQL Server Service • Set Permissions for Developer Accounts

  19. Using the Analytics Builder Workbook • Databases • Time • Dimensions • Levels • Member Properties • Virtual Dimensions • Physical Cubes • Measures • Virtual Cubes • Calculated Members • Advanced Sheets • Mappings

  20. Databases Subject Matter Database Name Analysis Database Name • Server Selection – Local vs. Remote • Database Naming Rules • Connectivity Testing Staging Db Cubes <DB id>_Staging <DB id> <ADB id>

  21. Time • Schema and Member Values Generated Automatically • Primary Key of All Time Level Tables is SmallDateTime • All Time Hierarchies Based on Dim_Time_Day Table

  22. Schema Object Attributes • ID • Short code for the object • Used in creating relational schema names • Name • User-friendly name for the object • Used in creating OLAP schema names • Label • Longer description of the object • Used in the description property

  23. Dimensions • Unique IDs and Names for Dimensions and Hierarchies Required • One Row Per Hierarchy • Standard, Flat or Parent-Child Dimension Structure • Name of “All” Member • Changing Type • Track History • Restate History • Restate History Seldom / Often

  24. Hierarchical Levels • Unique IDs and Names for Levels Required Within a Dimension Hierarchy • One Row Per Level in Proper Sequence Within Hierarchy • Two Generated Member Properties • Member code – natural key • Member label – description of member • Choose a Based On Level for Alternate Hierarchies That Share Levels

  25. Virtual Dimensions & Member Properties • Add Member Properties for Filters and Calculations • Member properties are optional • A column for each member property is added to dimension table • Specify a Virtual Dimensions for Enhanced Analysis • Any member property can be designated as a virtual dimension • A table for each virtual dimension is automatically created • Force Referential Integrity in the Relational Database • Foreign Dimension-Hierarchy • Foreign Level

  26. Physical Cubes • Unique IDs and Names for Cubes Required • Specify Automatic Monthly Partitioning • Select Dimension Granularity in Each Cube • By default, granularity is the leaf level of the hierarchy • The granularity can be any level, except the “All” level • The cube’s schema is automatically structured to remove lower levels

  27. Measures • Unique IDs and Names for Measures Required • This prevents naming conflicts in virtual cubes • This applies to both real and calculated measures • Specify Data type and Format • “Count” measures are created only in analysis database • “DegenerateDim” measures are created only in the relational databases • Specify if Measure will be Visible in Cube

  28. Virtual Cubes • Unique IDs and Names for Virtual Cubes Required • Inheritance • All dimensions of component cubes • All measures of components cubes • - Physical • - Calculated • If a level is disabled in each of the component cubes, the level is hidden in the virtual cube

  29. Calculated Members • Unique IDs and Names for Calculated Members Required • Apply a Calculated Member to a Specific Physical Cube, or a Virtual Cube • MDX is Not Validated in the Worksheet

  30. Advanced Sheets • Worksheets for Advanced Functionality • Named Sets • Actions • Calculated Cells • Validate Syntax Before Adding to Worksheet

  31. Mappings • You use this sheet to: • Map Default Values to Renamed Components • Remove Default Values for Deleted Components • Analytics Builder uses this sheet to: • Build XML Mapping for Client Generators

More Related