US and Worldwide: +1 (866) 660-7555
Results 1 to 10 of 10

Thread: Query with Different hierarchies of the same dimension

  1. #1
    Join Date
    Dec 2012
    Posts
    121

    Default 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. #2
    Join Date
    Dec 2010
    Posts
    272

    Default

    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. #3
    Join Date
    Dec 2012
    Posts
    121

    Default

    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. #4
    Join Date
    Dec 2010
    Posts
    272

    Default

    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. #5
    Join Date
    Dec 2012
    Posts
    121

    Default

    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. #6
    Join Date
    Jan 2013
    Posts
    531

    Default

    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. #7
    Join Date
    Dec 2012
    Posts
    121

    Default

    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. #8
    Join Date
    Jan 2013
    Posts
    531

    Default

    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. #9
    Join Date
    Dec 2012
    Posts
    121

    Default

    Quote Originally Posted by mcampbell View Post
    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. #10
    Join Date
    Jan 2013
    Posts
    531

    Default

    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.

Tags for this Thread

Posting Permissions

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