Module 3: Metadata Repository Understanding Analysis Cube Storage - PDF document

Overview � Microsoft Data Warehousing Overview � Analysis Services Components Module 3: � Metadata Repository Understanding Analysis � Cube Storage Options � Client Architecture Services Architecture � Office 2000 OLAP Components � Microsoft Data Warehousing Overview Data Transformation Services DTS DTS OLTP DW Analysis OLE DB DTS DTS Analysis Services OLTP DW Clients Clients Storage Services for OLAP, Source Source Storage ADO MD Transforming and Moving Data � Scheduling DTS Tasks � � Automating OLAP Administrative Tasks Data Warehouse Storage Analysis Services OLTP DW DTS Analysis Services OLTP DW DTS DTS DTS Analysis Clients Clients Source Source Storage Storage Services Not Limited to SQL Server 2000 � � SQL Server 6.5, SQL Server 7.0, Microsoft Access 97, Microsoft Access 2000, Oracle 7.3, Oracle 8.0 � Any ODBC / OLE DB provider 1

Client Interfaces Client Applications Client OLTP DTS DW DTS Analysis OLTP DW Analysis Client Clients Clients DTS DTS Source Storage Services Source Storage Services Interfaces Interfaces � APIs: � Office 2000 � Low level: OLE DB for OLAP and OLE DB for Data Mining � Third-Party Applications � Custom Applications � High level: ADO-MD � Analysis Services Components Note: ADO = Asynchronous Analysis Services Architecture Data Objects Analysis Manager Analysis Manager � Analysis Services Architecture OLEDB for DM OLEDB for DM OLEDB for OLAP OLEDB for OLAP � Analysis Manager SQL Server SQL Server Data Data � Analysis Server Characteristics Warehouse Warehouse DSO DSO PivotTable Service PivotTable Service Application Application Other Other OLE DB OLE DB Analysis Server ADO MD ADO MD Providers Providers OLAP DM Engine Engine MOLAP MOLAP Store Store Note: MOLAP = Multidimensional OLAP; DSO = Decision Support Object Analysis Manager Analysis Server Characteristics � Application for Database Administration � OLE DB for OLAP Provider � Snap-In to MMC (Microsoft Management Console) � OLE DB Provider � Decision Support Objects � Windows 2000 and Windows NT Service Analysis Manager Custom Administration Interface DSO Analysis Server 2

� Cube Storage Options Metadata Repository � Contains All Metadata for Analysis Server � Storage Modes � By Default, an Access Database � Partitioning � msmdrep.mdb � Virtual Cubes � Can Migrate Repository to SQL Server � Linked Cubes � SQL Server 7.0 OLAP Services format � Database Architecture � SQL Server 2000 Meta Data Services repository format � Analysis Server Limits Storage Modes Partitioning MOLAP Current Year 35% agg MOLAP Prior Year 10% agg ROLAP History 0% agg Virtual Cubes Linked Cubes � Linked Cubes Characteristics � Equivalent of RDBMS View � Are based on source cubes stored on different servers � Subset of One Cube � Reference entire source cubes and not subsets � Combination of Data from Multiple Cubes � Appear as regular cubes � Virtually No Storage Consumed � Always have ROLAP storage mode with no aggregations � Linked Cubes Limitations � Can only be created with SQL Server 2000 Enterprise Actual Actual Edition � Cannot be used to write back cube data Sales Virtual Cube Sales Virtual Cube � Can only be created on Analysis Server with query Budget Budget access to source cube 3

Database Architecture Analysis Server Limits Items Limits Databases per server Unlimited � Analysis Server Contains One or More Databases Cubes per database Unlimited � Each Database Contains One or More Cubes Cubes per virtual cube 64 � Each Cube Contains One or More Partitions Dimensions per cube 128 � Each Partition Can Have a Different Storage Mode, a Different Aggregation Design, and a Different Storage Measures per cube 1,024 Location Calculated members per cube 65,535 Levels per dimension 64 Partitions per cube Unlimited � Client Architecture Intelligent Caching Query 1) January, February, and March 2000 Sales Client Client � PivotTable Service Client Query 2) Quarter 1 2000 Sales 1) January, February, � PTS Architecture March 2000 Sales Calculates 2) Quarter 1 2000 Sales � Intelligent Caching 3) Quarter 1 1999 Sales Query 3) Quarter 1 2000 Sales � Multidimensional Expressions and Quarter 1 1999 Sales � Internet Support on Clients Server Server Only Quarter 1 1999 1) January, February, March 2000 Needed From Sales Server 3) Quarter 1 1999 Sales Multidimensional Expressions Internet Support on Clients � Used as the Syntax for Modeling and Querying an OLAP � Uses IIS (Internet Information Server) to Provide Database Authentication Over the Internet � Supported by PTS � Uses HTTP to Pass Through Firewalls � Part of the OLE DB for OLAP API � Supports Automatic Setup through ASP � Used to Create Calculated Members � Requires SQL Server 2000 Enterprise Edition � Key to Advanced Analytical Capabilities of Analysis Services 4

Office 2000 OLAP Components Review � Microsoft Data Warehousing Overview � Excel PivotTables � Analysis Services Components � Local Cubes � Metadata Repository � Web Pivot Control � Cube Storage Options � Client Architecture � Office 2000 OLAP Components 5