Many to Many Hierarchy Defining
Carmaboy
dimensional filtering in EXCEL 2007? I can't for the life of me see
if that is possible. When you browse your cube in AS, you can
dimensional filter, but in Excel, you can only report filter.
The issue comes up when when using Many to Many schema.
Say I have the structure,
-Building A
---Class A
-----Desk A
-----Desk B
---Class B
-----Desk C
-Building B
---Class C
-----Desk D
-----Desk E
If I put the Hierarchy dimension Layout in my rows, I can see the
above when expanded. If I then filter my data by Building Name
(placing that in the Report Filter and select Building A), it now only
shows me
-Building A
---Class A
-----Desk A
-----Desk B
---Class B
-----Desk C
which is what I want to see. The issue is that the filter list for
the Layout in the rows, shows
-Building A
-Building B
The hope is that depending on the Layout selected, only the associated
Building hierarchy appears in the filter list.
I read the Many to Many Revolution document (http://www.sqlbi.com/
Default.aspx?tabid=80) which on page 82 under Figure 87 depicts my
exact problem. Unfortunately, I cannot figure out how to apply the
suggestion stated.
"In the case your OLAP browser does not support subcube filtering, or
it may be too complex for the end user to use this feature, you could
define a single top-level member for each hierarchy that corresponds
to the name of the hierarchy. I use this strategy to facilitate the
use of "legacy" clients like Excel 2003 with this model. "
specifically....."you could define a single top-level member for each
hierarchy that corresponds to the name of the hierarchy"
Can some please point me in the right direction to "define"ing this.
Unfortunately, Excel 2007 is our reporting solution so using other
tools is not an option. With hundereds of Layouts, having the user
determine which Building belongs to which Layout using the Row
Filtering is not an applicable solution.
Thanks in Advance.
Marco Russo
You should join all the combinations between Buildings and Layouts,
creating a hierarchy for each of these combination. Of course, at this
point this dimension will have duplicates at the leaf level, and this
resulting dimension (call it "Layouts") is the one that will be mapped
many-to-many with a "native" dimension (call it "Buildings") where you
have the natural hierarchy with Buildings, Classes and Desks.
The "Layouts" dimension will be visible, the "Buildings" dimension
will be hidden.
Take care of possible performance issues.
Hope it helps.
Marco
Carmaboy
Thank you for your reply. (I've got 2 of your books which are
excellent)
If I understand you correctly, what your suggesting, I have done. The
problem isnt the schema but it exists when when using Excel 2007. If
you select a specific Layout in the report filter, all they Buildings
show up in the rows or columns rather than the 1 or many Buildings
associated to that Layout.
ie.
Layout A (Report Filter)
- Building A
+Class A
+Class B
This appears to be what I want, but as soon as I select the filter
selection for the rows (Building), I see all the Building for all
Layouts.
+BuildingA (belongs to LayoutA)
+BuildingB (belongs to LayoutA)
+BuildingB (belongs to LayoutB)
This adds confusion for the user as they are trying to filter out
certain members of a specific layout. If they are report filtering
LayoutA and expect to only see BuilingA and the first instance of
BuildingB, it would be difficult. There will be thousands of layouts.
In AS, placing the Layout in the Dimension Filter correctly filters
out the Buildings associated with the Layout so when they row filter,
only the assoicated Buildings appear. By filering this in AS, a
subcube is generated only providing me with the correct optinos for
the building filtering. When done in Excel, its just filting out in a
where clauses which does not provide the necessary row filtering.
If I misunderstand your reply, I appologize, but could you please
elborate more or perhaps correct my though process here? I'd be more
than willing to send my schema to you if that would help understand my
sitution. Suffice to say, that page 82 of your ManytoMany Revolution
paper is EXACTLY my issue with your Excel 2003 workaround being the
solution I need.
Thank you again for your help.
Marco Russo
Sorry for delay - if you see delay again please ping me at marco (dot)
russo (at) sqlbi (dot) com - I'll take a look sooner.
What I meant is that you have to create a hierarchy made of the levels
you want to show *AND* you have to hide the attributes that you used
for the levels. In other words, a hierarchy with:
Layout
Build
Class
levels.
If you're using a P/C hierarchy, this will be the only one visible to
the end user.
Let me know if it is more clear now.
Marco