Will you create extra objects in the universe for different types of product?
Let say I have an object called “Total Revenue” and there are 3 kinds of Product - Good, Fair, Poor
Now if I want to get the relations between “Product”, “Date” and total revenue, most probably I will use crosstab to get the result. But the main problem is my client does not know much about IT. They want to drag-n-drop.
Also, there are several blocks in a single report. Some blocks only show Good Product. Some blocks show Fair and Poor products.
So in order to achieve this, I provide extra object
Total Revenue (Good)
Total Revenue (Fair,Poor)
…etc, something like that.
This case is simple because there are only 3 kinds of product. But in real there are thousands. Teaching users to use crosstab and multiple query with filtering may help, but I am not sure whether the clients could understand it easily.
Users should be given overview of how data can be pulled from the universe, explain them about each dimension, date objects and filters, which classes to select to choose objects etc
And since they are not IT savvy, you can as well have canned (predefined) reports created and give them user access
Actually, I am concern on both my data warehouse/universe design. I find difficulties when I try to implement a simple, flexible, and user-friendly design.
My first design is to create a fact table with single revenue measure. The user need to use crosstab to show the values. I think it is simple and appropriate design. However, I find that there are always some calculations appeared between each product. User cannot simply drag and drop to get the result. The calculation needed to be done in reporting level (Please check the following link for an example) while user does not have enough skill to do that
Another approach is that I create a fact table with three measures, separating the total revenue into 3 facts. i.e., Good Revenue, Fair Revenue, and Poor Revenue. In that case, they do not need a crosstab. They will be happy and simply drag and drop all they want. Calculation is much simple than the one above.
But I think this approach violates general designs. Let’s say it will be a mess if there are 1000 products.
Depending on the data model you have and the report requirement, derived objects should be created in the universe and the remaining calculations to be done in the report
And as I said earlier, if users are not trained on using the tool and are just expected to do a drag & drop, then the calculations will have to be carried out in canned predesigned reports. Because all those report side calculations may be unavoidable in a crosstab, vertical or horizontal tables
So either train the users to do such tasks or give them canned reports
(BOB member since 2008-12-18)
(BOB member since 2005-07-18)
(BOB member since 2008-12-18)