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
Has anyone built Generic/Flexible Cubes?
-
- 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?
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.
"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.
-
- 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?
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.
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.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Has anyone built Generic/Flexible Cubes?
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.
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
- 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?
Analogy of the year. But more importantly...
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.
I believe that this is the first viable explanation for how Performance Muddler came about.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 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.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Re: Has anyone built Generic/Flexible Cubes?
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.
Hopefully this will give you some ideas. Good luck in your journey!
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.
Hopefully this will give you some ideas. Good luck in your journey!