More than One instance of a Dimension

[CiskoWalt]

Hello,

We currently have one 'Account' dimension and one 'Department' dimension. These 2 dimensions are shared by several cubes.

It is nice to have only one instance of a dimesnion. There is only one TI process to maintain it and one User defined Segment to maintain.

However, only a subset of the elements are used in some of the cubes.

Cubes used to track Capital Expenditures will only make use of Asset Accounts
A CustomerSales Cube will only make use of Revenue Accounts.
HR cubes will only require Departments that have people in them.

I think that sharing one unique dim is not a best practice for in this situation for 3 reasons:

1 Increased sparsity
By using All of the Accounts (Assest, Liability, Revenue Expense) are we are increasing the amount of sparsity?
(None of the Liability, Revenue and Expense accounts willl ever be used in the Capital Expenditure cube.)

2 Poor Dimension Browsing
When the users browse the Account dimension from w/i the Capital Expenduture cube, they will be forced to look at accounts that are not relevent.

3 Secutity Can differ by cube
The security settings for each cube might differ if not today perhaps in the future:

I can see dept '123' in the Capital Expenditure cube but I can only see Dept 889 in the HR cube.

Given these 3 reasons, does it not make sense to have a different dimension for each cube?

Thanks

Walt

[tomok]

Given your example I would create separate instances of the dimension. I know there are ways to limit the elements that clients see in each cube via subsets but that doesn't stop them from clicking "Show All" in the subset editor. My number one goal when designing a model is to keep it as simple to understand and use, from a user's perspective, as I can. Sometimes that goal has to trump the goal of lowest possible maintenance.

[John Hammond]

Would go for the one dimension and put element security on the CoA provided the sectional splits are discrete.

Normally I only replicate dimensions if you have to store data at a consolidated level eg you have a summarised CoA and only some sections with the full detail. This is where TM1's inability to store on consolidations gets irritating.

Restriction by security is more complex than separate CoAs and may result in extra development time so as Tomok says its always a trade-off in design...

[csjean]

Hi,

I would tend to side with tomok on this.
Especially with the different security settings for each cube.

You can even keep your 1 TI:

Populate/maintain both dimensions with this TI so they are allways "in sync".

[tomok]

Would go for the one dimension and put element security on the CoA provided the sectional splits are discrete.

The problem with this is that in the real world, I find that users are almost always in multiple groups. So even though you segregate the sections of the COA dimension by security, some users may still be able to see most or all of the COA because of the additive nature of security. Then all you've done is waste a bunch of time creating security that looks good on paper but when you look at the actual effect of that security, based on who is in what groups, you haven't done anything.

[David Usherwood]

So....
What you're actually saying, I believe, that you haven't been able to get the security setup to do what you want....
Is that a good argument for building lots of extra dimensions and cubes?

[John Hammond]

I think tomok is saying that you need a kind of application security to act first in a non additive way before the user security gets applied.

The only way would be to implement this yourself by having 2 cubes Application and User security as masters and then apply the results of the 2 to the system security }Client_Groups when a user enters a different application on the same server.

Messy, but possible... Ideally Iboglix should have something like this...

[tomok]

So....
What you're actually saying, I believe, that you haven't been able to get the security setup to do what you want....
Is that a good argument for building lots of extra dimensions and cubes?

No. What I am saying is that the additive nature of TM1 security can often make the best laid plans not effective. Given the OP's example of an Account dimension. Do you just have a single dimension for all cubes, e.g. Balance Sheet cube, CapEx cube, Income Statement cube? Clearly these cubes all use some part of the COA. If I build each of these cubes with a single Account dimension and use security to keep CapEx users from the seeing the income statement accounts and income Statement users from seeing the balance sheet accounts, etc. this works fine as long as you don't have users in multiple groups. If I am in both the BS and IS groups then when I open the IS cube, I can potentially see both sets of accounts in both cubes, making the security breakout, as far as a tool to keep this from happening, useless. That's why I said the only effective way to keep the accounts segregated like the OP wants is to create separate dimensions.

As long as you don't have overlaps security can work great for this. However, in my experience, there are always overlaps in security groups so this type of approach rarely works in the real world.

[jim wood]

For me it would depend on a couple of factors:

1) Current memory use to memory available.
2) Current Performance.
3) Ease for the users.

If you are not struggling for memory and performance is good why change? While it will be more efficient to use multiple dimensions while sparsity come sin to play you have to ask, do I really need it? Will it make easier for your users?

Answer these questions yourself and you have your answer. Anything else is just an opinion,

Jim.

[mattgoff]

1 Increased sparsity
By using All of the Accounts (Assest, Liability, Revenue Expense) are we are increasing the amount of sparsity?
(None of the Liability, Revenue and Expense accounts willl ever be used in the Capital Expenditure cube.)

TM1 is designed for sparsity. I would guess that reducing accounts would reduce your sparsity by less than an order of magnitude in most cases. That's pretty much zero in TM1 performance terms.

2 Poor Dimension Browsing
When the users browse the Account dimension from w/i the Capital Expenduture cube, they will be forced to look at accounts that are not relevent.

Hierarchy. Subsets. Zero suppress. Active Forms.

3 Secutity Can differ by cube
The security settings for each cube might differ if not today perhaps in the future:

I can see dept '123' in the Capital Expenditure cube but I can only see Dept 889 in the HR cube.

Multiple cubes and reduced elements in dimensions doesn't solve this problem. The user will still see that a given dept has data in one cube and not in another.

