Has anyone built Generic/Flexible Cubes?

Post Reply
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Has anyone built Generic/Flexible Cubes?

Post by RJ! »

Hi All,

I've been working with our users on some of their pain points in relation to their templates and TM1 performance came up quite often.
On investigation we could see that it was quite common for the teams to have multiple spreadsheets all linking to TM1 via DBRA/DBRW formulas, with a spagetti of inter-linking between them.

Now if these templates could instead feed this shared data into TM1, then we could remove the "spagetti" and increase performance.

My proposed solution was to build a Blank Canvas / Generic Cube with 4-6 Dimensions.

Biz_Team - For Team/Department (i.e. Marketing, Finance, Sales etc)
So it'd be something like:
Total Team
MKT
FIN
SAL

Period - Leverage existing Period Dimension
(i.e. the usual Years, Qtrs, Mths stuff)

Biz_DataDesc - For Table/Data Description
(i.e. Sales Margins, Profit per HC etc)
The Dimension would just a list of number from 1-> However many are needed (say 20).
Each Team name would be an Attribute of the dimension so that you can link the Table Name to Department.
Total
1
2
3
4...
etc

Biz_Dim1 - BizDim4 - Generic Dimensions
Then within each Dim, just a list of number from 1-> However many are needed (say 100).
Total
1
2
3
4...
etc


The idea is for a Table Template to be created in their templates so that the Templates that calculate the data can feed the results into appropriate Biz_Team/Biz_DataDesc cells in the cube.
Then the templates that consume would have the same structure to extract the data.

If all goes to plan, the teams can then go and manage their own "adhoc" data feeds into TM1. If anything looks particularly "juicy" (aka useful) we can then look it formalise it into a proper process.

Has anyone done this before?? Was it successful?

Thanks,

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

Re: Has anyone built Generic/Flexible Cubes?

Post by lotsaram »

I don't know that your are going to get many bites on this fishing expedition unless you define a bit more what exactly you are talking about.

"Flexible" or "Generic" cubes yeah sure I have built some and I'm sure a lot of other people have also. But the real question is how flexible and how generic?

In the scenario you describe it sounds like fit for purpose, logically, and well designed cubes + a healthy dose of user training are more like what is required as opposed to "generic cubes".

Generic "container" type cubes can sometimes be useful for delivering OOTB pre-build reporting or dashboards where the layout is static and there is very limited dimensionality (2, 3 max 4 or 5 dims). But you need to bear in mind that wherever dimensions and dimension element names become totally generic then the cube becomes very opaque and navigating the cube and finding where the data is can become very difficult. At which point you realise your oasis is a mirage.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Has anyone built Generic/Flexible Cubes?

Post by RJ! »

Hi Lotsaram,

The goal was to shift the store of data out of Excel and into to a central repository in TM1.

We have a few templates that have "links" to other templates and recalculating can take 20mins or more to complete. My thinking is that it'd be more efficient to have this as a retrieve from TM1 rather and a linked file.

With what I was proposing above, each business could essentially create as many "tables" in TM1 as they liked without requiring the Admin team's involvement.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Has anyone built Generic/Flexible Cubes?

Post by rmackenzie »

Multiple choice question: you work in a pasta restaurant and every day you arrive for work and the pot of spaghetti and pot of bolognese are bubbling away - the night chefs left it that way for you. Every day you serve up bowl after bowl of spaghetti and bolognese to hungry customers. You have so many customers it is all you can do to keep up with the demand in the dining room and have little time for keeping up with the kitchen maintenance. Day after day the little spots and remnants of spaghetti and bolognese mount up on the floor and you tell yourself you will get round to cleaning them up, when you find the time. One day, there are not so many customers and you realise you have that time to attend to the kitchen. Do you:

a) Sweep up all the pasta and sauce bits off the floor and put them back into the pot so you can serve back to customers?
b) Mop the floor and throw the waste food in the bin? It is getting pretty gross down there.
c) Make penne carbonara because it is time something different was on the menu?
d) None of the above.
Robin Mackenzie
RJ!
Community Contributor
Posts: 219
Joined: Mon Jul 23, 2012 8:31 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Has anyone built Generic/Flexible Cubes?

Post by RJ! »

Option B!
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Has anyone built Generic/Flexible Cubes?

Post by Alan Kirk »

Analogy of the year. But more importantly...
rmackenzie wrote:a) Sweep up all the pasta and sauce bits off the floor and put them back into the pot so you can serve back to customers?
I believe that this is the first viable explanation for how Performance Muddler came about.

I was thinking along the same lines as Robin's post but didn't think to put it quite as poetically. I agree entirely with Lotsa; In all seriousness when you over-generalise there is a very great danger - indeed almost a certainty - of people losing track of what is supposed to go where in the cube, why particular settings were created the way they were, what they need to do for future settings and so on.

It should be possible to do a bit of analysis on what all of these interlinked tables are and what purpose they serve, and come up with a cube which has a definite structure but is flexible enough to adapt (through additional measurement elements and the like) to future uses. A standard cube to handle import parameters (path, file name, etc) would be an example of this. In my own setup I have one for system processes and chores and one for user-triggered processes and chores. Sure, the users can change the parameters for the processes that they're allowed to run but if they change a file path they have to change the element that REPRESENTS the file path. What you're describing would have user A having some value over here, user B having an equivalent value way over there...

Anarchy may have a certain amount of appeal at times (like 2am after a TM1 user conference when a bar brawl breaks out over whether one or two time dimensions should be used in a cube) but it's probably not the best way to run a computer system.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
blackhawk
Community Contributor
Posts: 136
Joined: Thu May 29, 2008 2:29 pm

Re: Has anyone built Generic/Flexible Cubes?

Post by blackhawk »

RJ!

Yes, actually we did build something like that. It was called TM1QuickStart. It basically would allow you to point to a series of data files and it would either load or automatically create the cubes and load them on demand. The user could pick out what was a dimension, what was a measure etc, and then it would just load it. It had the intelligence to know that it was loading partial data, or full re-loads. It would then build a dimension based on fields you specify, taking care to watch out for uniqueness etc.

If you are looking to do this internally, it will take a lot of "genericizing" of scripts and using cubes for metadata; as well as some front end that does a bit of work outside of what TM1 can do, because the file handling capabilities of TI are ....shall we say...."limited".

I have included a screenshot of the form design (I don't have a working copy of the actual application). It doesn't do it justice because the form is actually dynamic and alters what is displayed based on user input. It was pretty cool and fun to work on. Much later Insight came along and we thought it was supposed to make this easier, and replace what we built, but it never did. It is still in use today by several clients, but we have moved on to bigger and better things since then.
Screenshot of an application for loading data from users.
Screenshot of an application for loading data from users.
Screenshot.png (53.2 KiB) Viewed 5864 times
Hopefully this will give you some ideas. Good luck in your journey!
Post Reply