Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post Reply
HyunaHyuna
Posts: 13
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by HyunaHyuna » Wed Jul 29, 2020 8:54 am

Hi,

I am putting together a sort of best-practice document for the junior consultants of my team about how to effectlively work with TM1 (for example: why are comments important, how to write them, why is it good to use spaces in code, etc). I am mostly using Cubewise's Bedrock White Papers, and presenting most of the advice there.

(Side question: for programmers familiar with these documents, are there any points in there that have not held up as time passed over the years since they were published? What parts do you think should be revised?)

Most points in the papers I can argue for easily, but I am stuck with one point, and it's a major one, and one that has never been used by my team. This is using separate measure dimensions for each cube. What is standard in my current team is to create one measure dimension, and put any measures that are used in any of the cubes in this one measure dimension.

The white paper only says this:
While many of the dimensions within a cube may be shared with other cubes, there should always be a separate measures dimension that is unique to each cube.
The pro of using one measure dimension is that you only have to define (and format) often-used measures (like Revenue) once, and you don't have to define it every time a new cube uses it. The drawback is that this dimension now has 100+ elements, for all kinds of special exceptions etc. Cleanup is especially hard (how do you figure out if one element is used in a cube somewhere in a big model?), maintenance is hard, visually it is not appealing at all.

However, I find myself in a spot where I am having a hard time coming up with more arguments for the separate dimensions best practice. I would like more talking points, to demonstrate once and for all that separate measures dimensions are superior. What are your thoughts, what other advantages would you highlight for using separate measure dimensions, and what other disadvantages to you see with using just one big measure dimension?

Thank you kindly for any insight.

User avatar
ykud
Posts: 108
Joined: Sat Jan 10, 2009 10:52 am
Contact:

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by ykud » Wed Jul 29, 2020 11:40 am

Somebody will put a string element in this mega-measure dim and bam: you cannot reorder dimensions anywhere anymore :)

lotsaram
MVP
Posts: 3416
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by lotsaram » Wed Jul 29, 2020 12:01 pm

HyunaHyuna wrote:
Wed Jul 29, 2020 8:54 am
The drawback is that this dimension now has 100+ elements, for all kinds of special exceptions etc. Cleanup is especially hard (how do you figure out if one element is used in a cube somewhere in a big model?), maintenance is hard, visually it is not appealing at all.
Really you don't need anything more than that. A single measure dimension accross all cubes is a bad practice. Simple as that.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Emixam
Posts: 65
Joined: Tue May 21, 2019 3:33 pm
OLAP Product: TM1
Version: PA 2.0.x
Excel Version: 2016
Location: North America

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by Emixam » Wed Jul 29, 2020 12:58 pm

There should always be a separate measures dimension that is unique to each cube.
That's it.

Personally, I like to have the same naming convention for all my measure across my model. It's always something like prefix_CubeName :
  • m_SalesMargin, m_ProfitAndLoss, m_BalanceSheet
  • measure_SalesMargin, measure_ProfitAndLoss, measure_BalanceSheet
  • meas_SalesMargin, meas_ProfitAndLoss, meas_BalanceSheet
That's not part of the "best practices" but after many years of TM1, this is how I like to name my measure dimension.

Cheers !
Sorry for my English :)

David Usherwood
Site Admin
Posts: 1415
Joined: Wed May 28, 2008 9:09 am

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by David Usherwood » Wed Jul 29, 2020 1:24 pm

lotsaram wrote:
Wed Jul 29, 2020 12:01 pm
HyunaHyuna wrote:
Wed Jul 29, 2020 8:54 am
The drawback is that this dimension now has 100+ elements, for all kinds of special exceptions etc. Cleanup is especially hard (how do you figure out if one element is used in a cube somewhere in a big model?), maintenance is hard, visually it is not appealing at all.
Really you don't need anything more than that. A single measure dimension accross all cubes is a bad practice. Simple as that.
Especially since it looks as if you are putting account elements like Revenue in your mega-Measures dimension, which should be in their own Account (or Line) dimension). Measures dimensions should absolutely be cube specific so you can manage them without affecting other cubes. Arguably they are there for future-proofing purposes - when the client discovers a new requirement you have a chance to implement it without restructuring your cube.

Wim Gielis
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by Wim Gielis » Wed Jul 29, 2020 1:42 pm

Just think about element security, which could be different for the same element, in different cubes.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Edward Stuart
Community Contributor
Posts: 236
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by Edward Stuart » Wed Jul 29, 2020 2:01 pm

Every cube in your model is unnecessarily sparse and places greater risks on your Rules/ Feeders needing to be on point.

I'd take the one off hit of setting the formatting on an element every day over the cleanup/ maintenance requirement of a general measures dimension.

My guess is this measures dimension started off as a few elements so it didn't seem too bad but over time it has morphed into 100+ elements and the drawbacks you've listed. Best practice is to separate your concerns/ introduce modularity to prevent these scenarios from occuring.

HyunaHyuna
Posts: 13
Joined: Thu Jun 16, 2016 7:57 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Separate measure dimension for each cube VS one singular measure dimension for all cubes

Post by HyunaHyuna » Fri Jul 31, 2020 12:29 pm

Thank you for the replies, I feel confident that I have enough talking points now!

Post Reply