Confusion in table design for BI Report

[tellranga]

 

 

 

 

 

 

 

 

 

 

I was given the output of a sample report as below to generate one as a POC,

Year                      2000    2001     Growth
Product                 sales   sales    sales
Total                      1600    1800     12.5%
---Audio CD          600         900     50.0%
---Video CD          500         250    -50.0%
---Books                500         650     30.0%
------Fiction            300         300     00.0%
------Non-fiction    200          350     75.0%

For the above BI Report, I can plan my mondrian xml to have

• Dimension as Year, Product
• Measures as Yearly sales (2000, 2001)
  and finally
• Growth sales as a calculated member in mdx query.

Assuming my above conventions are correct,

• how many tables should I create, and
• on which key field, should I have to set the parent child relationship across tables

I am confused in desighning the tables, so can you please pass the sql scripts required to creat the table ?

[swood]

fact table

• Time id foreign key
• Product id foreign key
• Sales measure column

time dimension table

• time id primary key
• Year
• Month
• Day

Product dimension table

• Product id primary key
• Product class
• Product name
• ...

 

Sherman

Jaspersoft

[tellranga]

 

Thanks  Sherman,
 I need one more clarification,
Since all my Analysis Report is built on complicated query refering multiple tables, I had a tough time building the mondrian xml ( despite using the workbench tool to generate such xml).
So, I decided to create a seperate view for each analysis report based on the corresponding query which the report is dependent on.

To eloborate further I will take your above table defenition as an example,

1. If the base sql Query for the above report is
   select year, product name,sales, some expression 'sales growth'
   from product,time,fact
   where product.product_id=fact.product_id and time.time_id=fact.time_id
   order by year,product_name
2. I will create a view caled SalesByYear_View with the same query defenition,
   create view sales_by_year_view as
   select year, product name,sales, expression 'sales growth'
   from product,time,fact
   where product.product_id=fact.product_id and time.time_id=fact.time_id
   order by year,product_name
3. Next I will generate my mondrian xml focussing only on a single object SalesByYear_View .

It helped me to resolve two major issues,

• generating mondrian xml became simple keeping only one object / view in front of me
• calculating expressions for fields like sales growth % also became simple (since the expression is already defined in view)

So my question is in terms of best practice for Analysis Report,

• my decision to create a seperate view for each analysis report based on the corresponding query which the report is dependent on is right or wrong ?

Note - This means if I have to generate 10 differnet Analys report, I will end up creating 10 different views.

[swood]

This sounds like a lot of work for no good reason. I don't know your data model, but you should be able to define a central fact table for the most detailed granularity of analyis, and have aggregate tables related to the fact table for performance purposes.

 

You should be able to define 1 cube that covers both of those situations, and in general, you should be able to have 1 cube for a logical set of data.

 

Sales growth is a very simple calculated measure in the schema. Have a look at the example Foodmart schema - the Sales cube in that defines "Profit Growth" which you can use as an example.

 

Sherman

Jaspersoft