Drillthrough is incomplete

I have a strange problem in my cube when drilling down to details.

If I run the following query and leave out the DRILLTHROUGH, I get only one number as result, 7.

The Measure Counter is defined as "COUNT on lines" of the source table. So I expect that the number 7 results from 7 lines in the source table - And when I run an SQL query translated to use the same filter options as WHERE part, I do indeed get 7 lines matching the date and other settings I asked for. Everything ok so far.

When I execute the MDX query with DRILLTHROUGH I get only 3 lines as result - I remove the DRILLTHROUGH again and get 7 as result again... what can be the reason for this?

DRILLTHROUGH SELECT {[Measures].[Counter]} ON 0 FROM [Cube] WHERE ([Final Level].[Level Hierarchy].&[-6], [Action].[Action].&[4], [Submitted Date].[Submitted Date].[Submitted Year].&[2007], [LMU-SL].[LMU-SL Hierarchy].&[3])

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.|||

Mosha Pasumansky wrote:

Perhaps there are duplicate records at the measure group granularity, therefore in the cube you ended up with only 3 distinct records, but the total value is still correct - 7.

Mosha, thanks for answering.

I understand what you mean, but I can confirm that this is not the reason / solution.

I did confirm that in fact the 7 rows are distinct for the granularity of the measure group. They each have their own primary key in the database, and the measure group is based on this table, on this pk.

I browse the cube in Excel, and there are more combinations like this one where a number of lines is counted in cube ("11" in this other case), 11 distinct lines can be confirmed using sql, but no line at all is returned for drill. If I create my MDX without drillthrough, I get "11" as value for the measure. If I put "drillthrough" before it - I get no results at all.

As always, it is all far more complicated, as you guessed... My original MDX showed 4 dimensions sliced. 3 of them are really dimension on the measure group, these dimensions are joined in as regular to the measure group. The 4th one is a dimension on a measure group with a finer grain, a deeper level of detail as I try to imagine it. This 4th dimension is joined to the finer grain as a regular dimension, and to the measure group queried using m-n joining "back up from finer detail level".

