TM1 Forum

Hello, can some direct me to a good tutorial/video for Alternate Hierarchies. I want to build a GL app using Alt H where I have multiple clients with different calendar start and end

For eg
Client1 Calendar Start- Jun 2018 and Calendar End May 2019
Client2 Calendar Start- Oct 2018 and Calendar End Sept 2019
Client3 Calendar Start- Jan 2019 and Calendar End Dec 2019

Also the Quarter structure could change from 4444 weeks to 4445 weeks between clients

I need to have a single Cube to store all the sales data and generate YTD for Week, month and Year. Have Cognos Analytics report on this cube for each client.

Any suggestion/insight for such architecture is greatly appreciated.

Thanks,

Rtel

Hello,

That's an interesting question and far from easy.

I would have a cube with 2 dimensions, other than the dimensions you did not write about like measure, account, product, ...:

- Client
- Week

Client would be a regular dimension, including rollups for whatever reporting you need.

Week (or Time or similar) would be a dimension with alternative hierarchies (AH) in PA-speak.

Main dimension name / AH: 'Week'. Elements: Total Week and as children all level 0 weeks. A week would have a naming convention like: 2019_W22.

As an exercise I created weeks from 2017_W01 until 2022_W53 with 53 weeks in each year.

Next, the leaves AH called 'Leaves'. Elements: generated by PAW so no need to worry about it. In fact it's very similar to the first AH we discussed.

Next, an AH called 'Quarter' with a Total and consolidated children: Q01, Q02, Q03, Q04 (or leave out the 0). These consolidated elements contain the relevant weekly elements. Here it will probably depend on 444 or 445 so this might lead to 2 separate AH. Make sure weeks are level 0 below the quarters in this AH.

Likewise, an AH called 'Month' with a Total and consolidated children: M01, M02, M03, ..., M12. These consolidated elements contain the relevant weekly elements. Here it will also probably depend on 444 or 445 so this might lead to 2 separate AH. Make sure weeks are level 0 below the months in this AH. You might want to add a week below 2 different month consolidations with a weight different from 1. It will be important to know / determine how a week falls within a/each month. Put differently, you need to maintain a calendar in a different cube. In the past I have solutions like input of the percentage of each week in each month. 2 days out of 5 would be 0.4 as the weight of week X in month Y. These weights will need to be used in setting up the hierarchies.

Yearly view in an AH: 'Weeks by Year'. Elements: Weeks by Year > 2018, 2019, 2020, 2021, ... > yyyy_W01, yyyy_W02, yyyy_W03, ..., yyyy_W52/53. This is very easy in theory. Yet in practice: could it be that some weeks are spread over 2 contiguous years ? Then same reasoning as for months.

Year-to-dates for weeks: Add a different AH. Like YTD W22 has children: 2017_W01, 2017_W02, ..., 2017_W22, 2018_W01, 2018_W02, ..., 2018_W22, [...], 2022_W01, 2022_W02, ..., 2022_W22
Year-to-dates for months: either build them into the AH for months, or add a different AH. If it's a different AH, proceed like in the example above about YTD for weeks.

You can go for a manual setup / maintainance of dimensions and hierarchies, but you will most probably be better off to maintain / setup these AH by using functions like HierarchyElementInsert, HierarchyElementComponentAdd, etc. Very similar to its better-known variants for dimensions.

Or use the built-in functionality to create simple AH: add an attribute to the main dimension and create the hierarchy based on the attribute.

In the screenshot you can see a mock-up: a value of 1 for 'Client 01' in any of the weeks.
My 2ç. Hope this helps !
Please post back any update / feedback / good-bad results you get !

Hi Wim, Thank you very much for all the details and your time. I am still learning to create alternate H. Via TI or manually.

I will work on your suggestions and will get back.

Also please let me know your thoughts for following approach.

1) have a cube where it shows start and end date of Calendar for each client.
2) In Week dimension; create alternate H one for each client that shows role up of weeks as per the start and end as well as 444 and 445 collection of week in a quarter.
3) Tie each of this Alt H to the corresponding member so that when when we select a client- corresponding Alt H shows up (I am not sure how to do that)

Please bare with me if this approach has some gaps in it. Its just a high level thinking.

Sincerely

Rtel.

I'm a little intrigued.
By "I have multiple clients with different calendar start and end" are you suggesting to hold multiple customers' GL data in a single multi-tenant environment? Customers with different financial years?

If yes I have to say I find such a scenario highly unlikely (to say the least). What about differences in other dimensions which are part of almost any GL cube like the chart of account, cost center or profit center? Yes you could have different alternate hierarchies for all of them but how would the customers feel about all their data being mashed up in the same place?

There's also some other catches when it comes to GL data; will the cube be movement based or balance based? will any additional adjustment period(s) need to be accounted for? (if yes where do the adjustment periods go? ... easy enough for a M01 - M12 system but if based on actual calendar months combined with different financial years then will fall apart).

Hi,

re lotsaram's query, obviously rtel can answer this but we have some 'internal clients' who use different calendars but use the same chart of accounts etc , for example normal calendar is April to March but schools calendar is September to August.

cheers, Mark

Hi Lotsaram and Mark for your comments and suggestions

In this scenario,

1) This central TM1 cube (with all client) will be source to Cognos analytics for visualization and reporting.

2) Client can access only their data based on security.

3) Accounts Hierarchy is same for all clients.

Measures I am looking to calculate are sales for --- Week to date, Month to date, year to date, Previous quarter, Previous year.

Please let me know if this is feasible solution or what are potential issues in terms of scalability, performance, security

Thanks

Rtel

A lot depends on what you mean by "clients", is this some kind of internal customer within a single organisation or genuinely different organisations. If its the former then its just a normal build with a bit of complexity on the time dimension.

Make sure that the hierarchies are available everywhere that you want to use them in CA, I don't know how much of CA can see TM1 hierarchies. (I'd be interested to know).

If it is the latter, multiple organisations in a single DB, then my view is this is probably just about OK in theory but the reality of running it means that it would not be practical.

Accounts Hierarchy is same for all clients.

This would be unusual or are we talking government mandated structures (I think in France everyone has to use the same COA for some aspects of reporting for example)

License / Legals - Not sure how IBM would view a partner hosting a single DB that was then access by multiple organisations, there is the potential for significant "lost" revenue from their PoV.

Security - You only need to make an error here once to cause someone and yourselves some very significant issues.

Hierarchy Security - Not checked, but I'm not sure if hierarchies themselves are something that be secured. This could make anything that is self-service in CA very confusing.