olap and hierarchy problem

Hi.
This is an OLAP question.

I have a dimension which is a father-son dimension.
I built the cube in analysis server.
In the analysis browser I can see the dimension fine,

I hant my reporting services report to show that dimension.
The problem is that the report won't show me that dimension in hierarchy tree (even if I check drill down) but flatten it down.

Does some one know if report service can handle those kind of reports?

Thanks.I assume the dimension is a parent-child dimension. RS is not an OLAP browser. As you have noticed, it flattens the parent-child dimenion. In this presentation download, you will find two approaches for dealing with parent-child dimensions in RS. The first one uses the RS recursive support. The second bypasses the SSAS 2005 provider in order to use straight MDX to expand the hierarchy. Needless to say, both approaches have their limitations and we can expect the support of parent-child dimensions and UDM in general to improve in future releases.|||Hi Teo,
Thanks for your replay, Its hard to find help and support for Olap and for Olap and SSRS on the net.

Its very disappointing to hear that RS does not support OLAP (and for me, if it can't handle parent-child dimension it can't handle olap).

Looks like we will have to use some other programs for working with cubes and creating reports.

Thanks for your post and I will have a look at the link you supplied to check if by any chance there is a way to work with parent-child with out any work arounds.

Regards,
Roy.|||Just to clarify. I didn't say it doesn't support OLAP. I said that it flattens the parent-child hierachies when converting them to two-dimensional datasets.|||I know you didn't - I did ("and for me, if it can't handle

parent-child dimension it can't handle olap").

I think Its a must when working with olap.

Thank for the reply :)
|||

How would you go about using OLAP with SSRS?

Do you purchase OLAP?

I've seen it used, but I know little to nothing about it.

|||You can build OLAP solutions (cubes) with analysis services and then deploy the cube to the analysis server.

After that you can create a report and as a data source tell him to connect to the cube on the analysis server.

But as you can see, reporting services can really work with olap :(

OLAP - EXCEL problem - urgent

Hello,

I have a dimension hierarchy with 2 levels, like this:

A - X

- Y

- Z

B - U

- V

- T

And I have a cube with the dimension above and one measure (sum).

The fact table of this cube has 2 columns (level2,sumvalue) and i have the values:

Level2 SumValue

X 1

Y 2

T 6

When I access this cube from Excel and use multiple selection weird stuff happends.

So, I create a Pivot table, in which i select only X,Y,U:

Level1

Level2

Total

A

X

1

Y

2

A Total

3

Grand Total

3

If i give a refersh to this pivot table i get :

SumValue

Level1

Level2

Total

A

X

1

Y

2

A Total

3

B

6

Grand Total

9

Why i see the B in the pivot table, if i selected only X,Y,U and U has no value in the fact table? I need an urgent answer because end-users use a lot the refresh option and in this case they see wrong values.

I'm using Microsoft Analysis Server build 9.00.2153 (SP1) and Microsoft Office Excel 2003 (11.6560.6568) SP2. I made the test with Microsoft Analysis Server SP2 and same version of Excel and i got the same result.

Any help will be appreciated.

Thanks,

Vulcano

A couple of things to check, that I can think of:

Is "Visual Totals" still enabled after refresh (ie. "Totals include Hidden Items" is disabled) - otherwise, these results would be expected?|||Thanks Deepak for your answer.

The property of the pivot table "Include hidden members in totals" is disable all the time. So no hidden members are included. In my dimension i don't have hidden members.

I run a Profiler and I discovered something strange:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[T] , [Dimension].[Hierarchy].[Level1].[ V ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ T ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

Why the two queries are different? Why in the second query [Dimension].[Hierarchy].[Level1].[ B ] disappears?

Thanks,

Vulcano|||

The member which disappeared in the 2nd query is: [Dimension].[Hierarchy].[Level1].[ V ] , not [Dimension].[Hierarchy].[Level1].[ B ] (though maybe these are place-holder, not real, names?).

When you look at the drop-down list of members selected in the pivot table, do you find that this missing member is now selected? If so, this could be a side-effect of refreshing the pivot table (though I'm not sure why the refresh would change the selected members).

|||

My mistake Deepak, the queries are wrong, they are missing the A, B Members of the dimension. I post again the queries:

For the first pivot table the query shown in the Profiler is :

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] , [Dimension].[Hierarchy].[Level1].[ B ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

For the second pivot table when the refresh is done the query is:

SELECT NON EMPTY HIERARCHIZE(Except({AddCalculatedMembers(Except({AddCalculatedMembers({DrillDownMember({DrillDownLevel({[Dimension].[Hierarchy].[All]})}, {[Dimension].[Hierarchy].[Level1].[ A ] })})}, {[Dimension].[Hierarchy].[Level2].[V], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] }))}, {[Dimension].[Hierarchy].[Level2].[ V ], [Dimension].[Hierarchy].[Level2].[T], [Dimension].[Hierarchy].[Level2].[ Z ] })) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [TestWarTestSimple] WHERE ([Measures].[SumValue])

And you are right again: when the first pivot is created the X,Y,U members are selected, after the pivot is refresh the U member is not selected anymore ( so the member selected are X,Y,B). Is this the right behaviour, or maybe is a bug?

Thanks,Vulcano