SQL Server 2005 Analysis Services
SQL Server 2005 SP2 is finally available... http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=d07219b2-1e23-49c8-8f0c-63fa18f26d3a
Since nobody else on my bloglist reported this (at least I didn't notice)... Here you find the KB article with the fixed issues: http://support.microsoft.com/kb/913090/en-us. And here is the download: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=cb6c71ea-d649-47ff-9176-e7cac58fd4bc
Wow, I really expected to see that download later... But I got some hints that it will we available in April since every vendor has to ship the version TPC benchmarks are based on a defined time after publishing the results. I'm not aware of that timeframe, but from that timeframe you could expect to see SP1 before May...
Let's see how things developed...
Thomas
So we’re getting closer and closer to RTM and we all hope that Microsoft will deliver a perfect product after such a long time of Betas (sorry, IDWs and CTPs). But you can be sure that this will not be a 100% finished product, just as Windows 2003 and other releases were not perfect on day one. I’m not one of guys telling you “wait for Service Pack 1”, SQL 2005 is a too good product to spend more time without it. But I’m sure you have to expect some issues mostly related to performance.
One issue we got aware of is a performance flaw when you use NON EMPTY with “non trivial” calculated members (well, this seams to be starting with already quite trivial calculations…). We had queries running for minutes and asked Microsoft why they take so long. They suggested exchanging the calculated member with the formula behind it. We had this with just a ParallelPeriod function we used in a calculated member. When we changed the query to use the base measure and put the ParallelPeriod in the query the performance was very good (some seconds for a quite complex MDX).
Remember that NON EMPTY is used very often, i.e. Reporting Service’s query designer uses it by default. So it’s quite likely that you get in touch with this issue.
It’s confirmed that this flaw will NOT be fixed in RTM. And there are others I’m not aware of, yet…
Thomas
It’s quite a while since my last blog and I’m not quite sure how the frequency of new entries will develop in the next time… Some things changed in the last weeks and all are connected to my new employer www.avanade.com. It’s really a great company, a joined venture of www.accenture.com and www.microsoft.com bringing together Accenture’s business expertise with the Microsoft technology we (hopefully) all love…
My job is still closely connected to Business Intelligence, perhaps even more than before. It’s also still very technology focused while my colleague Markus, who moved to Avanade with me, is concentrating on the business side. So we’re now a small little team focusing on BI here in Germany (worldwide we have quite a number of people experienced in Business Intelligence projects) and there are some indicators which make me feel confident that there will be a significant growth in the future. SQL Server 2005 is knocking on our doors and I would be very surprised if not quite a number of companies will let it in.
Currently Avanade is working on an ETL framework bringing together the best practices of many BI projects around the world with the very new things coming up with SQL Server 2005. We can use the experience from SSIS implementations we just finished or which are getting ready for the customer soon.
Together with some other toolsets Avanade offers a very solid foundation for delivering Microsoft based IT projects. So if you’re looking for a partner to develop an enterprise BI solution I have a very good suggestion for you ;-))
And I’ll still try to keep you up-to-date with interesting things I find out. You’re also welcome to give me feedback and if you have any suggestions what you would like to read about at this place, please let me know!
Thomas
OK, I really don’t know why, but my statements about currency conversion (http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16196.aspx) weren't 100% correct… Or to be honest, some were 100% scrap…
Mosha told me that calculated measures based on measures affected by currency conversion scripts should work perfectly, I didn’t believe that. I have screenshots for that in case you think that I have to be wrong… However after a new deployment of the Analysis Services database all the measures work perfectly now… So Mosha, you (certainly) we right and there was just something screwed up in my cube…
Another thing I was complaining about is the performance of the currency conversion scripts. I did quite a lot of tests in the last weeks and I’m not finished, yet. We saw these performance problems mainly when we use the cube in Reporting Services (SSRS). So I tested the cube in the Server Management Studio (SSMS) and did the same queries we do with SSRS and they performed very well. So where’s the difference between SSRS and SSMS? When you filter a cube in SSMS you use subcubes by default. When you do the same in SSRS you use “standard” filters. So I changed my queries in SSMS to use filters and – poor performance. I investigated a little bit to find out if you can change SSRS`s behaviour to use subcubes instead of filters but you can’t…
Another approach would be to test exchanging the currency conversion scripts to simple measure expressions… Mosha told me that there might be quite some performance improvement… I didn’t have time to test it (OK, I tried by my Analysis Server started throwing memory errors when I changed my measures) so I can’t confirm that…
Watch out for further updates, I hope that there will be further improvements in upcoming releases (damned, when will the next CTP arrive?!?!)…
Thomas
Well, this is a good question... A common understanding is that the source of an UDM can be a cube or a relational database. So how do you define a relational source for the UDM?
The idea of the UDM is that it's the only source for any reporting demand in an enterprise. So you collect all your relational schemas and use UDM to set up a dimensional model on top of it. It also controls access to the data because it carries all the security information you had to define at different places before. This model is used by all reporting tools as a data source.
So the question is not "how to feed a UDM with relational data" because this is the most natural thing. Every UDM has one (or more) data source, and this is relational (in most cases). Just think of the UDM as a cache for your (relational) queries. And this cache is what we call a cube. This can be ROLAP (here you have your relational data source, if you want without any caching) or MOLAP (the "classical" cube). Using 100% ROLAP (without any aggregation), 100% MOLAP (with aggregations and scheduled updates) or anything in between, that's your decision. The aggregation strategy is defined per partition (that means a logical part of a cube). You can mix ROLAP and MOLAP in one cube, process a partition regularly (based on a schedule) and use another partition with proactive caching which processes the partition based on notifications (when the underlying data changes). So real-time OLAP is becoming true with the speed of MOLAP aggregations. And you don't have to be careful what attributes you include in a cube... With SQL Server 2000 you had to think carefully about every dimension, level or member property you want to define... Might it be too much for the cube (regarding memory) or is it getting too complex (for the end user)?! You can forget about that in SQL Server 2005... Add all the attributes from your relational schema to the UDM! Because it's the single source of information for your enterprise your user will expect that!
The concept is really compelling. A single source of information, only one place to define security, one model where you provide an easy to understand access to your data for end users, which you can feed from different sources, a common place to define business logic (by storing complex calculations in the UDM), central definition of currency conversions and multi language support, that's nearly too good to be true. This is what Microsoft has to prove that it will work.
Back to the question... I hope I made things a little bit clearer. The only thing you can't expect that you can run SQL queries against the UDM. At least that's not the way you should do it. MDX is the language of the UDM. There might be a way to get it working with SQL, too (like there was a "workaround" for that in SQL Server 2000), but I didn't try that, yet.
If you have any comments on this please let me know...
Thomas
Yesterday I got the confirmation that performance on currency conversion is not addressed in SSAS 2005... So there will be no change soon and this makes currency conversion an Enterprise Feature with limited usability... Or better: the next Enterprise Feature (see http://sqljunkies.com/WebLog/tpagel/archive/2005/07/24/16197.aspx)
I don't want to be to negative, I still love SQL Server 2005, but there are some tasks to be finished before releasing SP1 ;-)
Thomas
Microsoft tried to include best practices and other common tasks you had to implement on your own in Analysis Services 2000 in the new 2005 release by wizards. What do I have to say – THANKS for that! However there are some problems arising…
One is the currency conversion… Microsoft added a nice wizard for that. It handles the currency table and all the exchange rates. The result is a calculation added automatically in the cube. Here’s an example:
// <Currency conversion>
// Currency conversion wizard generated script.
// Currency conversion generated on: Freitag, 24. Juni 2005 11:44:51
// by user: cthiell
// Currency conversion type: OneToMany
// Selected members to be converted: Kosten des Einkaufs
// Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again.
// All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
Scope ( { Measures.[Price]} );
Scope( Leaves([Date]) ,
Except([Reporting Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[EUR]));
// This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate FAKT KURS - FAKT KURS KURS
// LinkMember is used to reference the currency from the source currency dimension in the rate cube.
Scope( { Measures.[Price]} );
This = (Measures.[Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) * [Reporting Currency].[Currency].[EUR];
End Scope;
End Scope;
So what does this script do? It simply exchanges the calculation of the measure “Price” for all currencies when the currency selected is not the currency the measure is stored in. So if you use “USD” instead of “EUR” each day’s value is multiplied by each day’s conversion rate (which is 100% correct) and then aggregated.
OK, so what’s the problem? Well, I see two problems:
-
If you have any calculated measures based on measures working with currency conversions, the calculated measures don’t work anymore. This might be a problem of my limited MDX knowledge but at the moment this is a fact for me…
-
If you have a complex cube you will notice significant performance degradation. Why? Well, that’s easy… Instead of retrieving the year’s sales (that’s already pre-aggregated) each day sales is queried, multiplied by the conversion rate and then aggregated... This is really a significant overhead…
I didn’t try to use a different approach… This would be that I change the Cube’s underlying Data Source view to do a cross join of the fact table and the currency conversion table. So I would have a fact record per currency with the correct conversion rate. This would end up in a much bigger cube but performance should be better (since everything can be aggregated) and calculated measures should be no problem anymore.
Any comments are very much appreciated…
Thomas