CurrentMember and a set

I have an excel sheet that people are using with pivot tables. The users want to be able to select a set of dates, and get a current year measure, along with the corresponding last year measures on a day to day comparison (not day of year, or date vs. date, but a "monday this year vs. monday last year", which is 364 days ago). They want to be able to pick and choose which days go into the set, for instance they want 1/1/cy, 2/1/cy, 2/5/cy and the sum of a measure vs. 1/2/ly, 2/2/ly, and 2/6/ly same measure summed up.

I can do this for a single selection (using ([Calendar].[Date].CurrentMember.Lag.(364), [Measure]), but I don't know how to access a list of items in the calcluated measure when they select a set.

If I could write this in MDX it would be much easier, but they would like to have control over it and do it through Excel (who can blame them?).

Does anyone have any suggestions?

thank you in advance,

John Hennesey

Assuming that you're using AS 2005, which version of Excel is involved (2003 issues different multi-select MDX queries than 2007)?|||

Sorry I left that detail out. I am using Excel 2003.

Thanks in advance,

John

|||

Hi John,

With Excel 2003, you should be able to use the "Existing" approach, discussed in Mosha's blog:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

...

But obviously we wanted AS2005 to work well with existing client tools too. Therefore, AS's query engine recognizes the shape of the queries where there is query calculated member doing Aggregate over constant single grain set, and this calculated member (or members if there are multiple multiselects in different hierarchies) is in the WHERE clause. And when AS detects this situation, it replaces the calculated member in the WHERE clause with the corresponding set.

...

>>

So, in your scenario, the calculated measure MDX expression could be like:

Aggregate(Generate(Existing [Calendar].[Date].[Date], {[Calendar].[Date].Lag(364)}), [Measure])

|||

Awesome - thank you very much for the response. I will give it a shot. One question - when it iterates through the currently selected set, how will it know which ones are selected? Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember? If this is a dumb question, please forgive me - I'm still fairly new to MDX.

Once again, thanks!

John

|||

"when it iterates through the currently selected set, how will it know which ones are selected?" - Mosha's blog entry above explains this better than I could.

"Should the first [Calendar].[Date].[Date] be [Calendar].[Date].CurrentMember" - no, this is the [Date] level of the [Calendar].[Date] hierarchy, and is shorthand for [Calendar].[Date].[Date].Members, which you can use instead. This all applies if you're using AS 2005, of course.

|||Cool - I will dig into Mosha's blog. Thank you for your quick response!

Currency or Double preferred?

I'm wondering whether it's better to choose Currency or Double for measure datatypes if the aggregated values will fit inside either datatype. Thoughts? Which would be smaller in terms of storage? If the number gets real big will either Currency or Double lose precision? Are rounding errors more prevalent with one rather than the other?

I have read the following pages but can't seem to make heads or tails of it in terms of best practices:

http://msdn2.microsoft.com/en-us/library/ms129408.aspx

http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbtype.aspx

http://msdn2.microsoft.com/en-us/library/678hzkk9(VS.80).aspx

http://msdn2.microsoft.com/en-us/library/364x0z75(VS.80).aspx

There is no Double in SQL Server so you have to use either Currency or Decimal or you may have to do conversion before sending the value to SQL Server. Try the link below for the SQL Server Types, ADO.NET types and .NET types. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms131092.aspx

|||

Caddre, thanks for the reply. That was a good link if you're doing SQLCLR stuff.

The Double SSAS datatype is equivalent to the float datatype in SQL Server. The Currency SSAS datatype is equivalent to the money datatype in SQL Server. Regardless, I'm not interested in SQL Server datatypes, just Analysis Services.

The question still remains, which datatype do you choose in SSAS if either will work: Currency or Double?

|||

Float is not a data type for persistence in any layer of SQL Server because of known precision issues in all programming languages. And my reply was related to your posted links which are clr related.

|||

Hi Furmangg,

If you have to store financial information in your cube and your database stores it as money(currency) you should definitely use currency data type for your mesures. Operations with the currency data type take less CPU power (no FPU calculations) and moreover will be better comressed by the storage engine.

Also if your information could be stored in currency data type (from -922,337,203,685,477.5808 to 922,337,203,685,477.5807) you schould take this type for your measures.

Best regards,

Vladimir Chtepa

|||

Thanks Vladimir. That's the answer I was looking for. I appreciate it.

Currency Format Issue

Please Help!

I am stuggeling with a amount measure in my cube. The format of the measure is set to currency but i always shows the dollar($) sign no matter the regional settings or the language setting of the report.

How can i fix this problem as i do not know where to start looking. Everyone keeps saying the currency will change according to the language setting of the report or language setting under properties of sql 2005 analysis services.

Thanks in advance,

Jacques kruger

I have struggled with the same problem. This solution work if you have the same currency in a column: (### ### ### ###.##) or (### ### ### ###.##EUR) with a currency. Note the blanks as a thousand separator and the dot as decimal).

HTH

Thomas Ivarsson

|||

I have found this link to more information in Mosha's Blog: http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx

Regards

Thomas Ivarsson

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency - not $

Hi

I have a cube with a sales amount measure. When I choose the format string to currency i get a $ infront of it when i browse the cube.

What should I do to get a ￡ infront or maybe SEK in the back (SEK = Swedish Kronor)

Thanks for answers

//Patrik

Try changing the format string to #,#.00 kr.

/Per

|||

Well, that worked.

But it seems to be a work around, and not the final solution. You must be able to select what type of currency the currency format code is.

/Patrik

|||

Yes, I agree. Should be possible - calculated members work fine.

Maybe Frederik knows - can ask him during the coffe break.

/Per

|||Change the language setting in the properties section for the cube in Business Intelligence Development Studio. Then you will get the correct regional display of the currencies.|||

Thanks man...that did it.

I think thats the only Language setting that I didn't change :P

//Patrik

Cubes not available after processing

The problem is that the cube is not available for queries. But process structure has been made, a full process of the dimensions and of the measure groups.

This is what I did do:
I did Import the SSAS Project from my live server to my local machine (laptop). I did some changings in the project in the dimensions and measure groups and deployed the solution to the server. After this I did a process structure. I logged into Analysis Server, opened a mdx script and the cube was available. Then I did a FULL Process of all the dimensions to be sure that everything is allright with them. Then I did reprocess all my Measure Groups.

Now the cube is not available. This happend the second time now. I process the cube by using ascmd.exe executing xmla commands.
After I do a process structure again it works. I can see the cube and the measure data is available after processing the measure groups.

I cannot explain this behaviour. Can you?

Best regards,
Stefoon

What happens is that when you do a full process of a dimension. The structures of any related cubes become "unprocessed". Processing a measure group under the cube does not implicitly re-process the structure of the cube.

After you do a full process of a dimension you will need to reprocess the structure again.

|||

Hi,

I think you are right. I did forget about the fact that the full process destroys the maps auf the dimensions for the cube construct.

Best regards,

Stefoon

Cube Process Error : The version of the linked Measure group xxxxx has changed.

I have migrated all my AS 2000 to 2005. But 2 cubes A and B have a processing

issue.

Cube B shares data from Cube A.

When I process Cube A I get this message that the version of the linked Measure Group in B has changed.

I have no problem processing Cube B which actually does share data with Cube A.

Any ideas

Thanks

Are both cubes in the same database? Try processing the whole database. Does that work?|||

Yes both cubes are in the same database.

As part of the 2005 migration, I processed all the cubes in the database successfully.

It is only when I am processing these 2 cubes as part of the posting process for these cubes that I experience the problem.

cube measure only displays one total

I did something to my cube where it is only displaying a much smaller value for

the sales amount measure than it should.

I have a project with two dimensions and one measure.

After deploying the cube i browsed it and pulled the sales amount into the

details field area. normally i should end up with some 400 + million in dollars instead i get 90+ thousand. It seems as if the cube is not processing fully or the browser tab is somehow being filtered. I checked and there is nothing in the subcube or any other field.

Can someone direct me on how best to debug this?

thanks

I found the problem and thought I would share what I did.

I'm surprised it works this way maybe it is a bug. Perhaps the pros here could add some info to this.

here is what I did.

I have a field called "Rate" which is multiplied by the "Qty" to come up the "Sales Amount" measure. This is done in the DSV as a "Named Calculation" in the measures table.

This same table is also used as a dimension table. In here i considered the "Rate" field to be non-Aggregatable or (IsAggregatable = false). Since i would never add up the rates for a total.

Apparently SSAS does not like this and hence things got really messed up. Switching it back to (IsAggregatable = true) fixed everything.

Hopefully my hair will grow back soon.

Cube History

I am trying to build a cube to monitor the sales performance. My measure is the sale amount. My dimensions include 1) Time 2) Sale hierarchy (salesman, sales office, region), and 3) Product Line. The problem I have is that when the salesman move from one office to another, the sales occured before this movement should continue to be credited to the old office. Only sales happen after the movement should be credited to the new office.
How should I implement the cube to achieve this effectively? We have more that 40,000 sales, about 500 offices, in the company to monitor. So I don't think keeping a copy of the sales hierarchy whenever there is a change is feasible.

Any inputs will be highly appreciated!

Thanks.You will need to Consider creating another dim for salesman.

you apear to need to collect when an employee was at a particular site for a particular sale so you will have to add a location value to each ORDER and not rely on the salesman's location. it has to be tied to the sale itself so the location attribute should be derived for the order

if this is a seldom problem you may want to make your cube holap (part relational and part cube)instead of pure olap and then you can use the drill down feature for a particular sale or set of sales to find the distinction between sites.|||Thank you very much for the information. I can see how that will fix the problem. However, I am not sure how to make the drilldown work using holap as you have suggested. Could you please elaborate it a bit about your last paragraph? Thanks. Sorry, I am new in the DWH field...|||check out those topics in BOL for analysis services
i will try to reply later