measure using calculated member

I have fact and 2 dimensions. i have to create a mesure using calculated member.

fact is having

fkey

ckey

monthid

hits

responses

CDIM is having

Ckey

ADateID

MonthDim is having

Monthid

MonthName

now i want a measure(XYZ) to be calculated using calculated member as Count of Ckey for AdateID = '20000101' for current member of monthdim. finally when i browse thru a cube it should display some thing like below.

Monthid XYZ

200601 1000

200602 2000

Thanks in adv

If you have a measure that is simply a row count of rows in the fact table called "Measures.FactRowCount" you can create the following calculation that would count the number of rows with AdateID = '20000101' given that you had a dimension "CDIM" with "AdateID" as an attribute:

CREATE MEMBER CURRENTCUBE MEASURES.XYZ

AS

(CDIM.AdateID.[20000101],Measures.FactRowCount),

FORMAT_STRING="#,#';

HTH,

Steve

|||

I tried this but same value is repeating for all my monthdim members. i used below query

select [Measures].[XYZ] on columns,

[Month Dim].[Month Dim Hierarchy].members on rows

from [MyCube]

--

And also I changed Expressions as below but not worked.

([Month Dim].[Month Dim].currentmember,[Cdim].[adateid].[20000101], Measures.FactRowCount)

Could you please help me

Thanks in adv

|||

Open up the cube editor and look at the "Dimension Usage" tab. Are all of your dimensions related correctly to the measure group?

- Steve

|||

Steve, they are correctly related. and i want to tell you that most of my dimensions are not directly related to fact table. they are reference thru a fact(this table is a dim for main fact) table (say FD). The measure i am creating is the distinct count of FD dimension for the selected month.

hope above info helps you to understand my problem

|||

Lets take this offline. You can email me at stevepon@.microsoft.com

It would be helpful if you could send me a copy of your project files so I could better understand the relationships.

Steve

meassure groups with different amount of dimensions

Hi everybody,

I've got a Fact Data table with a value and 16 dimensions.

Now I want to create a second measure group Color with a value and 3 dimensions.

I've filled this table with values and id's for each dimension.

Bu when I make an mdx query with a measure from the FactData and a measure from the second measure group (Color), only the first measure has a value, the second (the measure from Color) is null.

Is these something I've forgotton to set in the cube?

thanks in advance

Filip

Hi,

If you run a simple query (below) do you get two columns of data in the results?

select {[Measures].[Measure Group 1],[Measures].[Measure Group Color]} on 0

from [Cube]

results:

Measure Group 1 Measure Group Color

123231 123213

If not I suspect something else is wrong, perhaps check that you have set up the joins between the dimensions and the facts correctly. If you do get values in both, perhaps it is worth while posting your query.

Hope it helps,
Matt

|||

Hi,

that query runs.

but I have another problem now, this measure group I've created for storing color information, only can't have a Sum or Count as aggregation.

I cannot see the 65280 or ... value for the color I want to use.

I didn't included a period dimension to this measure group.

Is that the cause?

Filip

|||

Hi,

Sorry for delay I was investigating. If the measure is not aggregatable then you will get Null in the measure unless you go down to the granularity in which the data is held at. This is an area not that familar with and finding it difficult to prove.

But if you don't include a dimension in measure and the measures are aggregatable it will not causes nulls to appear, you get funny results. e.g.

select {[Measures].[Measure Group 1],[Measures].[Measure group 2]} on 0,

[Dimension only on measure group 1] on 1

from [cube]

Results:

Measure Group 1 Measure Group 2

dim a 45 1234 1234 being the total in measure group 2

dim b 23 1234

dim c 78 1234

Sorry I could answer it more positively.

Matt

|||

thank you for your help!

it works at a certain level.

at the granularity level, it works fine,

but once it starts aggregating, the value 256 becomes a sum of x times 256.

I have to solve that problem...

thansk for you help

Filip

MDX with OR statement

Hello,

I have a MDX query with two dimensions (KBC Account and Badge).

