Working with Management Data Warehouse, a reporting tool for Data Warehouses introduced in SQL Server 2008 and never changed since, makes a lot of limitations visible and also the lack of in-depth documentation by Microsoft. They tried to make the documentation better accessible in SQL Server 2012 but the product itself did not change.
What are the main limitations?
MDW uses snapshots, which is actually a 'easy' way to handle all the issues involved in collecting ongoing activity. It is like taking 10 pictures per second of a dancing girl: you see the changes in every movement but if you want to know what is the total distance the girl covered then it can get rather complex. So you have for 1 minute of pictures and I bet you will break your teeth on that one. If you want to see the distance the girl danced in a week, month or year go for it!
This gets us into the basic question of BI. Why are we collecting data? What do we want to do with it? Apparently taking 10 pictures a second with a Nikon F4, which is a great camera, but it did not help us finding a reply to the question: what distance did the girl cover. We need other tools like a simple step counter.
It is the same with MDW: we collect a lot of data but Microsoft never asked it's customers what they wanted to collect.
Questions like what query did blow the performance in my data warehouse at day x can be answered but you will never find the user involved.
The same with question on server availability. A valid question in a database world where words like bronze, silver or gold SLA's are standard now. Microsoft is talking of 99.999% uptime for a clustered server but can you show it? No, you have to use TSQL code and create your own report.
MDW is designed from a technical point of view trying to make it easy to examine issues on your data warehouse but the result kind of drowned in a waterfall of data poured out over us.
Questions like: when did my DWH drown in the TSQL requests fired and who did it are impossible to answer without diving into a day to day analysis like the pictures of the dancing girl above.
So Microsoft tells you you can do this yourself. Of cause you can extend MDW but that is not an esy task. Queries are stored in XML (why did they choose that) and XML does not like '', >, <, etc. And TSQL does not like
<, >, ',etc.
More about this issue in this article: XML parsing errors
That will not resolve your issues in MDW but it simply shows you that you have to change all your comparisons in your custom extensions.
If you look at the article Microsoft published on extending the MDW you will run into trouble within the first minute. Check it out: Create a Custom Collection Set That Uses the Generic T-SQL Query Collector Type (Transact-SQL)
If you do exactly what is written in that example you create a new data collection set. If you collect the data in that example over 3 months using the snapshot mechanism your table will be humongous.
What are the main limitations?
MDW uses snapshots, which is actually a 'easy' way to handle all the issues involved in collecting ongoing activity. It is like taking 10 pictures per second of a dancing girl: you see the changes in every movement but if you want to know what is the total distance the girl covered then it can get rather complex. So you have for 1 minute of pictures and I bet you will break your teeth on that one. If you want to see the distance the girl danced in a week, month or year go for it!
This gets us into the basic question of BI. Why are we collecting data? What do we want to do with it? Apparently taking 10 pictures a second with a Nikon F4, which is a great camera, but it did not help us finding a reply to the question: what distance did the girl cover. We need other tools like a simple step counter.
It is the same with MDW: we collect a lot of data but Microsoft never asked it's customers what they wanted to collect.
Questions like what query did blow the performance in my data warehouse at day x can be answered but you will never find the user involved.
The same with question on server availability. A valid question in a database world where words like bronze, silver or gold SLA's are standard now. Microsoft is talking of 99.999% uptime for a clustered server but can you show it? No, you have to use TSQL code and create your own report.
MDW is designed from a technical point of view trying to make it easy to examine issues on your data warehouse but the result kind of drowned in a waterfall of data poured out over us.
Questions like: when did my DWH drown in the TSQL requests fired and who did it are impossible to answer without diving into a day to day analysis like the pictures of the dancing girl above.
So Microsoft tells you you can do this yourself. Of cause you can extend MDW but that is not an esy task. Queries are stored in XML (why did they choose that) and XML does not like '', >, <, etc. And TSQL does not like
<, >, ',etc.
More about this issue in this article: XML parsing errors
That will not resolve your issues in MDW but it simply shows you that you have to change all your comparisons in your custom extensions.
If you look at the article Microsoft published on extending the MDW you will run into trouble within the first minute. Check it out: Create a Custom Collection Set That Uses the Generic T-SQL Query Collector Type (Transact-SQL)
If you do exactly what is written in that example you create a new data collection set. If you collect the data in that example over 3 months using the snapshot mechanism your table will be humongous.
Comments
Post a Comment