TM1 Forum

Hi all

Is there any way in Cognos BI we can filter a list report built on TM1 cube by string measure element value?
Let's say we have a measure dimension with "comment" element and I want to filter by that value using "position" function

Thanks in advance

Ok, will try to ask another question here.
My requirements are to build an interface for users in TM1Web so they can enter a list of programs and data by departments. They have a different set of programs each month. I don't know program names in advance and even how many programs they going to enter.

The main problem here I think is the data model is transactional (they have a new set of programs each month). I see 2 ways I can do it:

SOLUTION 1. Create a cube with dimensions: 1). Period, 2).Department, 3). Program line, 4). Measures
PROS:
- I do not need to add new Program elements as they are just values keyed in by Program line dimension elements
- I can use a cube view (so no need in websheet)
- easy to setup security by department, no need to refresh security if new programs are added
- a smaller cube size
CONS:
- seems I cannot filter programs by a cube value (i.e. measure "Program Name") in Cognos BI, so will need to create a separate reporting cube and transform program lines to a true program dimension like in solution #2

SOLUTION 2. Create a cube with dimensions: 1). Period, 2).Department-Program, 3). Measures
PROS:
- I can copy all the string measure elements to program attributes and use it for filtering in Cognos BI.
CONS:
- I will need a websheet and button to add new programs
- I will need to manage security by programs
- as program set is different every month I will have a sparse cube or another solution will be a cube with dimensions: 1). Period-Department-Program, 2). Measures or 1). Month, 2). Year-Department-Program, 3). Measures

What is the best practice when working with "transactional-like" data model, when one dimension is changed every period?

Hi

Best practice depends on the circumstances. However, I would suggest you consider the following.

Use an Input Cube which would be like your first solution.

Run a process to build a cube that is then like your second solution, or perhaps one where Department is a separate dimension, and Program is another separate dimension, depending on requirements. The second cube is then the one that you use for reporting.

Depending on how long the gap between input and the requirement to report is, you could update the second cube via an overnight chore, or possibly an hourly or half-hourly chore, or you could create a Web Sheet with an Action button to trigger a TI process to update the second cube immediately. The process will read a unique set of programs stored in the measures of the first cube, against each line, and update the Department-Program or Program dimension. Once this is in place, data can be pulled across by rules, or by TI. I would suggest that TI would be better. If the number of new Programs is small relative to the total, consider the use of the Dimension Direct set of functions. However, bear in mind that a Dimension update will cause locking on the Reporting cube.

Regards

Paul Simon

Hi Paul, thanks a lot for your answer.

The requirements are to input the data only in the last couple of months and all the previous periods are read only. Now I even consider solution #3, which is to build an input cube as in solution #1 and for reporting copy the data to relational database (when period is closed) and use it in Cognos BI. This solution is possible as the reports will be used by management where I don't need department security (otherwise the only possible way would solution #2, or its some modification).

Thanks again