What I want to accomplish is:

- KBC account must not be 834 or 322

or

- KBC account must not be 320 in combination with Badge BRA

So in SQL language this would be:

where kbc_account != 834 or kbc_account != 322 or !(kbc_account = 320 and badge = 'bra')

How can I achive this with MDX?

I have tried something like this (subtext from the query) (but no result):

(

SELECT ( - {

[Symbol].[Dim KBC Account].[KBC Account Number].[834],

[Symbol].[Dim KBC Account].[KBC Account Number].[320]

} ) ON 0 FROM

[CUBE]

)

WHERE

( IIF ( [Symbol].[Dim KBC Account].[KBC Account Number].[322], (-{[Badge].[Dim Badge].[Badge].[BRA]}), 0 ) )

Regards

Hessel Appers

Could you give some examples with data, because there seem to be some logical discrepancies in the SQL where clause above:

"where kbc_account != 834 or kbc_account != 322" will always be true, since it can't be both.

Assuming the above should be: "where kbc_account != 834 and kbc_account != 322" - if this is false, then kbc_account is either 834 or 322. In that case, "or !(kbc_account = 320 and badge = 'bra')" will always be true, since kbc_account can't be 320.

|||

I will try your MDX query.

Sorry for the incorrect SQL. This must be:

WHERE (kbc_account_number <> 834) AND (kbc_account_number <> 320) AND NOT (kbc_account_number = 322 AND badge = 'BRA')

Now the SQL is correct will your MDX interpretation still be the same?

|||

It should be the same, except for a typo which I've corrected below:

Code Snippet

where

Except(CrossJoin([Symbol].[Dim KBC Account].[KBC Account Number], [Badge].[Dim Badge].[Badge]),

{CrossJoin({[Symbol].[Dim KBC Account].[KBC Account Number].[834]}, [Badge].[Dim Badge].[Badge]),

CrossJoin({[Symbol].[Dim KBC Account].[KBC Account Number].[320]}, [Badge].[Dim Badge].[Badge]),

([Symbol].[Dim KBC Account].[KBC Account Number].[322], [Badge].[Dim Badge].[Badge].[BRA])})

|||

Thnx Deepak,

I changed the MDX with your code and it works fine.

Ticket closed!

MDX to consolidate dates with measures by columns

I have a cube in SQL Server Analysis Services 2005 that has a structure like:

Measures:
Number of Tickets ([Measures].[Number of Tickets])

Dimensions:
Submitted Date ([Submit Date].[Date].[Date])
Submitted By ([Submit By].[Name].[Name])
Assigned Date ([Assigned Date].[Date].[Date])
Assigned To ([Assign To].[Name].[Name])
Resolved Date ([Resolved Date].[Date].[Date])
Resolved By ([Resolved By].[Name].[Name])
....
I would like to have returned a result set that cosolidates the dates with the number of tickets for each (or similarly, consolidates the people with the number of tickets for each). I need a set of all dates back, with the number of tickets aliased appropriately, so that I end up with something similar to the following:

Date # Tickets Submitted # Tickets Assigned # Tickets Resolved
April 1, 2007 14 27 11

April 2, 2007 11 4 null

April 3, 2007 null null 5
... etc.

What is the MDX to do this? I know how I would do it in t-sql, but unfortunately I'm not very proficient in mdx yet. I have tried using LinkMember and can get a single set of dates, but still don't know how to get the different ticket counts for each.

You might be able to figure something out with LinkMember, but it could get messy.

Have you considered changing your structure and setting up a couple of common dimensions instead? This should make a lot of different analysis a lot easier.

Measures:

SubmittedCount, AssignedCount, ResovledCount

Dimensions:

Date

Person

Or even something like

Measures:

ActivityCount

Dimensions:

Date

Person

Activity (Submitted, Resolved, Assigned)

|||

Hi Patty,

as Darren said one possibility is to change the structure of your measuregroup. If you are not able to do so you could try the following (SLOWER!!!) MDX.

