New to MDX. Help Needed with a Calculation Definition in my SSAS 2005 Cube

I'm new to MDX and I don't quite get the hang of it yet, so I'll try to provide all of the relevant information that I can so that you guys can help me.

My company has a lot of "Rolling 12 Month" metrics and I've been able to incorporate all but a few in my SSAS 2005 cube. By "Rolling 12", we refer to the 12-month period that ends with the previous month. So all "Rolling 12" metrics reviewed in December 2005 refer to the period of December 2004 through November 2005.

So for "Rolling 12 Month Invoices", I have the following calculation defined in my cube and it seems to be correct:

SUM( LASTPERIODS ( 12, [Invoice Date].[Invoice Month]), [Measures].[Invoice $])

Now I'm having trouble defining the # of customers in the Rolling 12 month period. I added a DistinctCount measure for # of customers and have been able to use it in the definition of 2 other metrics.

For example, for the # of customers for 2004, I have it defined as:

([Measures].[# of Customers] , [Invoice Date].[Invoice Year].&[2004])

And it appears to fine.

I tried similar variation of the following with no luck:

SUM(SUM( LASTPERIODS ( 12, [Invoice Date].[Invoice Month]), [Measures].[# of Customers]))

Your help is greatly appreciated!

EDIT: I'm using SQL Server 2005 Standard Edition
EDIT: Please let me know what other details you may need about my cube.A Distinct Count measure isn't strictly additive, so try Aggregate() rather than
Sum() to compute it over a set (only works with AS 2005):

>>
Aggregate(LASTPERIODS ( 12, [Invoice Date].[Invoice Month]),
[Measures].[# of Customers])
>>
http://msdn2.microsoft.com/en-us/library/ms145524(en-US,SQL.90).aspx
>>

Aggregate (MDX)

Returns a scalar value calculated by aggregating either measures or an optionally specified numeric expression over the tuples of a specified set.
...

Distinct Count

An aggregation across the fact data contributing to the subcube when the slicer axis includes a set.

Calculations on the set generate an error. Calculations below granularity of the set are ignored.

...
>>

|||Thank you very much!!!

New Time Dimension Role after cube wizard

If I have a data source view consisting of two tables - a fact table and a time dimension table. The fact table has three date columns - transaction begin date, transaction end date, and updated date. When I initially designed the data source view my requirements only called for the trans begin and end dates so I made two joins to the time table from the sales table. I then ran the cube wizard which correctly detected that I intended to use the time table twice for two different Time hierarchies (Roles) on a single time dimension.

I have recently been asked to add the updated date but I cannot figure out how to add a new hierarchy (Role) on the time dimension. Any Ideas?

You should be able to go to the Dimension Usage tab in the cube designer and click on the Add Cube Dimension button on the toolbar (the third button from the left). When the list of dimensions comes up, simply select your Time dimension and add it again. You'll then see the dimension added within the list of dimensions down the left side of the tab, likely with a name like Time (Time 1) or something similar. Now, simply highlight the newly added role-playing dimension, hit F2, and rename it whatever you want. Then, of course, set the appropriate relationship between this new dimension and the measure group(s) within the cube...

HTH,

Dave Fackler

sql

New partition using XMLA

Hi,

I have an existing cube containing a single partition.

I am running the following xmla script to create a new partition :

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">

<ParentObject>

<DatabaseID>myDB_id</DatabaseID>

<CubeID>mycube_id</CubeID>

<MeasureGroupID>mymg_id</MeasureGroupID>

</ParentObject>

<ObjectDefinition>

<Partition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<ID>mypartition_M1</ID>

<Name>mypartition_M1</Name>

<Annotations>

<Annotation>

<Name>AggregationPercent</Name>

<Value>30</Value>

</Annotation>

</Annotations>

<Source xsi:type="QueryBinding">

<DataSourceID>myDS_id</DataSourceID>

<QueryDefinition>

SELECT blabla...

</QueryDefinition>

</Source>

<StorageMode>Molap</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<ProactiveCaching>

<SilenceInterval>-PT1S</SilenceInterval>

<Latency>-PT1S</Latency>

<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>

<ForceRebuildInterval>-PT1S</ForceRebuildInterval>

<AggregationStorage>MolapOnly</AggregationStorage>

<Source xsi:type="ProactiveCachingInheritedBinding">

<NotificationTechnique>Server</NotificationTechnique>

</Source>

</ProactiveCaching>

<EstimatedRows>1000000</EstimatedRows>

<AggregationDesignID>AggregationDesign</AggregationDesignID>

</Partition>

</ObjectDefinition>

</Create>

My partition is created and I can see it into SQL Server Management Studio.

I can process it as well and everything looks fine.

The only problem is that I cannot see the partition in BIDS which is quite strange....

Any ideas ?

Regards,

JL

BIDS works against project. Unless you made changes to the project - it won't see the changes on the live server. You can create new project by using "Import" option - i.e. create project from current server state. Or you can also work in "online mode" by doing File -> Open -> Analysis Services Database.|||

Mosha,

Thx a lot !

JL

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark

New Measure Based on Attribute Values

I'd like to add a new measure to my cube which is based upon values of an attribute. For example I'd like the measures to always apply the filter [Geography].[Country].&[Australia] applied even if the Geography attribute isn't included in the SELECT.

I've tried:

CREATEMEMBERCURRENTCUBE.[measures].[Oz Internet]

AS

AGGREGATE

(

EXISTING

(

{

[Geography].[Country].&[Australia]

}

*

[Sales Channel].[Sales Channel].&[Internet]

)

),

but this doesn't return anything. Is there a way to do this?

Here is an example where I limit a specific measure by a dimension member:

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])

select

{[Measures].[Reseller Sales Amount],[Measures].[Reseller Sales Amount Bikes]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

Bryan|||Thanks for that. What if I needed it to work for more than one category eg Bikes and Accessories? I'm sure it's going to be simple but I can't get the syntax right.|||

One way to pull out the individual measure values and add them. That's seen in the first calculated member.

In the next calculated member, you can see I build a set of members, get the measure against this set and then aggregate. You can build that first set many different ways.

Good luck,
Bryan

Code Snippet

withmember [Measures].[Reseller Sales Amount Bikes+] as

([Measures].[Reseller Sales Amount],[Product].[Category].[Bikes])+

([Measures].[Reseller Sales Amount],[Product].[Category].[Accessories])

member [Measures].[Reseller Sales Amount Bikes+ Too] as

AGGREGATE({[Product].[Category].[Bikes],[Product].[Category].[Accessories]},[Measures].[Reseller Sales Amount])

select

{[Measures].[Reseller Sales Amount],

[Measures].[Reseller Sales Amount Bikes+],

[Measures].[Reseller Sales Amount Bikes+ Too]} on 0,

[Date].[Calendar].[Month].Memberson 1

from [Adventure Works]

|||

That's very helpful. Thanks Bryan.

Mark