Oracle BI metadata repository - VI. Setting the time dimension and developing indicators for time comparisons

In " Oracle BI Metadata Repository - V. Set hierarchies for dimensions, creating a level-based ratios "as described" step-by-step "procedure as in the business model of creating hierarchies for dimensions and how to create the basis level-based and ratios. The aim of this article is to show the "step-by-step" process as the business model sets the time dimension, how to create indicators for comparing the time (eg Revenue showing the state before a certain period or YearToDate Revenue). Furthermore, as is easy to create indicators showing changes (eg, difference, percent, index), and finally, how all rozkategorizovat into folders and make available to end users for analytics.

The resulting presentation layer and Business Model by indicators for time comparisons

A / Set the time dimension Note: If you need to create a pointer of type 'obdobíAgo "or" obdobíToDate "then it is necessary to identify the hierarchy of the time dimension with the" Time dimension ". 
In the case of actual physical table for the time dimension is appropriate for the data type for columns individual level (eg, YEAR = 2008, MONTH = 200,812) was a string (STRING) - you should not use a numeric type (NUMBER). The reason is that if you use a numeric type in the physical layer of the BI metadata repository used type DOUBLE (of course it depends on what type of physical data goes), which for the "YEAR = 2008" will display the value "2008.00". This then can cause malfunction and counting, "Ago" indicators in the drill values.

1st In Business Model "Sales" select an existing hierarchy time dimension DIM_OBDOBI> right click> Properties ... > Here and check the checkbox "Time dimension" second At the level of the hierarchy KVARTAL DIM_OBDOBI KVARTAL_ID add column (column dimension of $ D_OBDOBI - the PK table) and mark it as a new logical key (Drag & Drop to move the column from the dimension hierarchy to> right click> New Logical Level Key ...) Note : Chronological key is a column containing gradually increasing in value over time. The key data type does not matter, it might be the type of numeric, string or date. The key should be selected column, which is the primary key dimensional table, that through this column is the join between a table made ​​dimensional and facts. 3rdColumn KVARTAL_ID Chronological mark as key (to select the level KVARTAL> right click> Properties ...> Keys tab) B / Creation of indicators showing the status before the first The facts table F_PRODEJ $ create a new logical column for VYNOS_PRED_CTVRTLETIM indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_PRED_CTVRTLETIM> check Use existing logical columns as the source), the second In the Expression Builder to create a calculation to show the state before the (Select Functions> Time Series Functions> Ago> Insert) third The AGO function as "Measure

















"Add revenue to the" Level'Quarter level and "Number of Periods"Add" 1 "(one quarter back).

4th The result is a new indicator based VYNOS_PRED_CTVRTLETIM

5th In the same way load indicatorsNAKLAD_PRED_CTVRTLETIM,VYNOS_PRED_ROKEM and NAKLAD_PRED_ROKEM (as "Level" at the annual level of indicators, select "Year")


B / Development Indicators YearToDate
first The facts table F_PRODEJ $ create a new logical column for VYNOS_YEAR_TODATE indicator, which will be calculated from the already existing indicators (the Business Model "Sales", select the table F_PRODEJ $> right click> New Object> Logical Column ...> call it VYNOS_YEAR_TODATE> check Use existing logical columns as the source)

2nd In the Expression Builder to create a calculation to display the status of Today (Select Functions> Time Series Functions> Today> Insert)

3rd The Today function as a "Measure" add revenue to the "Level" Year level.

4th The result is a new indicator based VYNOS_YEAR_TODATE

5th In the same way load indicator NAKLAD_YEAR_TODATE 


C / Creation of indicators showing change between now and the past 
Note: For purposes of comparison of two indicators is available"Calculation Wizard", which will facilitate the creation of typical parameters such as change, percentage change, index or percentage, and more will help you treat exceptions such as division by zero, or accessing a NULL value.

1st The facts table, select the column F_PRODEJ $ DECREE> right click> Calculation Wizard

2nd Select a metric to compare the yield indicator (selectVYNOS_PRED_CTVRTLETIM and VYNOS_PRED_ROKEM )

3rd For both indicators allow to generate indicators showing change and percentage change in

4th Calculation Wizard generates four new indicators

5th In the same way and follow the pointer Circulation C / categorization, move and rename the new ones in the presentation layer Note: To simplify the orientation of end users to design reports, presentations can be categorized into different metadata subfolders. The result may be similar to a tree structure:




1st Move and rename the newly created pointer to the presentation layer

2nd In the Presentation layer, select the field "Sales> right click> New Presentation Table

3rd Name the folder "- Revenues"

4th In the same way, create folders with names "- Cost" and "- Profit"


5th The newly created folders divide (Drag & Drop) as indicators of the importance of component sales result is a newly established indicators showing the status before the cumulative indicators showing YearToDate and indicators showing change between now and the past.




The next and final part will be devoted to how to put all this up and make available to end users for reporting and analysis .