Creating View based on Parameter Input

For some processes with certain criteria, we need users to send parameters before they run the processing.

After the TM1 process executed, maybe you will use the parameters to retrieve data from data source or to show data based on that parameters.

How to make sure that users will see the correct data?

For example, users do data loading for year 2015, actual sales data, and currency USD. After data loading, they expect to see the data under the correct parameters (Year 2015, Scenario Actual, and Currency USD) in Sales cube, while the purpose of sales cube is to store data of Sales either actual, budget, Forecast.

We can reduce step by step that users should do by limited the view base on the parameter entered by users by creating correct view.

Example:

Structure of Sales Cube:

Dimension:

1. Year (2000,2001,…,2030)
2. Months (Jan, Feb, …Dec)
3. Currency (USD, IDR, IDN, SGD, AUD, …)
4. Scenario (Actual, Forecast, Budget)
5. Product (Product1, Product2, …)
6. Account (Qty, Unit Price, Unit Discount, …)
7. Sales Measure (Value,…)

Data source: sql server

Data loading parameters:

pYear:  [2015]

pScenario: [Actual]

pCurrency: [USD]

Process:

1. Retrieve data from Sql server tables.

Sample query:

Select qty, unit_price, Unit_discount, …from [table name] where year = [pYear] and Currency = [pCurrency] and qty <>0 and scenario = [pScenario]

2. Validation

Example: do process only if account is exist in account dimension

3. Write data into Sales cube

Cellputn (qty, [Cube Name], ‘2015’,’Jan’,’Actual’,’USD’,’Qty’,’Value’)

Cellputn (Unit Price, [Cube Name], ‘2015’,’Jan’,’Actual’,’USD’,’Unit Price’,’Value’)

Cellputn (Unit Discount, [Cube Name], ‘2015’,’Jan’,’Actual’,’USD’,’Unit Discount’,’Value’)

4. Create view to show the result

SubsetCreate(‘Year’,[subset name]);

SubsetElementInsert(‘Year’,[SubsetName],pYear,1)

SubsetCreate(‘Scenario’,[SubsetName])

SubsetElementInsert(‘Scenario’,[SubsetName],pScenario,1)

SubsetCreate(‘Currency’,[SubsetName])

SubsetElementInsert(‘Currency’,[SubsetName],pCurrency,1)

ViewCreate([CubeName],[ViewName])

ViewSubsetAssign([CubeName],[viewname],’Year’,[SubName])

ViewSubsetAssign([CubeName],[viewname],’Scenario’,[SubName])

ViewSubsetAssign([CubeName],[viewname],’Currency’,[SubName])

5. Put the view under the same application for user to check the result of data loading.

Users don’t need to choose element as the view created base on parameter enter by users during data loading process.

The view will only show:

year = [pYear],

Scenario = [pScenario]

Currency = [pCurrency]

By creating view based on processing parameters, we present the correct data. Users do not need to choose elements they wanted to check as the result of data loading process, and it is avoid users to choose wrong elements. For example the process data of 2015, but they check data 2014 or other years.

Illustration:

Illustration of creating view based on parameter input

(Veronika Rotua Gultom)