Calendars - Hierachy issue

I have date dimension that uses a date_skey as the primary key (format = 20070101). I have both fiscal and calendar hiearchies. My problem is that date based functions (ie YTD and ParallelPeriod) work with my regular calendar hierachy but not the fiscal hierachy. I have the following fields:

Year (type years)

Quarter (type quarters)

Month (type months)

Week (type weeks)

Date (type date)

FiscalYear (type FiscalYears)

FiscalQuarter (type FiscalQuarters)

FiscalMonth (type FiscalMonths)

FiscalWeek (type FiscalWeeks)

date_skey (type regular)

Calendar Hierachy:

Year (type years)

Quarter (type quarters)

Month (type months)

Date (type date)

and

Fiscal Hierachy:

FiscalYear (type fiscalyears)

FiscalQuarters(type fiscalquarters)

FiscalMonths (type fiscalMonths)

Date (type date)

Am I using date and date_skey incorrectly in this setup?

Any recommendations are welcome.

Based on results from the Adventure Works Date dimension, which has both Calendar and Fiscal heirarchies, you may need to use more explicit versions of MDX time series functions for the Fiscal hierarchy. For example, instead of YTD([Date].[Fiscal].CurrentMember), try PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember), etc.|||This was the right answer - YTD, MTD would not work on my Fiscal Calendar hierarchy but I was able to use PeriodsToDate in place of them. Thanks

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendars.
How do I handle that ?
If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact and
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calendars.
> How do I handle that ?

Calendar dimension with multiple Fiscal calendars

I am building a data warehouse using the dimensional model -- i.e. fact and
dimensional tables in a star schema.
For my Calendar dimension, I want to include Fiscal periods, but the data
warehouse has data for different clients that have different fiscal calendar
s.
How do I handle that ?If I understand well, you have:
Customer A - 15/10/2006 - CY 2006 FY 2007
Customer A - 30/09/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
Customer B - 15/10/2006 - CY 2006 FY 2006
You want to see FY 2006 data where FY is the one specific for each
customer.
If this is the case, I think the right model is to have one Fiscal
Calendar dimension (with FY Year, FY MonthNumber, FY QuarterNumber)
that is linked to the fact table.
Of course this model doesn't support you if you want to change the FY
of a Customer without changing the data already loaded into the fact
table.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
Craig HB wrote:
> I am building a data warehouse using the dimensional model -- i.e. fact an
d
> dimensional tables in a star schema.
> For my Calendar dimension, I want to include Fiscal periods, but the data
> warehouse has data for different clients that have different fiscal calend
ars.
> How do I handle that ?

Calculations Script Order and Formatting

I have a parent-child dimension for Accounts with a calculated member drapped off of the top level that figures Net Earnings % using a measure called Amount. As described by the name of the member, I need this expressed as a percentage. I am using this calc in the same view as a (Shell/Utility) Time Calculations dim that figures Year-to-date, Prior Year-to-date, YTD % Change, etc.... Depending on the order of the calculations in the calculations script I get different results. If I put the formula for the Net Earnings % before the MDX for the Time Calculations the results comes out correct but the formating gets overidden by the formating applied when the Time Calulations are applied. So the Net Earnings % for [YTD] and [YTD PY] are expressed as decimals and [YTD % Change] is expressed as a percentage. If I flip the order in the calculations script the formating gets corrected because the formatting of the Net Earnings % get applied last but my results for Net Earnings % are incorrect for [YTD % Change] because they were applied after the Time Calculations and end up applying the formula using the reults (the percentages) of the [YTD % Change] calc instead of the numbers used to make up that percentage.

Is there a way to put the Net Earnings % MDX before the Time Calculations MDX so that the results are correct for [YTD % Change] and reapply the formating of percent to [YTD] and [YTD PY] after the formatting of the Time Calculations get applied?

Nevermind, I figured it out. I placed the Net Earnings % MDX before the Time Calculations MDX and placed the following MDX after the Time Calculations MDX:

Code Snippet

Scope({[Account].[Account Hierarchy].[Net Earnings%]});

FORMAT_STRING(THIS) = "Percent";

End Scope;

|||

Nevermind, I figured it out. I placed the Net Earnings % MDX before the Time Calculations MDX and placed the following MDX after the Time Calculations MDX:

Code Snippet

Scope({[Account].[Account Hierarchy].[Net Earnings%]});

FORMAT_STRING(THIS) = "Percent";

End Scope;

Calculations on members and aggregation

