So continuing with the BI theme lets take a look a small example.
Sample Code: http://quick-max.com/samples/BI/BI-Lite.zip

So i was thinking how much SQL would I have to write to actually get this to work
at the smallest level. The answer if one simple view that gave me Primary Key, and foreign key mappings that i called PkFk (go figure). The rest of the data retrieval was handled by Subsonic package, why write a whole data layer when all that i am interested in is Table names and keys.

The code only really has two files that you want to take a look at default.aspx.cs and TblGraphItem.cs. Also you will need to run the create view sql script on the pubs database….but you can basically point it to any database and change the code slightly.

so what were my results, very simple use reporting tool with some drill down. Where i didn’t have to write table specific code.

Examples: ( ‘->’ will be used to signify drill down)

1. Titles -> Sale

to

so you can see that I output what sections drill down to what data. I have over simplified the notion of BI but this is the basis, the difference is all you need is the View and its plug and play from there on… i don’t have to massage my data to accommodate reporting.