How to automatically create summary table based on existing FM Model

Check list of all posts

As Known, summary tables provides a big performance advantage. This document will share a solution to automatically generate summary table with the help of Excel spreadsheet and Cognos FM, assuming that all detail tables are ready and modeled from Cognos Framework Manager.

Assumption:
The existing ETL and reports based on detail tables represent a significant investment.
All these detail tables can be consolidated.

Goal:
We will make summary tables with analytical capacity with much better performance;
We can also use summary tables to speed up any new report development and dashboard; and
We can make summary tables to support both Cognos and Tableau.

Approach:
To make current existing ETL code and report not touched or with minimal adjustment, we will reuse conformed measures and conformed dimensions.
Overall the effort to create summary tables is assessed to be considerably smaller than expected.
Identify all conformed dimensions
Identify all conformed measures
create Summary tables
We can list all measures and then identify most used measures for summary tables.

Implementation:

Step 1: create a Data Warehouse Bus Matrix as below

Step 2: create a excel template to generate SQL

The basic logic is to define new table, and insert data from detail tables into new summary table. All column names are defined as column names and types, including both conformed dimensions and measures.

DROP TABLE FACT_AGGREGATE_PERFORMANCE ;
CREATE TABLE FACT_AGGREGATE_PERFORMANCE ....
INSERT INTO FACT_AGGREGATE_PERFORMANCE
SELECT cast(to_char(FILEDATE,'YYYYMM') as integer) AS YYYYMM..........
UNION
SELECT YEAR_MONTH AS YYYYMM, .........
 Create table for example: CREATE TABLE MPL_BI."&B14&" ("&C6&" "&C7&","&D6&" "&D7&","&E6&" "&E7&","&F6&" "&F7&","&G6&" "&G7&","&H6&" "&H7&","&I6&" "&I7&","&J6&" "&J7&","&K6&" "&K7&","&L6&" "&L7&","&M6&" "&M7&","&N6&" "&N7&","&O6&" "&O7&","&P6&" "&P7&","&Q6&" "&Q7&","&R6&" "&R7&","&S6&" "&S7&","&T6&" "&T7&","&C14&" "&C15&","&D14&" "&D15&","&E14&" "&E15&","&F14&" "&F15&","&G14&" "&G15&","&H14&" "&H15&","&I14&" "&I15&","&J14&" "&J15&","&K14&" "&K15&","&L14&" "&L15&","&M14&" "&M15&","&N14&" "&N15&");
Insert data for example: SELECT "&C4& " AS "&$C$6&","&D4& " AS "&$D$6&","&E4& " AS "&$E$6&","&F4& " AS "&$F$6&","&G4& " AS "&$G$6&","&H4& " AS "&$H$6&","&I4& " AS "&$I$6&","&J4& " AS "&$J$6&","&K4& " AS "&$K$6&","&L4& " AS "&$L$6&","&M4& " AS "&$M$6&","&N4& " AS "&$N$6&","&O4& " AS "&$O$6&","&P4& " AS "&$P$6&","&Q4& " AS "&$Q$6&","&R4& " AS "&$R$6&","&S4& " AS "&$S$6&","&T4& " AS "&$T$6&","&"SUM("&C12&")"&" AS " &$C$14& ","&"SUM("&D12&")"&" AS " &$D$14& ","&"SUM("&E12&")"&" AS " &$E$14& ","&"SUM("&F12&")"&" AS " &$F$14& ","&"SUM("&G12&")"&" AS " &$G$14& ","&"SUM("&H12&")"&" AS " &$H$14 &","&"SUM("&I12&")"&" AS " &$I$14 &","&"SUM("&J12&")"&" AS " &$J$14 &","&"SUM("&K12&")"&" AS " &$K$14 &","&"SUM("&L12&")"&" AS " &$L$14 &","&"SUM("&M12&")"&" AS " &$M$14 &","&"SUM("&N12&")"&" AS " &$N$14 & " FROM  MPL_BI."&B12 &C18 & " GROUP BY " &$C4& "," &$D4& "," &$E4& "," &$F4& "," &$G4& "," &$H4& "," &$I4& "," &$J4& "," &$K4& "," &$L4& "," &$M4& "," &$N4& "," &$O4& "," &$P4& "," &$Q4& "," &$R4& "," &$S4& "," &$T4 & " UNION "
Step 3:  Fill in logic for columns and measures
The logic is actually built into Cognos Framework Manager, we need to simply find the column and test result, and find the native SQL, and then fill into column.

Step 4:  handle special cases such as to combine detail fact tables with different granularity Combine detail fact tables with different granularity

Sometimes, the detail tables have different granularities and different columns. One detail table includes all data, but not all columns, while other fact table includes only partial data only but with much more related columns. The challenge is to combine both data together to present the single interface which should not make end users aware of two different data sets.
The solution is to separate measures into two different groups:
1.Make sure all conformed dimensions are  assigned to both  detail tables.  This way will make both detail fact tables share the same conformed dimensions. (This is very much act as the stitched query in Cognos with multiple fact table.) if column(s) is not available, then assign -1, or “-1” for the column.
2.Assume that there are two detail fact tables, or Fact 1 and Fact2. Fact 1 handles measure A, B and C, while Fact 2 handles measure D, E and F.  In this case, then assign Measure A, B and C, and assign 0, or NULL for measure D, E and F for  Fact 1; Assign Measure D, E and F, and assign 0, or NULL for measure A, B and C for  Fact 2 able.