If you use the same Date Dimension for all your Dates (-> Same Keys) you can use Filter or VBA to calculate your measures. I tried this in Adventureworks and for me the VBA was faster.

Here The MDX I used in Adventure Works DW:

Code Snippet

WITH MEMBER Measures.[Orders Delivered]

--AS ([Measures].[Order Count], ROOT([Date]), FILTER([Delivery Date].[Date].[Date].MEMBERS, [Delivery Date].[Date].MEMBER_KEY = [Date].[Date].CURRENTMEMBER.MEMBER_KEY).Item(0))

AS ([Measures].[Order Count], ROOT([Date]), StrToMember(VBA!CSTR("[Delivery Date].[Date].&[" + [Date].[Date].CURRENTMEMBER.MEMBER_KEY +"]")))

SELECT

{[Measures].[Order Count],Measures.[Orders Delivered] } ON COLUMNS,

{[Date].[Date].[Date].MEMBERS} ON ROWS

FROM [Adventure Works]

As u see I use the Key Of the Current Date to generate the UNIQUE_NAME in the other date dimension with vba. You should be able to replace Date with Submitted Date and Delivery Date with Assigned Date.

Hope this helps

Markus

MDX to aggregate measure over specific dimensions

Hi,

I'm trying to write a calculated member in SSAS 2005 that will only aggregate across certain dimensions. For example, say I have five dimensions: D1 - 5. I only want the member to aggregate across three of these dimensions. So in the cube browser, when I drag these three dimensions in, I get the correct aggregated value. But when I then drag dimensions four and five in, I want this value to stay the same. (The measure is currently in a measure group that uses all five dimensions).

I was thinking that the solution would be to have an MDX expression of the form

([Measures].[Measure],
[D1].CurrentMember,
[D2].CurrentMember,
[D3].CurrentMember,
[D4].[(All)],
[D5].[(All)])

but I would prefer not to have to list all dimensions and their hierarchies, and have to remember to add to this list if I add a dimension in the future. In SSAS 2000 I had a lookup cube that only contained the dimensions I wanted to slice by.

My other solution was to create a named query on the fact table that this measure is currently in, create a measure group on that, and then set up the dimension usage so that only the dimensions I want to slice by are referenced. However, I'm thinking that there must be a better solution, probably using some MDX that I don't know about!

Thanks in advance.
James

Take a look at the MDX Root function. Here's an example of something that might work for you using Adventure Works:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
*
[Customer].[Country].[Country].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo)

You still need to list all the dimensions but at least you don't need to list the hierarchies. Watch out for this 'feature' of the function, though, which occurs when more than one member from a hierarchy is in scope:

with member measures.rootdemo as (
root([Date].[Calendar Year].currentmember),
root([Product].[Category].currentmember),
root([Customer]),
[Measures].[Internet Sales Amount]
)
select
[Date].[Calendar Year].[Calendar Year].members
*
[Date].[Calendar Semester of Year].[Calendar Semester of Year].members
on 0,
[Product].[Category].[Category].members
on 1
from [Adventure Works]
where(measures.rootdemo,{[Customer].[Country].&[Australia],[Customer].[Country].&[United Kingdom]})

HTH,

Chris

|||
Hi Chris, many thanks for your reply.

This indeed worked. Going back to my previous example, I created a calculated member as:

(Root([D4]),
Root([D5]),
[Measures].[Measure])

and the measure is only sliced by dimensions D1, D2 and D3.

Thanks for your help!

James
|||

Another approach to this problem is to put these measures into dedicated measure group which excludes dimensions D4 and D5 - then you will get the aggregates you need without calculated members. Of course, if you sometimes do need detailed information over them, then the approach with calculated member is the right one.

One more note - you don't have to use Root() function if all the attributes in your dimensions are aggregatable. You will get better performance if you simply use

([D4].[All], [D5].[All], [Measures].[Measure])

|||
Hi, yes I thought those were my two options.

