Skip to main content

Star schemas and aggregate (or summary) fact tables


Aggregate tables can further improve query performance by reducing the number of rows over which higher-level metrics must be aggregated. 
However, the use of aggregate tables with dimension tables is not a valid physical modeling strategy. Whenever aggregation is performed over fact data, it is a general requirement that tables joined to the fact table must be at the same attribute level or at a higher level. If the auxiliary table is at a lower level, fact rows will be replicated prior to aggregation and this will result in inflated metric values (also known as "multiple counting").

With the above Time dimension table, a fact table at the level of Day functions correctly because there is exactly one row in DIM_TIME for each day. To aggregate the facts to the level of Quarter, it is valid to join the fact table to the dimension table and group by the quarter ID from the dimension table.

Sql
select DT.QUARTER_ID,
   max(DT.QUARTER_DESC) Quarter_Desc
   sum(FT.REVENUE) Revenue
from DAY_FACT_TABLE FT
   join DIM_TIME DT
      on (FT.DAY_ID = DT.DAY_ID)
group by DT.QUARTER_ID

If, however, there is an aggregate fact table already at the level of Quarter, the results will not be correct. This is because the query must join on Quarter ID, but the quarter ID is not a unique key of the dimension table. Because any given quarter of a year contains 90, 91 or 92 days, the dimension table will contain that many rows with the same quarter ID. Thus fact rows will be replicated prior to taking the sum, and the sum will be too high.

Sql
select FT.QUARTER_ID,
   max(DT.QUARTER_DESC) Quarter_Desc
   sum(FT.REVENUE) Revenue
from QTR_FACT_TABLE FT
   join DIM_TIME DT
      on (FT.QUARTER_ID = DT.QUARTER_ID)
group by FT.QUARTER_ID

This is a generally recognized problem with star schemas, and is not strictly a MicroStrategy limitation.

Star schemas will function correctly with MicroStrategy SQL Generation Engine 8.x as long as they obey the general data warehousing principle that fact tables should not be at a higher level than the dimension tables to which they are joined.

If aggregate tables are required, it is necessary to provide higher-level lookup tables with unique rows corresponding to each aggregate table's key. Logical views are a way to do this without adding tables or views to the warehouse. For example, LWV_LU_QUARTER may be defined using the following SQL statement:

Sql
select distinct QUARTER_ID, QUARTER_DESC, YEAR_ID
from DIM_TIME

 

With this logical view, it becomes possible for MicroStrategy SQL Generation Engine 8.x to query the quarter-level fact table as follows. Since the logical view has distinct rows per quarter, multiple counting will not occur in this query.

Sql
select FT.QUARTER_ID,
   max(LQ.QUARTER_DESC) Quarter_Desc
   sum(FT.REVENUE) Revenue
from QTR_FACT_TABLE FT
   join (select distinct QUARTER_ID, QUARTER_DESC, YEAR_ID
         from DIM_TIME) LQ
      on (FT.QUARTER_ID = LQ.QUARTER_ID)
group by FT.QUARTER_ID

For more information on the use of logical views in MicroStrategy SQL Generation Engine 8.1.x and 9.x, consult the MicroStrategy Project Design Guide manual, Appendix B: Logical Tables, "Creating logical tables."

Aggregate tables store pre-summarized totals at a higher level of aggregation than the most granular fact table. They allow reports to be generated from small, rather than large, tables; therefore, performance is enhanced. A successful aggregation strategy seeks to choose aggregate tables that will have the most impact while taking the least amount of space.

Aggregation decisions are driven by the following factors:

  • Usage patterns: Build aggregate tables that are likely to be used the most.
  • Compression ratios: The compression ratio between two tables is defined as the size of the aggregate compared to the size of the smallest table from which the aggregate can be derived.
  • Volatility: Changes in hierarchies over time impact the accuracy of aggregate tables. Sometimes aggregate tables must be rebuilt as a result of changes in dimensions.
A good candidate for aggregation should have at most 10-15 percent of the size of the smallest table from which it is derived.

EXAMPLE:
The MicroStrategy Tutorial project uses aggregate tables by default. A simple metric sum (Revenue) will go to different aggregate tables depending on the attributes on the template.

  1. Create a report with Year on the rows and Revenue on the columns.
  2. Execute the report and view the SQL:

  3. Drill from Year to Item and view the SQL:

The query will go from using ORDER_FACT to ORDER_DETAIL. When Year is on the template, the engine selects the smaller table and the fact is calculated as:

