BI Suite (2)

As I posted earlier, I went for the brave approach and imported a customer’s 3NF based DW design in to the administration tool and then created a denormalized business layer within the BI tool. This was a slightly more convoluted process than I expected as various logical and physical foreign keys needed to be declared to support the dimension objects. Soon I had all of the reference data hierarchies in place and tested in both Firefox and MS web browsers. I was surprised at the speed of hierarchy navigation, but a bit perplexed that my customer hierarchy drilldown fell over with spurious (or spurious to me) errors – it looks like I was trying to bring back to much information for my system configuration. Still I will not do anything else with it for now as it works well enough to demo, and I have limit on the amount of FOC work I can do in a year.

I have now added the two base sales (fact) tables to the demo system. This again took a bit more effort than I hoped as joining of fact to dimensions seems a little counter-intuitive. But it worked. I can now query and drill on two years of sales history. And performance seems far better than that from another user layer tool.
Capturing the SQL generated against the backend database revealed nice, clean SQL that looked well laid-out and more to the point was not studded with all of the junk you sometimes see in the captured tool-queries. And more to the point query rewrite works.