1 / 24

Business Intelligence Fundamentals: Unified Dimensional Model

Business Intelligence Fundamentals: Unified Dimensional Model. Ola Ekdahl IT Mentors. Reviewing OLAP Fundamentals Understanding Database Components Programming SSAS. Agenda. Where Are We?. Data Sources. Data Marts. Staging Area. Manual Cleansing. Data Warehouse.

chester-andrews
Download Presentation

Business Intelligence Fundamentals: Unified Dimensional Model

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. Business Intelligence Fundamentals: Unified Dimensional Model Ola Ekdahl IT Mentors

  2. Business Intelligence Fundamentals: Unified Dimensional Model Reviewing OLAP Fundamentals Understanding Database Components Programming SSAS Agenda

  3. Where Are We? Data Sources Data Marts Staging Area Manual Cleansing Business Intelligence Fundamentals: Unified Dimensional Model Data Warehouse

  4. Business Intelligence Fundamentals: Unified Dimensional Model Reviewing OLAP Fundamentals • Introduction to OLAP • Business Scenarios • SQL Server 2008 Analysis Services • Unified Dimensional Model (UDM) • Benefits of the UDM

  5. Business Intelligence Fundamentals: Unified Dimensional Model Introduction to OLAP • Contains structures optimized for rapid ad hoc information retrieval • Includes a calculation engine for fast, flexible transformation of base data • Supports discovery of business trends and statistics not directly visible in data warehouse queries • OLAP = OnLine Analytical Processing

  6. Business Intelligence Fundamentals: Unified Dimensional Model What sales did we expect to achieve in North America for CY 2004 Q1? Querying the Cube 5,005,000

  7. Business Intelligence Fundamentals: Unified Dimensional Model Business Scenarios • Sales analysis • Budgeting and forecast collection and reporting • Financial reporting • Web statistics analysis • Survey results analysis • ETL process analysis Can apply to any scenario involving aggregated data that needs to be queried fast and flexibly at different levels of summarization

  8. Business Intelligence Fundamentals: Unified Dimensional Model SQL Server 2008 Analysis Services • OLAP component • Aggregates and organizes data from business data sources • Performs calculations difficult to perform using relational queries • Supports advanced business intelligence, such as Key Performance Indicators • Data mining component • Discovers patterns in both relational and OLAP data • Enhances the OLAP component with discovered results

  9. Business Intelligence Fundamentals: Unified Dimensional Model Unified Dimensional Model (UDM) • Provides a bridge between the end user and the data sources • Consolidates business rules into a single model • Challenges traditional models • Commonly called a Cube in SSAS 2000

  10. Business Intelligence Fundamentals: Unified Dimensional Model Benefits of the UDM • Permits extensive enhancements to the user model • Provides high-performance queries supporting interactive analysis, even over large volumes of data • Supports richer analysis by encapsulating business rules • Can abstract multiple data sources • Supports “closing the loop,” letting users act on data

  11. Business Intelligence Fundamentals: Unified Dimensional Model Building an Analysis Services Database DEMO

  12. Business Intelligence Fundamentals: Unified Dimensional Model Understanding Database Components • Data Source • Data Source View • Cube • Dimensions • Calculations • Key Performance Indicators

  13. Business Intelligence Fundamentals: Unified Dimensional Model Data Source • Stores a connection string for a data source • OLE DB Provider • Managed .NET Provider • Supports SQL Server and other popular databases

  14. Business Intelligence Fundamentals: Unified Dimensional Model Data Source View • Metadata definition of schema elements • Invisible source for client applications • Benefits • Focuses on a subset of tables in the data source(s) • Allows integration of multiple sources • Requires only read access to the underlying definitions and data • Enables designing when disconnected from the data source(s) • Insulates objects from changes in the underlying data source:

  15. Business Intelligence Fundamentals: Unified Dimensional Model Cube • Combination of measures and dimensions as one conceptual model • Measures are sourced from fact tables • Dimensions are sourced from dimension tables • Rich data model enhanced by • Calculations • Key Performance Indicators (KPIs) • Actions • Perspectives • Translations • Partitions

  16. Business Intelligence Fundamentals: Unified Dimensional Model Dimensions • Create members from one or more tables or views in a data source view • Contain attributes that correspond to columns in the dimension table • Organize attributes as hierarchies • Attribute hierarchies contain one All level and one leaf level • User hierarchies are multi-level combinations of attributes • Can be placed in display folders

  17. Business Intelligence Fundamentals: Unified Dimensional Model Dimension Types and Characteristics • Time (Server time dimension) • Dimension membership is based on a time range, not a dimension table • Useful when basing the cube on an OLTP database • Special: Account, Currency • Manages aggregation behavior across accounts over time • Adds currency conversion capabilities to a cube • Characteristics • Parent-child • Write-enabled • Linked

  18. Business Intelligence Fundamentals: Unified Dimensional Model Dimension Relationships • Define interaction between dimensions and measure groups • Relationship types • Regular • Reference • Fact (Degenerate) • Many-to-many • Data mining

  19. Microsoft Developer & Platform Evangelism Business Intelligence Fundamentals: Unified Dimensional Model Working with Dimensions DEMO

  20. Business Intelligence Fundamentals: Unified Dimensional Model Calculations • Expressions evaluated at query time for values that cannot be stored in fact table • Types of calculations • Calculated members • Named sets • Scoped assignments • Calculations are defined using MDX • MDX = MultiDimensionalEXpressions

  21. Business Intelligence Fundamentals: Unified Dimensional Model Programming SSAS • SSAS OLAP Programmability • SSAS Application Programming Interfaces

  22. Business Intelligence Fundamentals: Unified Dimensional Model SSAS OLAP Programmability C++ App VB App .NET App Any App OLE DB ADO ADOMD.NET AMO Any Platform, Any Device WAN XMLA Over TCP/IP XMLA Over HTTP Analysis Server OLAP Data Mining Server ADOMD.NET Data Mining Interfaces .NET Stored Procedures Microsoft Algorithms Third-Party Algorithms

  23. Business Intelligence Fundamentals: Unified Dimensional Model SSAS Application Programming Interfaces • AMO (Analysis Management Objects) • .NET based and suitable for managed code programs • Develop management applications to administer database objects, security, processing, etc. • ADOMD.NET • .NET based and suitable for managed code programs • Extension of ADO.NET • Connect to SSAS databases to retrieve and manipulate data • Server ADOMD.NET • Extend MDX with .NET stored procedures

  24. Business Intelligence Fundamentals: Unified Dimensional Model Resources • SQL Server 2008 Books Online – Analysis Services, technet.microsoft.com/en-us/library/bb522607(SQL.100).aspx • www.microsoft.com/sql/technologies/analysis • Links to technical resources, case studies, news, and reviews • www.mosha.com/msolapwww.sqljunkies.com/weblog/mosha • MoshaPasumansky is the development lead for the Microsoft Analysis Services engine

More Related