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

    Scheduling a report or document to be sent to an FTP in MSTR

    Scheduling a report or document to be sent to an FTP server You can have a report or document automatically delivered to a location on your FTP server on a specific schedule. To do so, you must subscribe to the report or document, as described in the steps below. You can customize your subscription by typing macros in the  File Name ,  Sub-folder , or  Zip File Name  fields. These macros are automatically replaced with the appropriate text when the report or document is delivered. For example, you create a subscription to a document. If you type  {&Project}  in the  File Name field, the name of the project in which the document is saved is displayed in the name of the document when it is delivered. • This procedure assumes that an administrator has already added your FTP server as a new device in Developer. Steps to do so are included in the  System Administrator Help . To send a report or document to an FTP server on a schedule ...
    Post a Comment
    Read more

    Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

    Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy FiscalWeek Returns the numeric position of a week within a fiscal year, for a given  input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year. Syntax FiscalWeek< firstWeekDay ,  firstMonth >( Date / Time ) Where: • Date / Time  is the input date or timestamp. • firstWeekDay  (default value is 1) is a parameter that determines which day of the week is considered as the first day of the week. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday. • firstMonth  (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until ...
    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...
    2 comments
    Read more

    OLAP features in Microstrategy

    OLAP features in Microstrategy MSTR  OLAP Services uses Intelligent Cube Technology—an in-memory version of report data that can 1 About MicroStrategy OLAP Services  can be manipulated by the MicroStrategy Analytical Engine. MicroStrategy Desktop, Web, and Office users can slice and dice data in reports within Intelligent Cubes without having to re-execute SQL against the data warehouse.  Many of the standard OLAP features that MicroStrategy provides out of the box, such as: Page-by Pivoting Sorting Subtotals Banding Aliasing Outline mode Thresholds etc.. With an OLAP Services license, user can perform additional OLAP analysis, using the following features:  Displaying data on the fly: dynamic aggregation, page  Creating metrics on-the-fly: derived metrics, Defining attribute elements on-the-fly: derived elements,  Filtering data on the fly: view filters and metric filters,  Importing data as an Intelligent Cube
    Post a Comment
    Read more

    Star Schemas issue fixes in Modelling of Microstartegy

    Star Schemas issue fixes in Modelling of Microstartegy Explanation This schema is characterized by one lookup table per dimension, with base tables at the lowest level. This is the fastest way to set up a data warehouse: This type of schemas is supported but has restrictions such as when adding aggregate tables: Problem Double counting. According to the diagram above, a report that contains month and the a metric SUM(SALES_AMT) will go to the aggregate table and join to the column to retrieve the description from the table. Since the column is not unique in its lookup table, the results will appear duplicated. Recommendation MicroStrategy engine is optimized to work with snowflake schemas, where each attribute level has a distinct lookup table. Star schemas are supported with restrictions, as long as fact tables are not at a higher level than the dimension tables to which they are joined. Consult the following MicroStrategy Knowledgebase document for further information....
    Post a Comment
    Read more

    Create a transaction services photo uploader

    Create a transaction services photo uploader   1.  Create a new table "photo_upload" in Tutorial warehouse database (the default location: C:\Program Files\MicroStrategy\Tutorial Reporting\TUTORIAL_DATA_7200.mdb), as shown below:    2. The 'photo_upload' table has to be pre-populated with *exactly* 10 rows of data, the values for the 'ID' column should be 1-10 and the values for the 'uploaded' column should all be 0 3.  In MicroStrategy Desktop, create a freeform report "R1" based on the new table "photo_upload" in Tutorial data created at step 1, as shown below:   SELECT Location, Description, ID, uploaded, numbers FROM PHOTO_UPLOAD 4.  Create another table for transaction insert SQL. Make sure to create an 'autonumber' type ID as primary key for this table, or auto_increment ID for different DBs.                     5. Create...
    Post a Comment
    Read more

    Developer not starting with "invalid picture" message

    Developer not starting with "invalid picture" message. This error could be due to the fact that t here is insufficient disk space on the drive used for the Windows Temp directory or the Optional Work Drive. https://support.microsoft.com/en-us/help/964421/error-481-invalid-picture Users may see the "Invalid Picture" error thrown when attempting to launch MicroStrategy Developer in Windows: CAUSE: This error is caused by a lack of available space on the user's hard disk to properly launch the platform. ACTION: To remedy the issue, simply clear up some active space on the hard drive to allow MicroStrategy to properly launch.
    2 comments
    Read more

    exact string when searching for elements in an element prompt in MicroStrategy

    When a user types in keywords to tries to find element names in an element prompt, the search returns all objects containing the keywords in MicroStrategy Developer 9.4.x-10.x. However, the user would like to search for the exact phrase. It is suggested to use quotes to get exact phrase when there is a space between. Like "Black Panther" Using the MicroStrategy Tutorial Project as an example, a user wishes to search for an item named Minolta Maxxum Camera. The search results for Minolta Maxxum Camera return all items containing any or all of those words, as shown below: CAUSE: This occurs due to the search defaulting to 'ORing' the search terms. This means that any or all keywords that match the strings will be returned. The SQL for this search is shown below: SELECT ITEM_NAME FROM LU_ITEM WHERE (ITEM_NAME LIKE '%Minolta%' OR ITEM_NAME LIKE '%Maxxum%' OR ITEM_NAME LIKE '%Camera%') ACTION: To match an exact string, use...
    Post a Comment
    Read more

    Execute Integrity manager test from Command line

    Execute Integrity manager test from Command line  MSTR Integrity Manager allows the user to execute a test without having to load the GUI, or to schedule a test to run later at specific times or dates. Go over using Windows AT command at: https://support.microsoft.com/en-us/help/313565/how-to-use-the-at-command-to-schedule-tasks https://www.lifewire.com/at-command-2618090 Here are the prerequisites to execute a test from the command line: Create a test and saved using the Integrity Manager graphical interface. Make sure that the users has the ' Use Integrity Manager ' privilege for that project(provided by the administrator) and the ' Execute ' permission for the reports to be tested. Keep in mind that MicroStrategy Integrity Manager can only test three-tier projects, i.e., projects which are connected to a MicroStrategy i- Server. Projects in Direct Connection (two-tier) mode cannot be tested with this tool...
    1 comment
    Read more