August 15, 2011 at 5:11 am
Hi - we're starting to require a bit more flexibility from our Data Warehouse to return more information for a single data point.
The Warehouse primarily stores server performance data & at the moment the schema is heavily de-normalised with just a single numeric fact for each data point (e.g. CPU%)
However we are now looking at more complex data such as Top 10 Exchange Mailbox users.
One way of achieving this would be to create a new fact table with just the (composite) primary key & have an XML column in this new table to store more complex data.
This would offer a great deal of flexibilty & would only be used in a very small number of scenarios.
Am I mad to consider this?? this seems like a nice solution - has anyone else tried something similar?
Users will not have the ability to search on the XML data - it will just be returned as a whole during a normal time range based query.
I hope this makes some kind of sense - I'd be realy interested to hear people's opinions.....
Thanks!
August 15, 2011 at 4:20 pm
Why not create a normalized schema for the data so that it can more easily be validated and queried? I don't understand what advantage you think XML has in a data warehouse.
August 16, 2011 at 6:34 am
It's possible to query data warehouse tables using standard TSQL. This would keep you from having to create a second set of items. You could also consider creating a query as a view.
SSAS cubes have a query language, MDX, that allows you to query cubes. It's not the easiest language, but there are guides out to help.
August 19, 2011 at 7:49 am
I'm with David - I don't understand either what flexibility or advantage you think XML will bring to the table in a data warehouse environment.
In my case I'll suggest to go with a true dimensional model - FACTs are unique, not a good idea to have multiple FACT tables describing the same data; if that's the solution then chances are there is a big flaw on the basic design.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply