BI Reporting From TM1

[tryinghard]

I am trying to do reporting in BI using a cube in TM1. I have created the FM model; however the structure of the cube is entierly different from a regular DMR, for instance I do not see multiple levels in the hierarchy, I just see the Member hierarchy, so for instance I have to select just level 2, then I have to select all the members of level 2 individually, rather than the level.

Is there any document or best practices for reporting from TM1?

Thanks

[PlanningDev]

Take a look in the guides that come with TM1. There is a control cube called }HierarchyProperties. You have to name the different levels in there before the dimensions will show in BI as levels. Also, you need to have the measure dimension specified for the cube. Right click the cube and select properties and set the measure dimension.

[tryinghard]

Thank you planning dev and apologies for such a delayed response. The solution worked in the report studio; however, I faced a problem there. If there are 3 levels in a hierarchy i.e. level 0(lowest), level 1 and level 2. The hierachy is uneven i.e. few members in level 1 have members under it in level 0 and few members in level 1 are the lowest members. Now if I want to pull data for all the leaf members (which maybe in level 0 or level), then how do I do it. If I pull level 0 then it returns only the itmes in level 0.

In the hierarchy below level 0 has 10110, 10120, 10210 and 10220; however 10300 and 10400 are also lowest as they do not have anything under them in level 0. I want to create a report that has the leaf level for this hierarhcy 10110, 10120, 10210, 10220, 10300 and 10400.

Also, I do not see these levels in Analysis Studio at all, I also do not see any alias in analysis studio. Any way to fix that?

Eg
10000
-100100
--10110
--10120
-10200
--10210
--10220
-10300
-10400

[Herman Moller]

I have seen that issue with BI in the past as it doesnt like ragged hierarchies. Try the following test:

a) Create a TM1 Attribute call = Nlevel in TM1.

b) Manually 1st add all the codes you want to see eventhrough they are at different levels (You can use a attribute rule at a later stage)

These should filter through to Report Studio but from past experience you might have to refresh the FM package to bring through the metadata.

c) Try filter that view(In report studio) on the attribute and it should pick up all the elements you want to see.

HTH

Herman

[tryinghard]

Herman,

Thanks for the response. I thought that an attribute might solve the problem, but then I might have to create an attribute for every level in the hierarchy, also every time I add an item in the hierarchy, the attributes cube will have to be updated?

Also, any clue on how to fix Analysis Studio?

[PlanningDev]

I don't think analysis studio is designed to show levels. It is by nature a parent child analytical slice and dice data viewer. Im having the similar issue right now though where I want to run a rank on all bottom level elements.

[tryinghard]

Are you able to atleast see the alias in Analysis Studio, or just the original name. If you cannot see any alias then the whole point of using TM1 as a reporting source is pointless because a cube is best used for analytical reporting and most of the times the dimension item names would be numbers rather than description, making it very difficult for users to work with. IMHO

Any thoughts?

[PlanningDev]

Yes you can see an alias but you pick it when importing the cube into the framework model. Other attributes are not available in analysis studio to filter on.

[tryinghard]

I have seen that issue with BI in the past as it doesnt like ragged hierarchies. Try the following test:

b) Manually 1st add all the codes you want to see eventhrough they are at different levels (You can use a attribute rule at a later stage)

These should filter through to Report Studio but from past experience you might have to refresh the FM package to bring through the metadata.

HTH

Herman

I have created an attribute and in the attributes cube I created the calculation

['Level'] = ELLEV('plan_business_unit', !plan_business_unit); This rule is not populating any value in the cell. Then I created another calculation

['Level'] = N:1; even this calculation is not creating any value. Any thoughts?

[PlanningDev]

Is your attribute numeric or text? If its an alias or text you will need to use S: after the = sign.

[tryinghard]

It is a numeric attribute.

[tryinghard]

Yes you can see an alias but you pick it when importing the cube into the framework model. Other attributes are not available in analysis studio to filter on.

When I import the cube in and FM model I do get an option to click on an alias, but the moment I open up the cube in analysis studio I am unable to see the alias. Can you please let me know the step by step process to get the alias in FM model and then in analysis studio.

Thanks

[Martin Ryan]

I have created an attribute and in the attributes cube I created the calculation

['Level'] = ELLEV('plan_business_unit', !plan_business_unit); This rule is not populating any value in the cell. Then I created another calculation

['Level'] = N:1; even this calculation is not creating any value. Any thoughts?

I have found that even with numeric attributes you have to use the S: syntax. I think this is because the element type (as in what is returned by the dtype function) is "AN" not "N".

Martin

[PlanningDev]

Yes you can see an alias but you pick it when importing the cube into the framework model. Other attributes are not available in analysis studio to filter on.

When I import the cube in and FM model I do get an option to click on an alias, but the moment I open up the cube in analysis studio I am unable to see the alias. Can you please let me know the step by step process to get the alias in FM model and then in analysis studio.

Thanks

So you don't get to see any attributes of any kind in Analysis studio. Whichever alias you pick in FM is what becomes the element description that is shown to you.

[tryinghard]

I have found that even with numeric attributes you have to use the S: syntax. I think this is because the element type (as in what is returned by the dtype function) is "AN" not "N".

Martin

When I create this formula below it gives a syntax error. I tried this formula with a string attribute and also a numeric attribute, error in both.

['Level'] = S:ELLEV('plan_business_unit', !plan_business_unit);

[Martin Ryan]

You need to convert the number to a string:

['Level'] = S: trim(str(ELLEV('plan_business_unit', !plan_business_unit), 3, 0));

[tryinghard]

So you don't get to see any attributes of any kind in Analysis studio. Whichever alias you pick in FM is what becomes the element description that is shown to you.

These are the steps I am following:

1. Run Metadata wizard
2. Select the datasource and cube
3. In the window Metadata Wizard - Select Locales I click on the dimension and under it in the Alias Table: I click on the necessary alias. (I do not select any language option)
4. Click Next
5. Create a package
6. Open analysis Studio but I still do not see the alias that I selected

Note: when in step 6 I select the language as English, only then I see the alias. (Any thoughts here?)

[tryinghard]

['Level'] = S: trim(str(ELLEV('plan_business_unit', !plan_business_unit), 3, 0));

Thank you Martin, that worked like a charm. I do have one question though, why does it need this in the attributes cube but not in regular cubes?

[Martin Ryan]

I think this is because the element type (as in what is returned by the dtype function) is "AN" not "N".

Numeric attributes can't actually be numeric in a pure sense, because they're not supposed to sum up - the attribute of a consolidated element needs to be able to be written. This makes it more like a string than a number. Hence the S: rule requirement.

[PlanningDev]

So you don't get to see any attributes of any kind in Analysis studio. Whichever alias you pick in FM is what becomes the element description that is shown to you.

These are the steps I am following:

1. Run Metadata wizard
2. Select the datasource and cube
3. In the window Metadata Wizard - Select Locales I click on the dimension and under it in the Alias Table: I click on the necessary alias. (I do not select any language option)
4. Click Next
5. Create a package
6. Open analysis Studio but I still do not see the alias that I selected

Note: when in step 6 I select the language as English, only then I see the alias. (Any thoughts here?)

Try selecting a language option and see what happens. Your steps look fine to me.