SQLScholar
Programmer
Hey all,
If you have an OLTP system (SQL 2008 r2) and you wanted to have a dashboard running off it that was Near Live information (15 mins max). How would you go about it? For safeties sake lets say its a normalised database that has 1,000,000 customers 10,000,000 orders, 10,000 products, 10 sales regions, 100 sales offices, and 1,000 sales staff. You can guess the structure here (this is a made up idea - just to get the principle).
So now you need to create a dashboard that shows sales (numbers and volumes) for today and yesteday first at company level, then drill through to sales region, then to office, then to sales staff, then to individual sales.
Now if this was an system that only needed to be updated nightly, i would create a denormalised table to get the different layers so that the dashboard could run quickly.
But with a live system the build time of the DN tables or even cubes (as this example has been simplified) would take too long to get the information onto the dashboard. But i fear that running on the OLTP schema would be too slow to run a dashboard off.
We have the OLTP schema replicated onto a different server for reporting. So effectively the OLTP schema is slow for reporting, the OLAP schema\cubes take time to build.
Basically is there a way round this?
Thanks for any input given.
Dan
----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch
Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
If you have an OLTP system (SQL 2008 r2) and you wanted to have a dashboard running off it that was Near Live information (15 mins max). How would you go about it? For safeties sake lets say its a normalised database that has 1,000,000 customers 10,000,000 orders, 10,000 products, 10 sales regions, 100 sales offices, and 1,000 sales staff. You can guess the structure here (this is a made up idea - just to get the principle).
So now you need to create a dashboard that shows sales (numbers and volumes) for today and yesteday first at company level, then drill through to sales region, then to office, then to sales staff, then to individual sales.
Now if this was an system that only needed to be updated nightly, i would create a denormalised table to get the different layers so that the dashboard could run quickly.
But with a live system the build time of the DN tables or even cubes (as this example has been simplified) would take too long to get the information onto the dashboard. But i fear that running on the OLTP schema would be too slow to run a dashboard off.
We have the OLTP schema replicated onto a different server for reporting. So effectively the OLTP schema is slow for reporting, the OLAP schema\cubes take time to build.
Basically is there a way round this?
Thanks for any input given.
Dan
----------------------------------------
Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch
Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------
