TM1 Forum

Current Version: Planning Analytics Local (Tm1 and Workspace)

Hi there,

I'm wondering what are the impacts of building a sparse cube with
• Company Branch
• Year
• Month
• Day
• Version
• Products
• Metrics

And the 'Metric' Dimension has the elements:
• Unit Sold
• Sale Price
• Product Cost
• Product Tax
• Revenue
• Sale Cost
• Tax

The 'Unit Sold' and 'Sale Price' are recorded daily, but 'Product Cost' and 'Product Tax' are recorded yearly. So, the 'Product Cost' and 'Product Tax' data are loaded only on 'January', 'Day 01' and 'Actual' (version) elements. Then, 'Sale Cost' and 'Tax' are calculated by:

['Sale Cost'] = DB('Sales', !Company Branch, !Year, 'January', 'Day 01', 'Actual', !Products,'Product Cost') * ['Unit Sold'];

['Tax'] = DB('Sales', !Company Branch, !Year, 'January', 'Day 01', 'Actual', !Products, 'Product Cost') * ['Unit Sold'];

I'm aware that the best practice is to have separate cubes to limit the amount of times that the data is stored, also to avoid confusion when users are retrieving data. However, in this case, the data is stored on "dummy" elements and the users will have a PAW view with hidden titles for those who are needed.

The business requirements that I described is just an example, what I wanted to explain is the aproach of using one cube vs multiple cubes. I'm considering to use one cube aproach in many others scenarios. But I'm not sure of all the negative impacts. This aproach brings me flexibility to recreate the model in other clients in an easy way without recreating cubes to attend their needs (some will need 'Sale Cost' by Month, and so on)...

I'm sorry for the grammar, English is not my first language.
If a better explanation of the case is needed, please let me know.

Maybe you can create the element
Month : No Month
Day: No Day

In this case , user can easy to know why [Product Cost] and [Product Tax] have only one amount in a year.


['Sale Cost'] = DB('Sales', !Company Branch, !Year, 'No Month', 'No Day', 'Actual', !Products,'Product Cost') * ['Unit Sold'];
['Tax'] = DB('Sales', !Company Branch, !Year,'No Month', 'No Day', 'Actual', !Products, 'Product Cost') * ['Unit Sold'];

Thank you Howard! I'll follow that idea! It makes easy to maintenance too...

But I'm still in doubt about which method is the better approach to this case. I guess the performance is reduced on the One Cube Approach, but i don't know how much. If it doesn't loose too much, I'll prefer One cube to gain on maintainability.

Does someone use this approach to? Is it a common thing?

howard40116 wrote: ↑Fri Sep 20, 2019 1:49 am Maybe you can create the element
Month : No Month
Day: No Day

In this case , user can easy to know why [Product Cost] and [Product Tax] have only one amount in a year.


['Sale Cost'] = DB('Sales', !Company Branch, !Year, 'No Month', 'No Day', 'Actual', !Products,'Product Cost') * ['Unit Sold'];
['Tax'] = DB('Sales', !Company Branch, !Year,'No Month', 'No Day', 'Actual', !Products, 'Product Cost') * ['Unit Sold'];

I also use this idea of the 'dummy element' too but we were discussing the other day about the idea of having this 'dummy element' alone in an alternative hierarchy, does anyone know if it'll be better for the performance of the model? How can we measure/see the impact of both approaches?

luizg2019 wrote: ↑Thu Oct 03, 2019 8:29 pm I also use this idea of the 'dummy element' too but we were discussing the other day about the idea of having this 'dummy element' alone in an alternative hierarchy, does anyone know if it'll be better for the performance of the model? How can we measure/see the impact of both approaches?

It won't make a shred of difference from a performance perspective if the dummy leaf element is in a separate hierarchy or not. Once you have hierarchies all data is stored arainst the element in the Leaves hierarchy, which contains the union of all leaves from all availabe hierarchies.

From a useability and design perspective if you want to be able to mix day level data with no date data and cummulate both then the element needs to exist in the main hierarchy as well. (This doesn't mean you can't ALSO have the elemetn by istelf in a stand alone hierarchy to make finding the element and entering data easier).

lotsaram wrote: ↑Fri Oct 04, 2019 7:06 am It won't make a shred of difference from a performance perspective if the dummy leaf element is in a separate hierarchy or not. Once you have hierarchies all data is stored arainst the element in the Leaves hierarchy, which contains the union of all leaves from all availabe hierarchies.

From a useability and design perspective if you want to be able to mix day level data with no date data and cummulate both then the element needs to exist in the main hierarchy as well. (This doesn't mean you can't ALSO have the elemetn by istelf in a stand alone hierarchy to make finding the element and entering data easier).

Got it! It doesn't make a difference using hierarchies or not...but would it be better (from a performance perspective) using separates cubes without time dimensions for those elements that don't use time than using one cube with all dimensions, even that are elements that don't use time (using "dummy elements" for those)?

Thank you.
Tiago.