US and Worldwide: +1 (866) 660-7555

# Thread: Query with Different hierarchies of the same dimension

1. Senior Member
Join Date
Dec 2012
Posts
121

## Query with Different hierarchies of the same dimension

Hi all,
i have a dimension D which have two hierarchies: X and Y.
I tried to do a query to verify the different results, but i observed that the query returns the same result.

The query:
1)
SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
[Localita].[CENTRO] ON ROWS
FROM [Cube]
WHERE [D.X].[mylevel].members
------------
2)
SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
[Localita].[CENTRO] ON ROWS
FROM [Cube]
WHERE [D.Y].[mylevel].members

1) and 2) return the same result!!
Why the where does not work?

Thanks,
marco

2. Senior Member
Join Date
Dec 2010
Posts
293
Ciao Marco,
in my opinion, it works well.

If you run the following queries you'll get different results

SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
{[D.X].[mylevel].members} ON ROWS
FROM [Cube]
WHERE [Localita].[CENTRO]
------------
SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
{[D.Y].[mylevel].members} ON ROWS
FROM [Cube]
WHERE [Localita].[CENTRO]

but, if you use the same table for both hierarchies, [D.X].[mylevel].members = [D.X].[All] = [D.Y].[All] = [D.Y].[mylevel].members

3. Senior Member
Join Date
Dec 2012
Posts
121
Hi lukolap,
i need to use them on the WHERE because this query is the datasource of a chart.
Moreover, the D.X hierarchy points to the table MyTable. Instead, the hierarchy D.Y points to a view (on my database) which extracts a subset from the table MyTable (it's like select * from MyTable where <condition on a field table>).

Any suggestions?

thanks very much for the support!
Marco

4. Senior Member
Join Date
Dec 2010
Posts
293
I just tried to create a similar case in my pentaho environment and it works fine.
Have you cleaned pentaho repository cache and mondrian cache?

5. Senior Member
Join Date
Dec 2012
Posts
121
Yes,i reimported the schema on my system.

I resume my issue with my real case.

I have a Dimension called ModelloDispositivo. It has more hierarchies which gets data from different views. The views on db give the same output format (same columns) of the default table, called test_devices, how explained before.

When i do th following queries i get the same results:
1)
SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Localita.default].[CENTRO].[LAZIO]})} ON ROWS
FROM [WindCube2]
WHERE {Hierarchize({[ModelloDispositivo.Modelli NO LTE IOS].[modellodispositivo].Members})}

-------
2)
SELECT
NON EMPTY {Hierarchize({[Measures].[Numero Test]})} ON COLUMNS,
NON EMPTY {Hierarchize({[Localita.default].[CENTRO].[LAZIO]})} ON ROWS
FROM [WindCube2]
WHERE {Hierarchize({[ModelloDispositivo.Modelli LTE IOS].[modellodispositivo].Members})}

The two hierarchies are defined as follow. There is a level (modellodispositivo) and a Schema View, on which i do a query like this:

select * from nolte_view where device_type=0 for the 1)
select * from lte_view where device_type=0 for the 2)

Any ideas?

6. Senior Member
Join Date
Jan 2013
Posts
721
As a general rule each fact table row should map to a member of a dimension the fact table is joined to. So adding a WHERE constraint that includes all members of a level will typically have no effect.

Would it be possible to create a dimension in your model that has a [NO LTE] member and an [LTE] member? Or possibly include that as a level within your dimension? You could then use a WHERE clause that actually imposes a constraint, e.g.

WHERE ( [ModelloDispositivio].[NO LTE IOS] )
and
WHERE ( [ModelloDispositivio].[LTE IOS] )

7. Senior Member
Join Date
Dec 2012
Posts
121
Hi mcampbell,
sorry but i didn't understand what you mean.
I have just created what you suggested me, that is a Level (not a member, you cannot define a member under a hierarchy!).
I repeat my model:

Dimension: ModelloDispositivo
Hierarchy: Modelli LTE IOS, Modelli NO LTE IOS
Level: modellodispositivo

I cannot create two levels because the table is different.

Is there a way to obtain a filtering using the where condition.

Suggests on change my schema are also accepted!

Thanks very much.
Marco

8. Senior Member
Join Date
Jan 2013
Posts
721
My suggestion was to use a single hierarchy with with the following structure:

Dimension: [ModelloDispositivo]
Hierarchy: Default
Level: [(All)]
Level: [
Modelli LTE] -> members { [LTE IOS], [NO LTE IOS] }
Level: [modellodispositivo]

If I understood you correctly, the two hierarchies you have right now are based on two separate views. Can you use a single view that has the [LTE IOS] vs. [NO LTE IOS] indicator as a field?

9. Senior Member
Join Date
Dec 2012
Posts
121
Originally Posted by mcampbell
My suggestion was to use a single hierarchy with with the following structure:

Dimension: [ModelloDispositivo]
Hierarchy: Default
Level: [(All)]
Level: [
Modelli LTE] -> members { [LTE IOS], [NO LTE IOS] }
Level: [modellodispositivo]

If I understood you correctly, the two hierarchies you have right now are based on two separate views. Can you use a single view that has the [LTE IOS] vs. [NO LTE IOS] indicator as a field?
Ok, may be i can change my tables in order to define a flag which tells me if a device is LTE or not.

However, i cannot define a member under a level using the Schema Workbench. When you write [LTE IOS], [NO LTE IOS] you refer them as members of the level [Modelli LTE]? If yes, how can you do this?

Thanks for the support,
Marco

10. Senior Member
Join Date
Jan 2013
Posts
721
I'm not familiar with your data, but if you are able to create a field in your dimension table (or view) with values "LTE IOS" and "NO LTE IOS" as appropriate, then you would simply define the level like any other.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•