I'm designing a Profit And Loss report dimension that has the following attributes: Report Line, Cost Center and Account. Attribute relationships are defined between the attributes so that Report Line->Cost Center->Account.

A Report line is either A) A combination ofcost centers and accounts like:

Report Line A

- Cost Center 1

--Account 3000

--Account 3001

-Cost Center B

.. And so on

Or B) A calculation

I'm trying to handle calculations through mdx scripts by overwriting the value for those report lines that are calculations like:
scope (Report Line B);

this = Report Line A - Report Line C;

end scope

The problem is that the calculation report lines mess up the aggregation of the cube. I guess what I am really asking is wether its possible to turn off the members that are calculations in the aggregation of the cube. I realize i could use calculated measures for this but this doesnt fit my need for two reasons: Ease of use of the dimension and inability to drill down in reporting services on drillable members when calculated members are included.

Anyone have any ideas on this?

You can use the Freeze MDX statement to prevent changes to "Report Line B" from effecting the totals. To do this add simply apply Freeze to the all member of the hierarchy that contains Report Line B prior to updating Report Line B.

|||

Thanks!

Works like a charm, and as a bonus it helped me understand freeze

Calculations

Hi,

I have a cube like that:

Account Dimension vs Product Dimension

Sell Income | Quantity | Unit Price

Computers 210 7 30

Computer A 20 2 10

Computer B 100 5 20

Sell Income = Quantity * Unit Price

The cube always aggregate first and after it calculate, because of that the total get wrong, Sell Income of Computers give me $210 and the correct value is $120. I tried put that formula in the dimension custom roll up, in dimension unary operation, in a calculated member but always give me the wrong value.

The only solution is create a script command in the cube calculation like this:

Scope(leaves(account), leaves(product))

[Account].[Sell Income] = [Account].[Quantity] * [Account].[Unit Price];

End Scope;

But that way I have performance problem. Any ideia?

Regards,

Handerson

You could try adding a "Sell Income" Named Calculation to the fact table in the Data Source View (DSV), like:

Quantity * Unit Price

Then create a "Sell Income" measure, with "sum" aggregation function, on this new fact field.

calculation problem

Hello,

We have time dimension (month, week)

and fact table, total_revenue, no_of_work_day, order, order type....

We added a calculated member : Ave revenue/work_day
Expression: "[Measures].[Tot Revenue]/[Measures].[No Of Work Day]"
in BI studio, but result is wrong.

Something wrong with 'no_of_work_day' , but how to correct it?

Thank you very much for any help.

In spite of using calculated member inside cube, try to create a named calculation inside datasourceview!?

In the table of datasourceview, right-click and add a named calculation a insert your formula TotRevenue/NoOfWorkDay...

Regards!

|||

Can you show us your mdx query?

You try using the SUM(...) function on the measures members and dividing with it...

I would also like to say that you can create your calculated member in the cube...

Regards,

|||Thank you very much PedroCGD and Luis for the advice,

I tried using named calculation, it works. Great!

What is different between named calculation and calculate command inside cube?

I really appreciated your help.
|||

Dear Olap_user,

Always try to use named calculations... only when you need to calculate inside aggregations you use calculated member. The big advantage of using named calculation is that the system spend time to do calculates only once, and with calculated member each time you query the cube. So:

Named Calculation > each time your process cube

Calculated member > each time you query the cube!

Helped you?

Regards!!!

|||Thank you very much for the detail explanation, using Named Calculation is better for the performance.

Your explanation is very helpful.

Thanks a lot.

Calculation bind to a hierarchy

