Use of optimized tables to increase the performance of the Xcelsius dashboard

We had requirement for the Xcelsius dashboard with 4 tabs and approximately 10 reports for comparison of brand performance, sales, demand, overall market summary, country/region wise brand performance and growth etc.

Customer had specific requirements like
1. Dashboard should refresh on open
2. Dashboard should refresh in 20sec.

Generally we have dataware house, then ETL developer builds marts and BO developer creates universe with star or multi star schema. All the fact & dimension table relationships (joins) comes into picture, we need to define context , need to solve loops.

Middle ware table may be needed to joins two table, so number of joins will automatically increase causing more data retrieving time. This all depends on size of the tables too. In our case all the fact tables were very huge in size.

To avoid joins at universe level  we suggested to create optimized tables (derived or individuals tables) at database levels and created materialized views for every KPI. We had 5 to 8 KPIs with different combinations.

First we analyzed the dashboard reports to know how we can plot the reports in Xcelsius, what dimensions and facts are required for particular reports and then built materialized views. ETL team schedules this materialized views as per the customer requirements(Monthly/ Quarterly)

As these tables are reports centric there were no joins in universe so Webi reports required for live office became very simple. Triggers were applied on dashboard, so when user selects the particular filter it will directly hit the respective optimized table.

As customer wanted to see many KPIs at time on one dashboard we used this critical method of creation of materialized views on ETL side.

For all these efforts we got customer appreciations. 🙂

We can suggest this approach specifically for the dashboard development.

(Tools/database used: Xcelsius 2008enterprise, Live Office, BOXI3.1,Oracle 10 g, informatica 8.6 ).