Modeling Time series Data:
1) what is Time Series functions in OBIEE?
A) Time series functions providing the way to make the comparison between the time to calculate a measure because SQL is not providing any direct way to make time comparisons. So by using Time series function we can calculate a measure for the above explained requirements.
Note: Before going to use the time series functions we have to create Time Dimension hierarchy first.
a. We are creating a measure using time series functions in BMM Layer of Administration tool.
In OBIEE 10g we are having two time series functions
1. Ago( )
2. ToDate( )
1. Ago( )
This function calculates aggregated value for a measure as of some time period ( a month .
ago, or a year ago) from the current time. This function is passing three parameters.
Syntax: Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)
i. Measure: A measure column which you to make use in this function
ii. Level : On which level you want to calculate this measure. This level is selected from
the Time Dimension hierarchy.
iii. Number of Period: A numerical value which will go for how many level
you want to go before from current time.
2. ToDate( )
This function aggregates a measure from a beginning of a specified time period to the currently displayed time. We can create a calculated column using this function by following the same procedure how we have created a column using 'Ago' function.
Syntax: ToDate(<<Measure>>, <<Level>>) .
In OBIEE11g with the above function there is one more function has been added
3. PeriodRolling( )
This function allow us to create a aggregated measure across a specified set of query grain period, rather than within a fixed time series grain. The common use of this function is to create a Rolling Average such '10-Week Rolling Average'
Syntax: PeriodRolling(<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)
Measure: represents the logical measure column from which you want to derive.
Starting Period Offset: identify the first period used in the rolling aggregation.
Ending Period Offset: Identify the last period used in the rolling aggregation.
2)What is time series wizard? When and how do you use it?
1. We can do comparison for certain measures ( revenue.,sales etc.. ) for current year vs previous year, we can do for month or week and day also.
2. Identify the time periods need to be compared and then period table keys to the previous time period.
3.The period table needs to contain a column that will contain “Year Ago” information.
4.The fact tables needs to have year ago totals.
5. To use the “Time series wizard”. After creating your business model right click the business model and click on “Time Series Wizard”.
6.The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
7.The Time Series Wizard prompts you to select the period table used for the comparison measures.
8.Select the column in the period table that provides the key to the comparison period. This column would be the column containing “Year Ago” information in the period table.
9.Select the measures you want to compare and then Select the calculations you want to generate. For ex: Measure: Total Dollars and calculations are Change and Percent change.
Once the Time series wizard is run the output will be:
a) Aliases for the fact tables (in the physical layer).
b) Joins between period table and alias fact tables.
c) Comparison measures.
d) Logical table sources.
10.In the General tab of the Logical table source etc you can find “Generated by Time Series Wizard” in the description section.
11.Then you can add these comparison measures to the presentation layer for your reports.
Ex: Total sales of current qtr vs previous qtr vs same qtr year ago.
3)Can we create time-series functions at front end?
A)yes, new feature added in 11g
4)ORA-12801: error signaled in parallel query server?
A)When the level key is a function column, you may have this error:
[nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 12801, message:
ORA-12801: error signaled in parallel query server P000 ORA-01722: invalid number at OCI call OCIStmtExecute ....
a. The chronological column must have its values stored in a table column.
5)The query level ('Fiscal Year, Operational Year') must be a static level?
A) [nQSError: 10058] A general error has occurred. [nQSError: 22046] To use AGO function, the query level
('Fiscal Year, Operational Year') must be a static level. (HY000)
a. When you use two different hierarchy for the time dimension,
6) can we mix Analytics function and OBIEE times function?
A)You can not mix an database analytical function for instance lag with an ago or a to-date function otherwise you will receive this error.
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
[nQSError: 42015] Cannot function ship the following expression: Evaluate( LAG(%1,1) over (order by
%2),D903.c3, case when D903.c1 is not null then D903.c1 when D903.c2 is not null then D903.c2 end ) .
(HY000)
7) What is this chronological key ?
A)For defining a dimension to be a Time dimension, we need to have a chronological key. Chronological key is the key which is uniquely identifies the data at particular level whereas logical key is the key which is used to define the unique elements in each logical level.
a. Logical Level can have more than one key. When that is the case, specify the key that is primary of that level.
b. All other dimensions doesn't care about the order of the values in it.
e.g. In Region_Dim the values are north, south, west and east. Here nobody wants to see whether north comes first or south comes first. i.e. no order is required here.
c. In the case of Time Dimension there needs to be a particular order for all the values present in it.
e.g. 2010 is earliest and 2004 is older. Dec-10 is earliest and jan-10 is older. i.e. the values in the time dimension needs to follow a particular sorting order. So the chronological key is the key which tells the OBIEE that the data is increment based on the chronological column.
d. For defining a dimension to be a Time dimension, we need to have a chronological Key.
e.Then set the keys at each level, the hierarchy.
F. After doing these steps pull the Time Dimension to one of the folder in the Presentation Layer and after which when the column is pulled in the Analytic.
G. you get this output.
8) You can have inconsistent values when the Time/Calendar Dimension is not well build. Then which Rules you have to follow?
A) You have to follow this rules:
Key:
The chronological key of each level must be a real chronological key.
The chronological key of each level must not contain NULL values.
The chronological column must have its values stored in a table column and must not be the result of a function.
The foreign key of the time dimension which links to the fact table in the Business Model is not null.
Hierarchy:
Each sub-level must be contained in the above level. Each month belongs only to one year. A week can't be on two months.
Each sub-level must be present for the high level (All years must have 12 months).
All the time columns in your logical sql belongs to the right level of hierarchy. For instance, even if the week number is not the chronological key, the column must be in the week level of the time dimension.
