Express 10.2.2 / BI on TM1 cubes / Hierarchies and Levels

[mama]

Hi all,

In a Express 10.2.2 project I am faced with budgeting, reporting, and ad-hoc analysis requirements. To keep it simple I decided to use TM1 cubes in the budget applications (Contributor) and as well TM1 cubes for reporting and analysis with Workspace Advanced (and Report Studio). Even if the technical integration of TM1 and BI was quite painful it worked at the end. I was surprised how fast TM1 cubes are loaded with millions of records from the SQL datawarehouse (same cube took 2 hrs in Transformer, now a few minutes in TM1 to recreate). So far so good...

But now I have realised that TM1 dimension structures are not ideal for Workspace Advanced (and other BI reporting/analysis tools). About 5 years ago I faced the same problem and I hoped that it has improved...

I have dimensions with multiple hierarchies and of course these hierarchies have not the same amount of levels. To be able to use level-based analysis/reporting I must define the names in the hierarchy properties. Let's imagine this product dimension:

In the Product dimension I have 2 hierarchies (Product Groups and Product Types). The Product Group hierarchy consists of 3 levels (Product Groups, Product Lines, Products), the Product Type hierarchy consists of 2 levels (Product Types and Products). Ideally I have the levels named "Product Group", "Product Line", "Product Type"... But if I have Product Group and Product Type on the same level (i.e. Level 1) this naming is not possible.

Now finally my questions:
- If I have such a situation (different number of levels in hierarchies), I should not name the levels in TM1. Right?
- If I cannot name my levels properly the usage of Workspace Advanced and Report Studio is much less practical (i.e. I cannot work with levels, level attributes...). Right?

I suppose this IBM technote is still valid in 10.2.2? http://www-01.ibm.com/support/docview.w ... wg21598902

This leads to the conclusion that I have to look for another OLAP for reporting/analysis in Express. Dynamic Cubes and Transformer are not included in Express. So I thought SSAS remains (as I am already using SQL Server for datawarehouse). This is a little bit frustrating though...

I would appreciate some comments and tipps. Thanks a lot!

[David Usherwood]

But now I have realised that TM1 dimension structures are not ideal for Workspace Advanced (and other BI reporting/analysis tools). About 5 years ago I faced the same problem and I hoped that it has improved...

I have dimensions with multiple hierarchies and of course these hierarchies have not the same amount of levels. To be able to use level-based analysis/reporting I must define the names in the hierarchy properties. Let's imagine this product dimension:

It's true that TM1's current approach to multiple hierarchies is a bit odd - IBM have talked about doing proper named hierarchies but it's yet to arrive. But you are designing and building both the TM1 side and the BI side - why not roll back and build a set of cubes which are BI-friendly, with dimensions with single hierarchies with levels and one top? It's not that hard. Do some prototyping and see what you get.
HTH

[mama]

Hi David,

Thanks a lot for your help. I definitely give it a few more tries to keep with TM1 cubes only. But I can imagine it will be hard to get the OK from the users for single hierarchies in dimensions. I will find out whether multiple cubes might be practical (although I think that will also be hard to agree with users...).

Let's hope that IBM develops in that direction. Having subsets available in the BI environment (Report Studio, Workspace Advanced...) might also be an approach but probably impossible...

Again, thanks a lot!

[David Usherwood]

Thanks a lot for your help. I definitely give it a few more tries to keep with TM1 cubes only. But I can imagine it will be hard to get the OK from the users for single hierarchies in dimensions. I will find out whether multiple cubes might be practical (although I think that will also be hard to agree with users...).

I suggest you think somewhat outside the box. Since BI wants simple level-based hierarchies, build a set of cubes using such (different) hierarchies. Since TM1 doesn't mind, build a more unified cube for TM1 users. Align the content with TIs or (if the content isn't very large) rules. I really can't see why you need to go and get another OLAP server when you already have the best there is (but I may not be totally unbiased on this point )