sum(a11.ORDER_AMT)
    instead of:

    sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))
    Labels:

    Comments

    1. hinaFebruary 5, 2021 at 12:19 PM

      Is there any solution now to use aggregate tables with star schema without creating logical tables?

      ReplyDelete

    Post a Comment

    Popular posts from this blog

    Microstrategy "Error type: Odbc error. Odbc operation attempted

     "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...
    4 comments
    Read more

    Microstrategy Custom number formatting symbols

    Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
    3 comments
    Read more

    "Table structure cannot read or update" in the warehouse catalog error while updating a table structure in Microstrategy

    "Table structure cannot read or update"  in the warehouse catalog error while updating a table structure while using Warehouse Catalog in MicroStrategy Developer  This issue could be due to the incorrect prefix.  To fix the issue. 1. Open the Warehouse Catalog. 2. Select the table and assign the correct Prefix update the structure. 3. Save and close the warehouse catalog.
    Post a Comment
    Read more

    Email Subscription in Microstrategy

    Create Email Subscription in Microstrategy Creates an email subscription. Do not include any leading or trailing spaces in the ANSWER parameters. This causes a SQL error and prevents the command from executing. Ex: CREATE EMAILSUBSCRIPTION "New Multi Users" FOR OWNER "administrator" SCHEDULE "Books Closed" CONTACTGROUP "TEST"  CONTENT "Electronics Revenue by Region" IN FOLDER "\Public Objects\REPORTS\SUBJECT Areas\Sales and Profitability Analysis" IN PROJECT "MicroStrategy Tutorial" DELIVERYFORMAT HTML  EXPIRATIONDATE NEVER EXPRIED FILENAME "file_name"   SUBJECT  "Test REPORT" MESSAGE "Please Test"; CREATE EMAILSUBSCRIPTION [ subscription_name ] [FOR OWNER login_name ] SCHEDULE schedule_name  (ADDRESS address_name | USER user_name  | CONTACT contact_name [ADDRESS contact_ address_name ] | CONTACTGROUP contact_group_name ) CONTENT ( report_or_document_name IN FOLDER   loc...
    1 comment
    Read more

    Administration standards to optimize network data transfer speeds

    Optimization standards to help developers work with the System Administrator to optimize network data transfer speeds: Place all server components in the same segment to minimize latency between the Intelligence Server, Web server, data warehouse server, and metadata server Expand bandwidth and minimize latency between servers and clients to ensure that bottlenecks do not occur when data is requested over your network Use HTTP compression between the Web servers and Web clients to achieve maximum throughput between these components Configure a Web proxy server to handle caching and reduce the load on the Web server
    1 comment
    Read more

    Create an alert-based subscription in MicroStrategy Distribution Services

    Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief  steps bel...
    3 comments
    Read more

    Customers Who Live in the Same City as Call Centers

    Customers Who Live in the Same City as Call Centers Your new utility company has call centers located throughout the country, and your recent surveys indicate that customers who live in the same city as a call center are particularly satisfied with service due to extremely rapid repairs during power outages. To begin your new advertising campaign, you want to generate a list of Call Centers that coincide with Customer Cities. The following steps create an attribute-to-attribute qualification filter that generates the list of desired cities. To Create an Attribute-To-Attribute Qualification that Compares the Call Center and Customer City Attributes In MicroStrategy Web, log in to a project. Open any folder page (for example, click Shared Folders on the home page). Click the  Create Filter  icon  . From the Object browser on the left, select the  Customer City  attribute from the Customers hierarchy and drag it to the filter pane on the right. Change  Qualify...
    1 comment
    Read more

    HyperIntelligence Training Videos

    HyperIntelligence  Training Videos           Design and build hyper cards Optimizing Datasets for HyperIntelligence Using the HyperIntelligence for Office Outlook Add-In Building HyperIntelligence Cards Using HyperIntelligence for Mobile on Android Deploying HyperIntelligence for Outlook Insights On-The-Go: HyperIntelligence for Mobile Building HyperIntelligence Profile Cards Designing Custom HyperIntelligence Cards Using the Calendar with HyperIntelligence for Mobile
    4 comments
    Read more

    Best Practices for using images in Microstrategy reports

    Using images in Microstrategy reports On the I-Server we copy the images to the following folders: - Program Files (x86)\MicroStrategy\Developer\Images - Program Files (x86)\MicroStrategy\IntelligenceServer\Images - Program Files (x86)\Common Files\MicroStrategy\images To reference an image using relative path, the following locations are required to store the image in order to display in multiple MicroStrategy products.    Relative path sample: images/ logo.jpg To display images on: MicroStrategy Desktop, Web, Mobile, Distribution Services: * Additional Adobe Flash security rule applies when displaying images on Flash Dashboard For image to display on MicroStrategy Web Services:   Note: It is recommended that HTTP path should be used for reference image. User should consider using HTTP path to reference image and store image in an client accessible server for easier maintain and upgrading purpose ...
    Post a Comment
    Read more