Is it ok to use cube based reports even if we dont have drill up drill down scnearios
I have made it a rule to use it only in the above case and was wondering if its correct
Hi,
Cube based reports can offer much improved report performance and simplicity of report design/reduction of report development costs. So yes I use them regardless of whether any drill capability is required.
Rgds
Rob
Hi
I don't think you only need to use a cube when you have drill up/down requiremnts. I use cubes for a lot of different reasons but mainly because they make it a lot easier to do period based analysis (e.g. month on month, month on same month previious year etc) and also because of member sets which can really simplify layout requirements.
For an example
I've just replaced a list report that was using case statements to out amounts into month buckets, using a crosstab based on a cube I was able to just add the month hieracrchy which split out the amounts into months. This had a massive improvement on performance.
Using a cube gives you access to some really useful mdx functions which can be difficult to code against a relational source.
Another reason for using a cube is that you can restrict the amount of data in the cube so that it is just a subset of the database. Again that can have a beneficial impact on performance (in fact there seems to be an almost immediate improvement in performance just from using a cube).
There are of course downsides as well. You need to factor in the amount of time required to create/update the cueb as part of your batch. Also you need to be very careful how you design the cube (I'm talking about a transfomer cube, not sure if you get the same issues with a TM1 cube). If you can have mulitple lower level members in different drill paths ina dimension transformer will assign the member a number to make it unique (e.g. I have regions, areas, activities as a dimension, the same activity can be in multiple drill paths e.g. region1, area1, activity1 or regon2, area2, activity1, to make the activity1's unique transformer adds a number i.e ~123 etc). If you then have to change the structure of the cube or additional members get added the number could change. If you've used this member in a report then the report could break or maybe return incorrect data so you will need to hack the xml to change the number.
I know that sounds a bit scary but it's not too much of an issue. You just need to be careful hwo the cube is designed. If you see and tildas (~) in the mdx then you may have an issue.
Having said all that I think cubes can have great benefits and you could definatley use them in situations where you don't want to have drill up/down.
Hope that helps
Quote from: wyconian on 03 Apr 2012 03:20:19 AM
If you can have mulitple lower level members in different drill paths ina dimension transformer will assign the member a number to make it unique (e.g. I have regions, areas, activities as a dimension, the same activity can be in multiple drill paths e.g. region1, area1, activity1 or regon2, area2, activity1, to make the activity1's unique transformer adds a number i.e ~123 etc). If you then have to change the structure of the cube or additional members get added the number could change. If you've used this member in a report then the report could break or maybe return incorrect data so you will need to hack the xml to change the number.
I know that sounds a bit scary but it's not too much of an issue. You just need to be careful hwo the cube is designed. If you see and tildas (~) in the mdx then you may have an issue.
Great post wyconian ! However, I would add here that the downsides you indicated are valid but only arise when the cubes have been made with drag-and-drop technology instead of making it properly. Transformer would add tildas only when the 'Category Code' property is left blank. In that case, you are leaving it upto Transformer to assign Category Codes by itself which causes this problem. One member coming in different drill paths can also be handled by assigning MUNs (Member Unique Names). There is also a limit of 65000 members belonging to one parent which can also be handled even if IBM says it cannot be done.
I personally favor DMR as a technique and am no cube expert, but C7 cubes are very bad in the department of attributes. So ,with large amounts of descriptional data you might look elsewhere. From what I gathered SSAS is much better equiped in this respect
Quote from: cognostechie on 03 Apr 2012 02:44:21 PM
There is also a limit of 65000 members belonging to one parent which can also be handled even if IBM says it cannot be done.
how do you handle the 65000 restriction?
Insert an artificial level between the two so that the parent:child gets reduced below 65000. That way you can still have all of the members. You can use the substring function for that.
Thanks CognosTechie, I'll try manually assigning the categoy codes, see if that helps resolve my issues. It's getting to be a pain manually hacking the report xml everytime the cube gets updated.