Calculation bind to a hierarchy.
I have a report that shows values bind to a geograpich hierarchy dimension:
[Cube Dim Geographic].[Location].[Country
Description].&[Sverige].&[Norr].&[1. Insjön]
I want to set the only SalesChannelId from a parameter and not the whole
hierarchy. Is it possible to use a single geographic parameter,
SalesChannelId, from same dimension to set the selection?
[Cube Dim Geographic].[Sales Channel Id].&[1]
My hierarchy:
Sweden
North Region
Store number one (saleschannelid=1)
Store number two (saleschannelid=2)
Norway...
Thanks in advance!Hello Grundh,
Could you please let me know whether you want to use the parameter to
filter the dimension which the saleschannelid is the parameter value?
If so, you may use the Filter function in the MDX query.
http://msdn2.microsoft.com/en-us/library/ms146037.aspx
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Lu!
I have this strucure in Geograpic Dimension:
Geograpic Dimension:
+ Country Description
+ Country Id
+ Region Description
+ Region Id
+ Sales Channel Description
+ Sales Channel Id (I want to use this ID)
- Location Hierarchy
+ . Country Description
+ .. Region Description
+ ... Sales Channel Description
When i design the report i have to use the "Location hierarchy" to get the
right measures. But i want to use Sales Channel ID as a paramter to the
report.
"Wei Lu [MSFT]" wrote:
> Hello Grundh,
> Could you please let me know whether you want to use the parameter to
> filter the dimension which the saleschannelid is the parameter value?
> If so, you may use the Filter function in the MDX query.
> http://msdn2.microsoft.com/en-us/library/ms146037.aspx
> Hope this helps.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Grundth,
Does the Sales Channel ID an attribute of the Dimension?
If you put the attribute in the filter row, what did you get?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Wei!
Sales Channel ID is an attribute of the dimesion.
Could you please show me how to add a filter to the parameter dataset, and
how that filter could be an parameter to the report?
MDX For Geography dataset:
==================WITH MEMBER [Measures].[ParameterCaption]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]}
ON COLUMNS ,
[Cube Dim Geographic].[Location].Members
ON ROWS
FROM [CORES]
This is the MDX for the Sales Channel ID Attribute:
================================[Cube Dim Geographic].[Sales Channel Id]
Thanks!
Mats-Erik
"Wei Lu [MSFT]" wrote:
> Hello Grundth,
> Does the Sales Channel ID an attribute of the Dimension?
> If you put the attribute in the filter row, what did you get?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Grunth,
You could use the @.ParamName in the MDX to specify the Parameter and report
designer will recoginize the parameter and you will find it in the Report
Parameters.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Lu!
Can you show me how to add the required filter to my mdx-query that i sent
in my previous post?
Thanks in advance!
"Wei Lu [MSFT]" wrote:
> Hello Grunth,
> You could use the @.ParamName in the MDX to specify the Parameter and report
> designer will recoginize the parameter and you will find it in the Report
> Parameters.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Grunth,
You may use the Filter function in the WITH clause.
Please refer this article:
Filter (MDX)
http://msdn2.microsoft.com/en-us/library/ms146037.aspx
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
You could assist me applying the filter to my mdx-query:
WITH MEMBER [Measures].[ParameterCaption]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]}
ON COLUMNS ,
[Cube Dim Geographic].[Location].Members
ON ROWS
FROM [CORES]
This is the MDX for the Sales Channel ID Attribute:
================================[Cube Dim Geographic].[Sales Channel Id]
SalesChannel id is a numeric value.
Thanks!
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello,
How about using this:
WITH MEMBER [Measures].[ParameterCaption]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]}
ON COLUMNS ,
[Cube Dim Geographic].[Location].Members
ON ROWS
FROM [CORES]
WHERE [Cube Dim Geographic].[Sales Channel Id] .&[1]
If in the reporting services, please use
WITH MEMBER [Measures].[ParameterCaption]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel]
AS '[Cube Dim Geographic].[Location].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel]}
ON COLUMNS ,
[Cube Dim Geographic].[Location].Members
ON ROWS
FROM [CORES]
WHERE @.Parameter
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Lu,
The question returns this:
ParameterCaption ParameterrValue
ParameterLevel
All All [Cube Dim Geographic].[Location].[All] 0
Sweden Sweden [Cube Dim Geographic].[Location].[Count... 1
North North [Cube Dim Geographic].[Location].[Count... 2
SalesChannel1 SalesChannel1 [Cube Dim Geographic].[Location].[Count... 3
but i only want this:
ParameterCaption ParameterrValue
ParameterLevel
SalesChannel1 SalesChannel1 [Cube Dim Geographic].[Location].[Count... 3
Is this possible?
"Wei Lu [MSFT]" wrote:
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello,
I would like to perform some research. I appreciate your patience.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Calculating Median value from measures and dimensions

I am facing some problem in calculating Median

I am trying to calculate the median value using one of the measures and a dimension value.

Time is a measure in my cube and OpId is one of the dimensions.The result is as follows:

opid time median

1 55

2 23

3 23

Total 23

The Time here for Op Id 1 is the aggregation for all the rows whose OpId is 1.I want the median of the values whose OpId is 1 which is not showing at the moment.

What I am getting here is the median for all of the OpId but what I really want is the median for each of the individual Opid's as well.

I am using a calculated field Median with the following expression.

MEDIAN

( [Dim Operation].[Dim Operation].currentmember.children ,[Measures].[Elapsed Time])

Thanks

I think the reason you're getting a null for the first three rows is that the .Children function won't return any members if you're at the leaf level already. Instead, you'll want to use the Descendants function. Something like the following should do the trick:

Median(

Descendants([Dim Operation].[Dim Operation].CurrentMember, ,LEAVES)

,[Measures].[Elapsed Time]

)

Now if you're expecting to calculate a median value for opid 1 for more than simply the opid 1 value, then you'll need to add another dimension to the equation.

Calculating Length of a Member in MDX

hi,

can we calculate the length of any member in mdx query?i have a dimension having searched keywords like a,aa,abc,abcd... and so on.i want to filter the data based on length of these characters .Is it possible in mdx query?i am using MS sql server analysis services 2005.

Thanks

This is not too hard.

You would use something like Filter([Product].[ProductCode].members, vba!len([Product].[ProductCode].Currentmember.Name) = 3)

Another alternative that might give you better performance if you are going to do a lot of filtering on the length would be to calculate the length in a calculated column in the DSV and add it as an attribute to the dimension, in this way it can be indexed by SSAS rather than being calculated on the fly.

|||

Thanks for the Quick Reply Darren.

But in mdx i didnt find any function as you mentioned "vballen".i suppose this means variable length.can u suggest me how do i create such function in mdx?

Thanks

Calculated member with measure and dimension

Hi,

We have a little problem with our calculated member... in our table we have the total amount of a sale in 2 different currencies (US and CAN).

In another field we have the currency (1 being can, 2 being US)

Now I need to create a measure that will give me all the amount in CAN money and one in US money (depending on how users want to view the cube) but it's not working since my Exchange rate is not a measure and the type of currency isn't either, so the system isn't entering in my case.

I tried to do something like this :

case [Probill General].[Payeur Fund type]

when 1

then ([Measures].[Probill Total] * [Probill General].[Us Exch Rate])

when 2

then ([Measures].[Probill Total])

end

But it doesn't enter in the cases and it gives me an empty data field when I put it in the cube.

Any idea on how to bypass that problem?

Thanks,

Jason

Hello. One problem with your Case statement is that you do no write this in the TSQL way.

Instead try something like:

Case When [Probill General].[Payeur Fund type] = 1

Then [Measures].[Probill Total] * [Probill General].[Us Exch Rate])

Else [Measures].[Probill Total]

End

A good Blog post about currency conversion you can find here: http://blogs.conchango.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

HTH

Thomas Ivarsson

|||

Didn't work...

I went to that page and tried to create something based on that which didn't really solved my problem.

I think the real problem in my cube lies in the fact that my table has total that are both in canadian funds and us funds... all the solution on the web are to convert a full us fund (or other) to another currency but I can't seem to find anything where I can just transform or not an amount into a currency based on a dimension field...

I talked to my boss and she wants everything in Canadian funds, which means that IF my "[Probill General].[Payeur Fund type] = 1" then "[Measures].[Probill Total]" stays the same, but if it's "[Probill General].[Payeur Fund type] = 2" then it has to be "[Measures].[Probill Total] / [Probill General].[Us Exch Rate]" .

Any idea on how you can apply that in a cube?

Thanks

|||

With only two currencies the simple solution is to solve this problem in the ETL-process(not inte cube), when you load your data mart/data warehouse.

You will need a currency table with dates and currencies. If you have the currency code in the fact table when you load data, you join the date and the currency with your currency table.

If the requirement is that all values should be i CAD you only need to keep the exchange rate for CAD against your incoming currencies.

HTH

Thomas Ivarsson

Calculated member with measure and dimension

Hi,

We have a little problem with our calculated member... in our table we have the total amount of a sale in 2 different currencies (US and CAN).

In another field we have the currency (1 being can, 2 being US)

Now I need to create a measure that will give me all the amount in CAN money and one in US money (depending on how users want to view the cube) but it's not working since my Exchange rate is not a measure and the type of currency isn't either, so the system isn't entering in my case.

I tried to do something like this :

case [Probill General].[Payeur Fund type]

when 1

then ([Measures].[Probill Total] * [Probill General].[Us Exch Rate])

when 2

then ([Measures].[Probill Total])

end

But it doesn't enter in the cases and it gives me an empty data field when I put it in the cube.

Any idea on how to bypass that problem?

Thanks,

Jason

Hello. One problem with your Case statement is that you do no write this in the TSQL way.

Instead try something like:

Case When [Probill General].[Payeur Fund type] = 1

Then [Measures].[Probill Total] * [Probill General].[Us Exch Rate])

Else [Measures].[Probill Total]

End

A good Blog post about currency conversion you can find here: http://blogs.conchango.com/christianwade/archive/2006/08/24/Currency-Conversion-in-Analysis-Services-2005.aspx

HTH

Thomas Ivarsson

|||

Didn't work...

I went to that page and tried to create something based on that which didn't really solved my problem.

I think the real problem in my cube lies in the fact that my table has total that are both in canadian funds and us funds... all the solution on the web are to convert a full us fund (or other) to another currency but I can't seem to find anything where I can just transform or not an amount into a currency based on a dimension field...

I talked to my boss and she wants everything in Canadian funds, which means that IF my "[Probill General].[Payeur Fund type] = 1" then "[Measures].[Probill Total]" stays the same, but if it's "[Probill General].[Payeur Fund type] = 2" then it has to be "[Measures].[Probill Total] / [Probill General].[Us Exch Rate]" .

Any idea on how you can apply that in a cube?

Thanks

|||

With only two currencies the simple solution is to solve this problem in the ETL-process(not inte cube), when you load your data mart/data warehouse.

You will need a currency table with dates and currencies. If you have the currency code in the fact table when you load data, you join the date and the currency with your currency table.

If the requirement is that all values should be i CAD you only need to keep the exchange rate for CAD against your incoming currencies.

HTH

Thomas Ivarsson

Calculated Member Problem in RS

Hi,
There are two problems during I tested the Cube in RS:
1. If I choice a Member is the Top Level of the Dimension, as following, the
"All" this Member.
The All Member is the name of "All Caption" of the AS Advanced Property
and it's the sum of the Department Dimension.
When I use this All Member in RS, I can't see the Member name in Dataset
Fields, Why?
Sales Department Dimension
|--All
|--dept A
|--dept B
|--dept C
2. When the source data is Cube and use Matrix to present, is any way to
calculate dimension member on Column and Row?
It's mean that I want to use the member in Column and Row to calculate a
Calculated Member and show its value on "data" cell.
Or we should make the Calculated Member in AS first and select it on RS?
Have any idea about this and how to do?
Thanks for your time!
AngiLook for a whitepaper on Analysis Services and Reporting Services on the
Microsoft website. I know it is there. I read it and concluded that RS is
absolutely not ready for AS.
The disappearing "All" member is a known issue. The whitepaper shows ways
around the most common problems you'll encounter.
Hth,
Tom
"angi" wrote:
> Hi,
> There are two problems during I tested the Cube in RS:
> 1. If I choice a Member is the Top Level of the Dimension, as following, the
> "All" this Member.
> The All Member is the name of "All Caption" of the AS Advanced Property
> and it's the sum of the Department Dimension.
> When I use this All Member in RS, I can't see the Member name in Dataset
> Fields, Why?
> Sales Department Dimension
> |--All
> |--dept A
> |--dept B
> |--dept C
> 2. When the source data is Cube and use Matrix to present, is any way to
> calculate dimension member on Column and Row?
> It's mean that I want to use the member in Column and Row to calculate a
> Calculated Member and show its value on "data" cell.
> Or we should make the Calculated Member in AS first and select it on RS?
> Have any idea about this and how to do?
> Thanks for your time!
> Angi
>
>

Calculated Member Issue

Dear all,

My project running on AS2005 contains a dimension with 5 values in its table.

The fact table contains values for 4 of the values of the dimensions (the last one has not value yet).

When I browse the cube, if I display a calculated member (a sum) as measure and the dimension, I can see the measures for the 4 values of the dimension (the last value is not displayed).

But if I display a calculed member (done with "CALCULATIONS and which correspond to Measure.A/Measure.B), I see the measures for the 5 values of the dimension (the last value is displayed even if it has no value in the fact table). The fifth one is blank.

How can I avoid this ?

Thanks in advance for your support.

Juan

Hi Juan,

I'm assuming that you meant to say: "..if I display a sum measure and the dimension, I can see the measures for the 4 values of the dimension..".

So, if your issue occurs only with the calculated measure, not with measure group measures, you could try specifying Non Empty Behavior, like:

Create Member CurrentCube.[Measures].[RatioAB]

As [Measures].A /[Measures].B,

Non_Empty_Behavior = [Measures].B;