Do you just have a single dimension for all cubes, e.g. Balance Sheet cube, CapEx cube, Income Statement cube? Clearly these cubes all use some part of the COA. If I build each of these cubes with a single Account dimension and use security to keep CapEx users from the seeing the income statement accounts and income Statement users from seeing the balance sheet accounts, etc. this works fine as long as you don't have users in multiple groups. If I am in both the BS and IS groups then when I open the IS cube, I can potentially see both sets of accounts in both cubes, making the security breakout, as far as a tool to keep this from happening, useless. That's why I said the only effective way to keep the accounts segregated like the OP wants is to create separate dimensions.

Yes, I have one Account dimension for all cubes. I control what accounts are visible using subsets and/or zero suppress since most of my users have access to the full COA. And since they're aware of the full COA, they understand what subset they should be using based on the cube. Unsophisticated users are probably using an Active Form anyway, so that's easily controlled.

Decades of database development show that normalization is very important-- I'm not seeing a good reason yet why one would break that with multiple, nearly identical dimensions. You introduce problems with dimension and security maintenance for zero performance and little (I'd argue actually a loss) of end-user usability.

Matt

[jim wood]

I agree Matt, especially on sparsity. It's ability to handle large amounts of sparsity was one of TM1s selling points for years. It probably still is.

[tomok]

Decades of database development show that normalization is very important-- I'm not seeing a good reason yet why one would break that with multiple, nearly identical dimensions.

Well, if they were truly nearly identical then I would agree with you. However, given the example of different sections of the chart of accounts being broken out into separate dimensions I can't see how they are nearly identical. They only characteristic they share is that they were all sourced from the chart of accounts. Most, if not all, of the elements would be different.

Everyone has their preferences for this and I understand the pros and cons of both. My experience has been that when I present the choices, together with the pros and cons of each approach to a client, they invariably choose to have separate dimensions. Can't say I blame them. If I were in their shoes I would likely make the same decision. Why spend money on a workaround, i.e., breakable subsets (breakable by clicking on Show All), or active form shenanigans to hide elements that should never show up in their inpuit form or report in the first place.

[mattgoff]

Decades of database development show that normalization is very important-- I'm not seeing a good reason yet why one would break that with multiple, nearly identical dimensions.

Well, if they were truly nearly identical then I would agree with you. However, given the example of different sections of the chart of accounts being broken out into separate dimensions I can't see how they are nearly identical. They only characteristic they share is that they were all sourced from the chart of accounts. Most, if not all, of the elements would be different.

Everyone has their preferences for this and I understand the pros and cons of both. My experience has been that when I present the choices, together with the pros and cons of each approach to a client, they invariably choose to have separate dimensions. Can't say I blame them. If I were in their shoes I would likely make the same decision. Why spend money on a workaround, i.e., breakable subsets (breakable by clicking on Show All), or active form shenanigans to hide elements that should never show up in their inpuit form or report in the first place.

I partially agree, in that it's somewhat similar to the one dim vs two for time. But the OP was heading along a different, much more broad line of thought, e.g. different department lists by cube.

Here's my problem with breaking a set of like things (COA) into multiple dims: you solve your end-user usability problem (maybe), but if you ever run into a situation in the future where you need both sets elements in one dim, you're out of luck, and you'll have to build a third dim. Database normalization is all about simplifying things are much as possible while still allowing the greatest flexibility. Your solution may allow more simplicity (up for debate), but it definitely reduces flexibility.

TM1 provides tools to filter elements from dimensions and makes it very easy to do so in a repeatable and, for some, dynamic fashion (subsets, zero suppress, and Active Forms/MDX). I don't consider "Show All" to be breaking a subset-- just reselect the subset. That's "Using the Cube Viewer 101" for new users. I don't consider using "Active Forms" to be shenanigans any more than I consider "Suppress Zero" to be witchcraft.

Matt

[lotsaram]

I think this is a good discussion. I don't buy the sparsity argument at all, as Matt said handling sparse data is something TM1 does exceptionally well. For a situation with a CoA dimension for a "general use" kind of cube I wouldn't usually go with say splitting to an income statement, balance sheet and cashflow dimension for example for use in different cubes feeding different reports. Typically this requirement is better served up all from the same cube and most if not all users would have access to the full CoA and finance users SHOULD know where to look to find the data (yet every day I am amazed by those that don't, bt that's another long and sad story) and the ones that don't will usually be consuming pre-built reports.

However I certainly agree with Tomok that there are definitely times when splitting dimensions makes a lot of sense. For example where there is certain data input that pertains to only a certain subsection of accounts and the data entry also requires different dimensionality to the main cube. Or when you have a "sub-ledger" type cube such as revenue and direct marketing expenses that can be tied to a product or accounts payable to a customer or accounts receivable to a vendor where is doesn't make sense to reproduce the full CoA in the sub-ledger cube. However by far the biggest case for separating dimensions in the appropriate circumstances is to enable differential element security. More likely this wouldn't be for a CoA dimension but would often be for a cost center or some other org structure dimension or most likely for a version dimension. For example it is a common requirement that "budget" should be read only in this cube but writeable in another. I have seen some really ugly workarounds for this situation (and been caught out a few times myself) because the model designers didn't think through the security model and have the foresight to separate such dimensions.

[CiskoWalt]

I have found that one advantage to having cube-specific dimensions is to reduce the possibility that a user will create a view that exceeds the defined maximumn view size.

By default the max view size on the 64-bit software is 500 MB. (Yes, you can increse this).

The view size will be nunber of rows x number of columns x 16 Bytes. So if there are fewer elements selected in the view, then the user will not have the chance to select them when creating a view.

Pressing the 'suppress zeros' button will not reduce the size of the View. This option is executed on the client not the server.

-Walt