Is This Possible? Defaulting of a Dimension Atrribute

I have a dimension with one visible attribute, let's call it "Status." This attribute is binded to one column from a forecast fact table that stores monthly snapshots. This column simply has a 'Yes' or 'No' value for every record in the fact table.

For a specific customer and product combination in this fact table, this "Status" column could have started off having a value of 'No' a few months ago, and now has 'Yes' for the current month. In other words, it can have both possible values.

For example, let's assume the following forecast data for the Customer A and Product B combination:

Month | Forecast $ | Status
-
November 2006 | $5 | 'No'
December 2006 | $10 | 'No'
January 2007 | $10 | 'Yes'

This "Status" attribute is meant to be used at the Page Field level in Excel, and users would like to be able to see customer forecast data based on the "Status" being filtered for either 'Yes' or 'No'.

Now, all of our measures in the cube have the following MDX ((TAIL(EXISTING [Date].[Month].MEMBERS).ITEM(0).ITEM(0), [Measure]). This defaults the measures to the current month in a report unless a time frame, whether year(s) and/or month(s), is explicityly specified or filtered at the Page Field level.

Assuming a timeframe is not explicitly specified at the Page Field level and a user is looking at the forecast data for Customer A for Product B, this customer will show up regardless of what the "Status" Page Field is filtered for. I believe this is the case because it historically has had a record associated with both 'No' and 'Yes'. What does differ hough, is whether or not Forecast $ will populate or not.

For example, Status = 'Yes', then Forecast $ will show $10. If Status = 'No', then Forecast $ will now be NULL. Ideally, if Status = 'No', this customer would not even show up for the current month.

So is this possible? Hopefully what I'm asking makes sense.

Thanks!From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.|||

Mosha Pasumansky wrote:

From your description it looks to me that it already should behave the way you described, assuming the Status attribute is marked as IsAggregatable=false. I must note, though, that the MDX you use for your measures is not the optimal solution. It is much better to simply define all your measures as having LastChild semiadditive aggregation to get the same effect.

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like. Would it be easier if you took a look at my solution file to see what I may have missed? I've tried this numerous times with no luck.

Once I get this behavior resolved, I'll follow up with you with the MDX.

Thanks!|||

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

|||

Mosha Pasumansky wrote:

I did not have the IsAggregatble property set to false. However, after setting it to true, it's still not behaving the way I would like.

You actually need to set it to false, not to true. Did you follow my suggestion about LastChild semiadditive aggregation type ?

Sorry - but I won't have time to go over your solution file - perhaps somebody else in this forum will be able to do it.

My fault. My mind must have been elsewhere when I posted. I did as you suggested with no luck. I set it to false from the default of true.

I did not follow your suggestion on the LastChild semiadditve aggregation type yet, as I wanted to focus on the above since I'm not too familiar with this LastChild thing. Now that I think about it, I assume this wouldn't be available to me in Standard edition? We're running Standard Edition.|||In fact, LastChild is the only semiadditive aggregation type available in Standard Edition - so you got lucky :)|||Anyone have any other thoughts? Is what I'm looking for even possible?

The IsAggregatable property when set to false, simply removed the 'All' member and didn't do what I am seeking.

Is this possible with a mdx-query

Hi all,

I'm stuck on the next issue:
I have the Measure "Charges" and a Dimension "ComDevice".
I have a pie-chart that has to show me the "Charges" per "Comdevice"(with labels that show me the name of the comdevice)

And now it comes, I don't want to see all the comdevices. I only want to see the comdevices that have the highest charges.
When I reached 80% of the charges, I want so show the remainded charges summed as 1 comdevice named "Remainder"
I already did this with an sql-stored procedure, but we are getting the report-data from a cube now and I'm very new to mdx

Does anyone know if this is possible with MDX and how to do this?

Thanx in advanceyes, you can.
use "TopPercent" function|||sorry for previous answer. (not read carefully)

you can use filter function to generate comdevice set that reached 80% charge, and create a calculate member to get reminder.|||Thanx for the reply and your tip,

I came up with some help at the next query.
-----------------------
With
Set [SimCardPieChart] as
TopPercent({[Buyer].[Company].members} ,80, [Measures].[InvoiceAmount])
Member [Buyer].[Company].[Remainder] as
Aggregate([Buyer].[Company].members - [SimCardPieChart])
select nonempty({[Measures].[InvoiceAmount]})on Columns,
{[SimCardPieChart],[Buyer].[Company].[Remainder]} on Rows
from ["Cube_Name"]
where ( [Seller].[Company].["Buyer"], [Invoice].[Period Code].["PeriodeCode"])

-----------------------

But now I'm getting the next values:
- 80% of the biggest buyers
- the the remaining summed as "Remainder"
- The Total of all the "Measure"

My question now is how to edit the query so that it would give only the toppercent(80%) and The Remainder. Without the total.

Thanx In advance.|||Hi all,

You all can Ignore the previous message. Reporting services ignores the grand total.
So I don't get it in my report.

regards.sql

Is this possible with a MDX-query

Hi all,

I'm stuck on the next issue:
I have the Measure "Charges" and a Dimension "ComDevice".
I have a pie-chart that has to show me the "Charges" per "Comdevice"(with labels that show me the name of the comdevice)

And now it comes, I don't want to see all the comdevices. I only want to see the comdevices that have the highest charges.
When I reached 80% of the charges, I want so show the remainded charges summed as 1 comdevice named "Remainder"
I already did this with an sql-stored procedure, but we are getting the report-data from a cube now and I'm very new to mdx

Does anyone know if this is possible with MDX and how to do this?

Thanx in advance

Here's an Adventure Works query which returns countries with at least 80% of sales, and the remainder:

>>

With
Set [Top80%Customers] as
TopPercent([Customer].[Customer Geography].[All Customers].Children,
80, [Measures].[Internet Sales Amount])
Member [Measures].[SalesPercent] as
[Measures].[Internet Sales Amount]/
([Measures].[Internet Sales Amount],
[Customer].[Customer Geography].[All Customers]),
FORMAT_STRING = "Percent"
Member [Customer].[Customer Geography].[All Customers].[Remainder] as
Aggregate([Customer].[Customer Geography].[All Customers].Children
- [Top80%Customers])
select {[Measures].[Internet Sales Amount],
[Measures].[SalesPercent]} on 0,
{[Top80%Customers],
[Customer].[Customer Geography].[All Customers].[Remainder],
[Customer].[Customer Geography].[All Customers]} on 1
from [Adventure Works]
-
Internet Sales Amount SalesPercent
United States $9,389,789.51 31.98%
Australia $9,061,000.58 30.86%
United Kingdom $3,391,712.21 11.55%
Germany $2,894,312.34 9.86%
Remainder $4,621,862.58 15.74%
All Customers $29,358,677.22 100.00%

>>

Is there Simple way to get all dimension & Hierarchy levels from a database?

I need to extract a list of all dimensions and their levels from all dimensions and hierarchies in a cube.

Im assuming that an XMLA query would do the trick...

The reason is that in converting from SAS 2000 to SAS2005, most of our virtual dimensions became member hierarchies in existing dimension, and now we have a large number of saved MDX queries that need to be updated to have the new dimension names. It will be a simple process if I can start from the known dimension and level names

For example the virtual dimension [Product Class] is now referenced as [Item].[Product Class], so now it and all levels of it's hierarchies need to be updated in all saved queries that used it. I have the search/replace but need to build a from/to list quickly

Any suggestions? sample scripts?

Thanks in advance,

Hi Clayton,

You can try this Discover XML/A query, which returns all levels in the AW cube:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">

<RequestType>MDSCHEMA_LEVELS</RequestType>

<Restrictions>

<RestrictionList>

<CATALOG_NAME>Adventure Works DW</CATALOG_NAME>

<CUBE_NAME>Adventure Works</CUBE_NAME>

</RestrictionList>

</Restrictions>

<Properties>

<PropertyList>

<Catalog>Adventure Works DW</Catalog>

<Format>Tabular</Format>

</PropertyList>

</Properties>

</Discover>