260 likes | 467 Views
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.
E N D
Business Intelligence Fundamentals: Unified Dimensional Model Ola Ekdahl IT Mentors
Business Intelligence Fundamentals: Unified Dimensional Model Reviewing OLAP Fundamentals Understanding Database Components Programming SSAS Agenda
Where Are We? Data Sources Data Marts Staging Area Manual Cleansing Business Intelligence Fundamentals: Unified Dimensional Model Data Warehouse
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
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
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
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
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
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
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
Business Intelligence Fundamentals: Unified Dimensional Model Building an Analysis Services Database DEMO
Business Intelligence Fundamentals: Unified Dimensional Model Understanding Database Components • Data Source • Data Source View • Cube • Dimensions • Calculations • Key Performance Indicators
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
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:
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
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
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
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
Microsoft Developer & Platform Evangelism Business Intelligence Fundamentals: Unified Dimensional Model Working with Dimensions DEMO
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
Business Intelligence Fundamentals: Unified Dimensional Model Programming SSAS • SSAS OLAP Programmability • SSAS Application Programming Interfaces
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
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
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