June 8, 2007 at 5:03 am
Hi,
Currently we store our AS 2000 metadata in a SQL Server database rather than the Access database. When we moved it over we did see speed improvements and greater control over its back up. We have our AS 2000 on a separate server than our SQL database.
In AS 2005 the metadata is stored in an XML file format. They claim “you can expect better query performance than the case where the data is being retrieved from a relation database” *
Now I’m a bit of sceptic and would have thought that if XML retrieved data faster than a relational data base then what’s the point of having relational databases and why not do everything a database can do in XML. OK, Joking aside. Can anyone validate this claim and back it up with a plausible explanation.
The only reason I can think of is that they (Wrox) are referring to having the metadata stored on your OLTP or datawarehouse and that the metadata database would be contending for resources on a busy box where as XML files might not. If this is the case if put your metadata in a SQL database on a server that currently only has AS 2005 on surely that would be faster?
My main question is does anyone actually know how you migrate the metadata to an relational database from its native XML format?
Any suggestions appreciated
Howard.
* Professional SQL Server – Analysis Services 2005 with MDX (Wrox) P14 paragraph 3
June 8, 2007 at 8:33 am
I don't think you can (migrate the metadata to a DB), it's now filesystem based. Does mean that you might want to ensure your filesystems permissions are set up correctly though.
On the speed side, i doubt they were refering to the resource contention but you never know. THe metadata for AS2K contained a fair amount of XML as it was, so i wouldn't imagine there'd be that mich difference in performance.
Not trying to dis the wrox guys but i would also be lookign to the msft published Performance Guide (obviously the one for AS2K5) to see what, if anything, they say about implications of large metadata stores.
Steve.
June 8, 2007 at 9:00 am
Steve,
According to the Wrox gurs "Analysis Services 2005 provides you with the option of storing the data or aggregated data efficiently in a propriety format on Analysis Services instance or storing them in a relational database" Aaarrrr, are they talking about the metadata or the actual data in aggrgated form.
Either way I find it hard to understand how XML can be faster than a relational database.
It would be interesting to find out how to do it, but I cant find it in the Wrox book.
Howard.
June 8, 2007 at 9:14 am
The quote youve got there really looks like it is refering to the aggregations (and also to the base/actual data). With AS you have the partition of data an also some aggregations. You can chose what type of storage you want for both of these for every partition (ie quite flexable).
As to the speed of the XML, can't say i've ever had any speed issues with AS2K5 querying the metadata of a cube. AS2K dev enviornment was definitely slower when working with large objects (just my non-empircaly evidenced view).
Steve.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply