This forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.
HTH...
sqlThis forum is dedicate to SQL Server Notification Services; you'll probably have more luck getting responses in the SQL Server Tools forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=84&SiteID=1) or the microsoft.public.sqlserver.server newsgroup.
HTH...
This seems like a basic need that I don't understand.
I would like to populate cells with "aggregated" attributes.
For example, I have a Store with measures such as Sales and Expenses. I'd like to include in the report the Location and Size, both of which are dimensions. I want one record per store. The size of the store may change, so if I add Size to the axis, then I may get duplicate rows. Instead, I want the most recent store size to populate the cell.
I would like the query to return Store, Sales, Expenses, Location and Size; the latter two being attributes I don't want to slice my cube with.
What is the best way to accomplish this in MDX?
Thanks.
So, Store is a Type 2 dimension. Do location and size describe the store? If so, these are attributes within your Store dimension, right?
If these are all part of the same dimension, then you need some property from which you can determine which is the most recent version of a store. In your cube, how would you determine that two stores reference the same object and that one is a newer version of the other?
Bryan
|||If location and size are completely separate dimensions then you are going to have trouble. The only way that AS can figure out that a particular store has a particular size is to look for Sales or Expense records at the intersection of those two dimensions. I would think that Size and Location should be modelled as attributes of the store dimension (as type 2 changing attributes) and it is probably going to make things easier if you create a "Current Size" attribute.
|||Thanks for the replies.
Actually Stores is not a slowly changing dimension. I avoided a Snowflake Schema to keep things simple/fast.
Size and location are materialized dimensions.
I don't mind revising the Schema if necessary, but I want to make sure that if I make such a low-level change to the design that it's the right choice.
I want to get the size of the store based on the latest transaction date (it's the logic for the report....). With my limited knowledge of MDX, this is what I came up with:
DistinctCount(Descendants([Size],1))
This gives me the count of the number of sizes for the store along the time dimension in my query for the given period. The problem is that Count (as opposed to DistinctCount) returns the total number of unique sizes. Only distinct count returns the correct number of intersecting sizes. I can only get the count. I can't get the members. The following variations don't work:
Count(Distinct(Descendants([Size],1)))
Count(NonEmpty(Descendants([Size],1)))
Count(Descendants(NonEmpty([Size]),1))
Note that I'm using Store, Size, and Location as an analogy. I can't give too many actual details about my schema as a matter of confidentiality.
Any ideas on how to accomplish my goal?
Thanks.
|||It can be done with independant dimensions, but the perfoermance might not be that good. Basically, to find what you are after SSAS would have to look at all the possible combinations of Store, Size and Date and then find the last non-empty one.
Off the top of my head I think something like the following might work.
Generate([Store].[Store].[Store]
,TAIL(
NONEMPTY(
CROSSJOIN(
[Size].[Size].[Size]
,[Date].[Date].[Date]
)
,[Measures].[Sales]
)
,1
)
)
You don't necessarily need a snowflake to implement a slowly changing dimension. You should also note that snowflake and star don't really affect the performance for MOLAP based cubes. They do affect the processing time and in SSAS 2005, snowflake will usually be faster. It's hard to say if separate dimensions or attributes of one dimension will be faster without knowing more specifics, but you will often see performance improvments by reducing the number of distinct dimensions. In the Store analogy I would say that the sizes would not change all that often, so I would expect that implementing it as an SCD would result in a performance increase.
sqlThis seems like a basic need that I don't understand.
I would like to populate cells with "aggregated" attributes.
For example, I have a Store with measures such as Sales and Expenses. I'd like to include in the report the Location and Size, both of which are dimensions. I want one record per store. The size of the store may change, so if I add Size to the axis, then I may get duplicate rows. Instead, I want the most recent store size to populate the cell.
I would like the query to return Store, Sales, Expenses, Location and Size; the latter two being attributes I don't want to slice my cube with.
What is the best way to accomplish this in MDX?
Thanks.
So, Store is a Type 2 dimension. Do location and size describe the store? If so, these are attributes within your Store dimension, right?
If these are all part of the same dimension, then you need some property from which you can determine which is the most recent version of a store. In your cube, how would you determine that two stores reference the same object and that one is a newer version of the other?
Bryan
|||If location and size are completely separate dimensions then you are going to have trouble. The only way that AS can figure out that a particular store has a particular size is to look for Sales or Expense records at the intersection of those two dimensions. I would think that Size and Location should be modelled as attributes of the store dimension (as type 2 changing attributes) and it is probably going to make things easier if you create a "Current Size" attribute.
|||Thanks for the replies.
Actually Stores is not a slowly changing dimension. I avoided a Snowflake Schema to keep things simple/fast.
Size and location are materialized dimensions.
I don't mind revising the Schema if necessary, but I want to make sure that if I make such a low-level change to the design that it's the right choice.
I want to get the size of the store based on the latest transaction date (it's the logic for the report....). With my limited knowledge of MDX, this is what I came up with:
DistinctCount(Descendants([Size],1))
This gives me the count of the number of sizes for the store along the time dimension in my query for the given period. The problem is that Count (as opposed to DistinctCount) returns the total number of unique sizes. Only distinct count returns the correct number of intersecting sizes. I can only get the count. I can't get the members. The following variations don't work:
Count(Distinct(Descendants([Size],1)))
Count(NonEmpty(Descendants([Size],1)))
Count(Descendants(NonEmpty([Size]),1))
Note that I'm using Store, Size, and Location as an analogy. I can't give too many actual details about my schema as a matter of confidentiality.
Any ideas on how to accomplish my goal?
Thanks.
|||It can be done with independant dimensions, but the perfoermance might not be that good. Basically, to find what you are after SSAS would have to look at all the possible combinations of Store, Size and Date and then find the last non-empty one.
Off the top of my head I think something like the following might work.
Generate([Store].[Store].[Store]
,TAIL(
NONEMPTY(
CROSSJOIN(
[Size].[Size].[Size]
,[Date].[Date].[Date]
)
,[Measures].[Sales]
)
,1
)
)
You don't necessarily need a snowflake to implement a slowly changing dimension. You should also note that snowflake and star don't really affect the performance for MOLAP based cubes. They do affect the processing time and in SSAS 2005, snowflake will usually be faster. It's hard to say if separate dimensions or attributes of one dimension will be faster without knowing more specifics, but you will often see performance improvments by reducing the number of distinct dimensions. In the Store analogy I would say that the sizes would not change all that often, so I would expect that implementing it as an SCD would result in a performance increase.
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
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
