Daily Data Report Table from Existing Database

Question

Hi,

I've got a pretty lightweight SQL DB which is transactional throughout the day with users inputting data via a SQL / Entity Famework / WCF / SharePoint type stack.

I need to pull out from this database a daily view (BI report if you wish) of the data which is surfaced in charts at the web UI tier.

Now I know I can query the existing database using LINQ based on parameterised queries in real time BUT, the requirement is for 10 + charts to be displayed in a dashboard configuration whch places a heavy workload on LINQ - espeicially as there are alot of aggregations and calculations performed on the data.

What I would like to achieve is one or two database tables that consolidate the existing data on a dailiy basis, upon which to base my queries - I guess it's a data warehouse but on a much simpler scale - negating the need to run loads of LINQ queries on the real time data. This would save me a lot of processing power and improve the performance no end.

I would appreciate tips on the best tools to use within SQL server 2008 R2 to enable this without a fully fledged SSAS  /SSRS solution.

Thanks & regards,

MSF

Answer 1

Macintos hi,

I think you should try and use the "top down" method by doing the following:

1) define in a design document what exactly you want to see in the dashboard. (and of course in what platform, excel? your own code?)
this is the most important stage, you should write down for each chart what data you expect it to get including fields names and expected returned value.

2) design and "empty" query (VIEW OR Stored Procedure) that will hold the STRUCTURE of the data u need.

for example, if you need a table/chart of sales to customers totals:
you define (step 1) you need 4 fields to be returned: CusotmerName, CustomerKey (Unique), SaleAmount and SaleDate 
then you write a stored procedure OR a VIEW that will return the following

CREATE PROCEDURE spGetSalesToCustomer
AS

SELECT  ''     AS CustomerName
            ,''     AS CusotmerKey
            ,''     AS SaleAmount
            ,''     AS SaleDate

Now your query should be able to work with the UI.

3) create a table that holds the values of the query. (at the beginning this table/s could be empty)
at this point, make sure your Data Ware House is designed well so that all the queries you are going to write will take the data from the same source. you should put some logic here.

4) Write an ETL that will take the data from your current database into the new table/s,
The ETL should be running every night (or any other period that is good for u)

5) update the SP/VIEW so that the data will now be taken from the new table/s:

CREATE PROCEDURE spGetSalesToCustomer
AS

SELECT  tblMyNewTable.CustomerName     AS CustomerName
            ,tblMyNewTable.CustomerKey        AS CusotmerKey
            ,tblMyNewTable.SalesAmount        AS SaleAmount
            ,tblMyNewTable.SaleDate              AS SaleDate
FROM MyNewTable tblMyNewTable

of course this query now can be more complicated and have WHERE, JOINS, parameters and anything else you need,

what should be important is to NOT change the alias of the field so that no matter where the query is taken its data from, the UI won't be changing (unless adding/removing fields)

I hope this gives you a direction, if you want please feel free to ask more,

Shoshi

---

Shoshi r

Answer 2

Great answer - thank you - I was looking for ratification that the approach is an acceptable one and I take it from your answer this is a yes. I will use your starting point suggestions as a basis going forward.

Thanks again.

MS