A little bit of back ground information:
We have started to develop our Data Warehouse recently and only had a Production Environment. The Data Warehouse is growing and we now see the need to have a development and production environment. Probably should have done this from the start...However.... We have several reports and dashboards built. We have DEV environment up to sync with PRD and are now wanting to copy and test our reports in DEV to verify that they match. Then moving forward all development will be in DEV and be pushed up to PRD.
Our data warehouse is built in SQL Server Management Studio, SSIS, with cubes built in SSAS. For many of the reports we are linking to the cubes in excel pivot table and then populating an excel page.
Problem:
Our Cubes in SSAS DEV and PRD have the same structures. If I add a connection string to the pivot tables all filters have to be removed and the connection string has to be added to all pivot tables. It seems like this should not have to be done from PRD to DEV or DEV to PRD. This can be quite a bit of work if you have various filters and many pivot tables populating a master worksheet.
I also have tried editing the connection string in a renamed version of the sheet. Simply going under Connection Properties and editing the connection string source from DEV to PRD. This seems like a logical way to avoid having to reset your pivot filters and your connection to all your pivot tables. However when this is done Excel freezes
This could be very relevant for anyone developing a data warehouse and also uses excel for reporting. The development environment could be pushed up to production and only have to edit the connection string to update the reports. Especially if you were building reports off of several pivot tables with multiple filters.
Thanks
If you use this strategy to test your cube, you'll quickly see that you'll need a lot of time to compare the excel sheets loaded from Prod and the excel sheets loaded from DEV. You'll also discover when changing your structure that you'll be in trouble to compare your figures with Excel.
I'd advise to write your queries (or extract them with OLAP pivot extension) in MDX and make usage of NBi (http://nbi.codeplex.com) to create unit tests. NBi will do the job for you to compare your two results and find the differences (it will save your a lot of time). NBi can also be scheduled during the night so you'll have everything tested when you arrive next morning.
PS: with NBi, you'll also be able to test your structure or compare your figures in the database with the figures on your cube.
A little bit of back ground information:
We have started to develop our Data Warehouse recently and only had a Production Environment. The Data Warehouse is growing and we now see the need to have a development and production environment. Probably should have done this from the start...However.... We have several reports and dashboards built. We have DEV environment up to sync with PRD and are now wanting to copy and test our reports in DEV to verify that they match. Then moving forward all development will be in DEV and be pushed up to PRD.
Our data warehouse is built in SQL Server Management Studio, SSIS, with cubes built in SSAS. For many of the reports we are linking to the cubes in excel pivot table and then populating an excel page.
Problem:
Our Cubes in SSAS DEV and PRD have the same structures. If I add a connection string to the pivot tables all filters have to be removed and the connection string has to be added to all pivot tables. It seems like this should not have to be done from PRD to DEV or DEV to PRD. This can be quite a bit of work if you have various filters and many pivot tables populating a master worksheet.
I also have tried editing the connection string in a renamed version of the sheet. Simply going under Connection Properties and editing the connection string source from DEV to PRD. This seems like a logical way to avoid having to reset your pivot filters and your connection to all your pivot tables. However when this is done Excel freezes
This could be very relevant for anyone developing a data warehouse and also uses excel for reporting. The development environment could be pushed up to production and only have to edit the connection string to update the reports. Especially if you were building reports off of several pivot tables with multiple filters.
Thanks
If you use this strategy to test your cube, you'll quickly see that you'll need a lot of time to compare the excel sheets loaded from Prod and the excel sheets loaded from DEV. You'll also discover when changing your structure that you'll be in trouble to compare your figures with Excel.
I'd advise to write your queries (or extract them with OLAP pivot extension) in MDX and make usage of NBi (http://nbi.codeplex.com) to create unit tests. NBi will do the job for you to compare your two results and find the differences (it will save your a lot of time). NBi can also be scheduled during the night so you'll have everything tested when you arrive next morning.
PS: with NBi, you'll also be able to test your structure or compare your figures in the database with the figures on your cube.
0 commentaires:
Enregistrer un commentaire