For information, I can display a row count on the detail measure group as well, and these numbers in the cube are also perfectly in line with tests done using sql. But drills to the detail group give no results at all (even if the cube showed some 10 (distinct) detail lines for this dimension, verified using sql.

I suspect the problem is around that m-n from a finer level, since drilling into both measure groups without using this dimensions mix from different grains works perfectly fine. I am quite sure my dimension - measure setup is correct, since all counts in the cube come out the same value as manual sql test comparisons.

I am really a bit worried here with the reliability of SSAS 2005.

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data. I might also use CTP of SP2 to see what happens.

|||

Ralf_from_Europe wrote:

I am now cleaning out the db to only host the 7 lines in question as all data available, opposed to 2.5 mill on the queried level and 10 mill lines in the finer grain section to see if that makes any change in the result, and reload the whole SSAS db with this greatly simplified amount of data.

I think I found out the worst I could imagine: With less data loaded, or better: only data that falls within the area I slice for, I get ALL my 7 lines as result in drill and as cube measure. I did not change any settings in the project or made any changes to the query. I only threw out around 12 mill lines of data from the source db... I really doubt the precision, or maybe better the reliability of SSAS now.

In case anyone wants to know I noticed the difference between the lines given back and not given back: I suspect the problem I have is somewhere along the aggregations build for the cube. Why? The lines that work are in one category of a dimension (a pretty simple one with the order state, 8 items, like in "ordered", "mailed", "received" or "returned" and so). Anyway, the lines that work are in one state, the ones that do not are in another state.

This dimension is NOT used in this example, and also not part of my MDX query, neither with or without the drillthrough part. I suspect there is no aggregation on this dimension since the design wizard did not call an 8 entry dimension worth it. Then again, I did not change the aggregation setup, I only cleaned the source db of other data...

I am SERIOUSLY in doubt now! Trying SP2 CTP now...

Drilldown on Related measures: Calculations in DSV

Hi,

I have a strange requirement, although there is a workaround that I have thought about , it quite does not satisfy what I need. Here is the scenario:

I have about 200 calculations (basic arthimetic with maximum of 10 members). I have created them in the DSV as named calculations. The calculations involve other members of the DSV. We tried using calculated members but the performance became slow, because the user always has to see only the calculated values first.

The requirement is that, the users first see the calculated values, over different dimensions and dates. When the user wants to verify as to how a paticular calculated value was arrived, then he should be able to sort of drill down into the members that made up that calculated member. The users want to see this inline, like clicking on '+' expands the lower levels.

example: if a named calculation 'c' has a formula a+b then when user clicks on the 'c' cell or right clicks (whatever) it should show that c is made up of a+b and also the values of a and b for the corresponding dimensions being viewed.

my approach: I used drillthrough actions to achieve this, but the problem with drillthorugh actions is that it displays all actions for all cells, The user has to select the specific action for that cell. and in Proclarity (standard) the actions are displayed in the left navigation tab. not in the grid. (actually i have a problem with proclarity web standard and drillthrough actions, its in a other post)

1. I have read that we can have a general drillthrough action which targets similar common query subspace, but will we get the specific rows that make up the final value for all drillthorugh's. please do explain how to accomplish this. and how to identify a common query subspace.

2. Is there any other way to relate measures. can we create a dummy dimension that realtes the measures, so that they can be browsed using drill-down and drill-up kind of feature.

3. I have a drillthrough created which works with a set of dimension attributes, if I browse this drillthrough action with a new attribute added to the set, the drillthrough action does not return any rows. why is this?. Since I am only adding a attribute of the same dimension whose attributes are already there in the drillthrough return, should it not return the same rows as before? should I add this attribute in the return statement? but what if I dont want to see it in the return? but it is there while browsing?

Regards and thanks

What about creating a separate dimension to represent the hierarchy of named calculations - custom member formulii can be added, to override default aggregation behavior as necessary?

http://msdn2.microsoft.com/en-us/library/ms174474.aspx

>>

SQL Server 2005 Books Online

Working with Custom Member Formulas

You can define a Multidimensional Expressions (MDX) expression, called a custom member formula, to supply the values for the members of a specified attribute. A column in a table from a data source view provides, for each member in an attribute, the expression used to supply the value for that member.

Custom member formulas determine the cell values that are associated with members and override the aggregate functions of measures. Custom member formulas are written in MDX. Each custom member formula applies to a single member. Custom member formulas are stored in the dimension table or in another table that has a foreign key relationship with the dimension table.

...

>>

|||

Hi,

I have not tried this nor hear about it before.

Is it calculated measures?

where in BI studio can I create this.

I have to try this.

Thanks and Regards

|||

Hi,

I gave a bried reading about custom member formulas.

This is for attributes of a dimension, where you can create a custom attribute with an mdx expression pointing to a dsv table, right?

but I need this for fact members, I mean for measures.

my fact will be look like this:

fact:

measure 1

measure 2

calcMeasure3 = measure1 + measure 2

calcMeasure4 = measure2 / 5.0

when users browse, they have to see only calcMeasure3 and calcMeasure4. if they want to see more details, they would want to click/right click on calcMeasure3 to see measure1 and measure2 (because calcMeasure3 is measure1 + measure2).

How can I use custom memer formulas for this scenario? can you please explain for the above example?

I am kind of confused now.

thanks and regards

|||

My idea is to add a parent-child dimension like DimCustomMeasures. The dim table would have fields: MeasureID, ParentID, MDXFormula. For the example above, the rows could be like (without using named calculations in fact table):

"All Measures", (Null), "[Measures].CurrentMember"

"calcMeasure3", "All Measures", "[Measures].[measure1] + [Measures].[measure2]"

"calcMeasure4", "All Measures", "[Measures].[measure2] / 5.0"

"calcMeasure3.measure1", "calcMeasure3", "[Measures].[measure1]"

"calcMeasure3.measure2", "calcMeasure3", "[Measures].[measure2]"

calcMeasure4.measure2", "calcMeasure4", "[Measures].[measure2]"

|||

Hi,

Thanks,

I will try this out.

just trying to understand a little more........

will this ( "calcMeasure3", "All Measures", "[Measures].[measure1] + [Measures].[measure2]") show both the resultant measures?

I mean it should show values for both measure1 and measure 2. (not the added value for them, since this is already avaliable in the calcMeasure3)

Its like the user wants to know what values made up to the calcMeasure3.

So if the user browses this on a higher level of granularity, then the resultant should be many rows of measure1 , measure2 values.

I hope I am able to put out the requirement correctly?

Regards