Servers

    Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

    Language:
    English
    This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique.
    • Note:There are multiple files available for this download.Once you click on the "Download" button, you will be prompted to select the files you need.
      Version:

      1.0

      File Name:

      Many-to-ManyQryOptTech.docx

      SalesReasonMatrixLoad_SSIS.zip

      Date Published:

      3/29/2010

      File Size:

      691 KB

      176 KB

        Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group).
    • Supported Operating System

      Windows Server 2003, Windows Vista, Windows XP

        Windows 2000; Windows Server 2003; Windows Vista; Windows XP
        Microsoft Word
      • Click Save to save the document to your hard disk
        Click Open to open the document immediately

    Popular downloads

      • 01

        Microsoft® SQL Server® 2012 Express

        Microsoft® SQL Server® 2012 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

      • 03

        Microsoft® SQL Server® 2008 Management Studio Express

        Microsoft SQL Server 2008 Management Studio Express is a free, integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server, as well as combining a broad group of graphical tools and rich script editors that provide access to SQL Server to developers and administrators of all skill levels.

      • 04

        Microsoft® SQL Server® 2014 Express

        Microsoft SQL Server 2014 Express is a powerful and reliable free data management system that delivers a rich and reliable data store for lightweight Web Sites and desktop applications.

      • 05

        Microsoft Power Query for Excel

        Microsoft Power Query for Excel is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, access and collaboration.

    Loading your results, please wait...

    Free PC updates

    • Security patches
    • Software updates
    • Service packs
    • Hardware drivers

    Microsoft suggests

    Download a free trial of Windows Server 2012 R2.
    Windows Server 2012 R2 free trial
    Experience the new and enhanced capabilities.
    Free trial