I don't want to create another measure group as I'll have duplicate measures and the table with this measure in is large (it's a requirement in our system to keep processing time to a minimum). I was hoping that there would be a solution where I didn't have to list every dimension I wanted to remove from the slice (and remember to add to the list if I add dimensions in the future), but at least I have a solution!

Many thanks for your help.
James

MDX Syntax equivalent of SQL (NOT IN) Clause

I have a cube that I'm trying to separate into 2 distinct populations. After filtering on several combinations of dimensions, I get a set of unique people. I have the MDX syntax for this and it works well. However, I then want to turn around and get all of the other people not selected in the first group.

I can't merely take the "not" dimensions of the first group because I have both one-to-one and one-to-many dimensions and doing that will double count a lot of them. Oh yea, I've got a distinct count member hanging around that is needed.

In sql, its very rudimentry but using the "where x not in (select y from tablename .... )" will get the all the others not in the first group.

Any suggestions on getting the equivalent statement in MDX built?

Thanks! jmac

Hi,

I think you should be able to accomplish what you're trying to do using the 'Except' function. Google has lot's of examples of it..

C
|||I'll take a look. Not sure how to get both results into two sets for comparison.|||If you have some crossjoining to get to the set of members you are interested in, you might also need to use the Extract() function in combination with Except() to get just the members from one attribute.

MDX Syntax equivalent of SQL (NOT IN) Clause

I have a cube that I'm trying to separate into 2 distinct populations. After filtering on several combinations of dimensions, I get a set of unique people. I have the MDX syntax for this and it works well. However, I then want to turn around and get all of the other people not selected in the first group.

I can't merely take the "not" dimensions of the first group because I have both one-to-one and one-to-many dimensions and doing that will double count a lot of them. Oh yea, I've got a distinct count member hanging around that is needed.

In sql, its very rudimentry but using the "where x not in (select y from tablename .... )" will get the all the others not in the first group.

Any suggestions on getting the equivalent statement in MDX built?

Thanks! jmac

Hi,

I think you should be able to accomplish what you're trying to do using the 'Except' function. Google has lot's of examples of it..

C
|||I'll take a look. Not sure how to get both results into two sets for comparison.|||If you have some crossjoining to get to the set of members you are interested in, you might also need to use the Extract() function in combination with Except() to get just the members from one attribute.

MDX Sum distinct across specific dimensions

I have a measure group that has a few measures that are not addittive across all dimensions. I need to be able to get the sum distinct across specific dimensions and am having problems getting it to work. I tried specifying that the aggregate function should be semiaddittive, but it complains that it is missing a time dimension. I have tried many variations and combinations of MDX functions including sum, aggregate, crossjoin, filter, distinct, etc. but am struggling with the right combination.

Here is a simplified example of what I am trying to do:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

When I group on role I need to see this:

Licensing Manager 5

Production Manager 3

When I group on Title, I need to see this:

Director Operations 5

Basically I want to do a sum distinct for the GroupID. I only want to add the ConditionCount once for each distinct GroupID since the value will be the same for all instances of an individual groupID. Is there a way to do this in MDX?

Thanks in advance for your suggestions!

Clayton

Could you create another fact table and measure group for ConditionCount? For example, if the original fact table has the fields above, a named query could be created like:

select GroupID, Avg(ConditionCount) as ConditionCount from OriginalFact group by GroupID

Then, a "sum" [Measure].[ConditionCount] measure created on this measure group, which only relates to the Group dimension, would work as above.

|||I don't quite understand your suggestion. I do have a table where GroupID is the primary key, but I can't figure out how to get it to count values once for each distinct iteration of Group ID. The rollups and sum distincts across specific dimensions works with Oracle analytic functions, but I can't figure out how to get it to work with MDX. Thanks for your response.|||

Here's a more detailed description of the suggested model, which uses many-many dimensions:

Suppose this is the "TitleRole" fact table and measure group, with dimensions Group, Title and Role:

GroupID Title Role ConditionCount

11682 Director Operations Licensing Manager 2
11683 Director Operations Licensing Manager 3
11683 Director Operations Product Manager 3

There is a 2nd "Group" fact table and measure group (as suggested above), with [ConditionCount] "sum" measure - this could be the Group dimension table:

GroupID ConditionCount

11682 2
11683 3

This measure group relates directly to the "Group" dimension, but has a many-many relation with the Title and Role dimensions, via the intermediate "TitleRole" measure group. Now, if the Title: "Director Operations" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 5. And if the Role: "Product Manager" is selected, [Measures].[ConditionCount] in the "Group" measure group should be 3.

|||Ah. I understand now. Let me give that I try when I get back to it in a few days and I'll respond. I already have that table set up in the data warehouse, but didn't think about trying a many-to-many relationship. Thanks again!

MDX Script: How do I create a YTD-Balance Measure?

Hi,

I am using the Standard Edition of SQL Server 2005. I have a financial reporting cube with the measure Amount and several dimensions (Time, Account, etc). A simplified version of the data is:

AccountType

Month1

Month2

Month3

Month4

etc...

Asset

200

20

25

30

Liability

-100

-5

-10

-15

If I use the default cube then this is how data will show. What I would like to do is to add a YTD-Balance measure to the cube. This YTD-Balance measure would show the following data:

AccountType

Month1

Month2

Month3

Month4

Asset

200

220

245

275

Liability

-100

-105

-115

-130

So instead of showing the difference in a month (eg 20) like the underlying data, I would like to show a running total for the year (eg 200+20=220). My attempt so far is:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM({PeriodsToDate([Time].[(All)])}, [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Amount] },
VISIBLE = 1;

This doesn't do what I want however: it sums all months in a quarter, all quarters in a year, and all years in the cube. What I instead want is for each month to be a summary of the months in the year so far. Is anyone able to help me out at all with this?

Thanks, Matt

Assuming that there is a [Year] attribute in the [Time] dimension, whose type is "Years"; and that there is a [Time].[Calendar] hierarchy (as in the Adventure Works Date dimension), then you should be able to use the YTD() function:

Create Member CurrentCube.[Measures].[YTDBalance]
AS SUM(YTD([Time].[Calendar].CurrentMember), [Measures].[Amount]),
NON_EMPTY_BEHAVIOR = { [Measures].[Amount] },
VISIBLE = 1;

|||A word of caution: You should only set the NON_EMPTY_BEHAVIOR if you are certain that there actually is a value in [Measures].[Amount] for the cells in which you want to display YTDBalance. Example: If you wanted to show the YTDBalance for all months in 2006, but you only had data up until July 2006, the calculated member suggested by Deepak would only return values for YTDBalance up until July 2006.

MDX query for displaying all the dimension in a row

I have cube which have 6 dimensions.

I want to display all the dimsion members in a row . Can anyone help me with the MDX query for the same

e.g. suppose the dimension are dim1, dim2, .............

Now I want to retrieve data in the form of table as

Dim1.........Dim2...................Dim3............Dim4...............

Val1..........Val2....................Val3...............Val4................

Is there some way to inner join the data in MDX as in Sql. I mean just like we have inner join in SQL is there any MDX equivalent

e.g suppose we have three tables A,B,C and these three are related through primary foreign key relation ( A conatins the refernce for B and B in tuyrn contains the reference for C).

Now these three table are used to form a Cube and the column in these are tables form a dimension. Nwo can I join these three tables and show the data

MDX help required

Hi,

I have a requirement for which i need to write an MDX. The scenario is, i have a fact table with dimensions. The FactStudent consists of keys from dimensions like location, ranks dimension and period dimension. i want to know the students who have got same rank for an year and previous year (to check consistency in performance). how should be the MDX for getting this info.please help.

Thanks & regards,

Vivek S

Some reading for you

http://www.databasejournal.com/features/mssql/article.php/10894_2238011_1

Bit outdated, but you should be able to use the basics with AS2005 as well.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.