Hitachi Vantara Pentaho Community Forums
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Member Name and &

  1. #1
    Join Date
    Sep 2010
    Posts
    100

    Default Member Name and &

    Hi,

    I'm building a schema and I'm facing the problem between the member key and member name.

    Assuming I have a dimension called Provider identified by provider_id and provider_name, I want to use provider_name or provider_id and get the provider name in both cases.

    If I'm right, both sentences are correct:

    select [provider].&[1] on rows ...

    select [provider].[provider 1] on rows ...

    I'm trying to do the same configuring the level as:

    name-> Provider
    column-> column_id
    nameColumn -> column_name
    type-> integer
    leveltype-> Regular

    With this I can do the second option but with the first option I get: "Mondrian Error:MDX object '[provider].[0]' not found in cube 'SERVICES'" while there's data.
    I also tried like [time].[month].[1] and i get the same error.

    And also played with captionColumn attribute but no way.

    What's wrong?

    thanks
    Last edited by Bronson; 11-06-2012 at 01:26 PM.

  2. #2
    Join Date
    Sep 2010
    Posts
    100

    Default

    I'll answer myself my silly question, when something is not working... try it tomorrow :-D

    The attribute for the name is nameColumn but you must remember that the member name is case sensitive while levels and dimensions not ...

  3. #3
    Join Date
    Sep 2010
    Posts
    100

    Default

    Hi again,

    I'm having problems again with this issue. I could use the provider name or id to reference it in a mdx query and make it run but, I can't make it run with the month level of the time dimension.
    I configured exactly as I did it for provider and it doesn't work, for any reason mondrian is trying to use &[1] as month name instead of month id.

    I've got year, month and day levels. I played with type and internaltype attributes, and namecolumn and captionColumn...

    This is the xml for dimension time:

    <Dimension type="TimeDimension" visible="true" highCardinality="false" name="DIM_TIME">
    <Hierarchy name="default" visible="true" hasAll="true">
    <Table name="DIM_TIME" schema="DW">
    </Table>
    <Level name="year" visible="true" column="year_id" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="month" visible="true" column="month_id" nameColumn="atr_month_name" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
    </Level>
    <Level name="day" visible="true" column="time_id" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    </Hierarchy>
    </Dimension>

    Any idea? thanks in advance

  4. #4
    Join Date
    Nov 2008
    Posts
    777

    Default

    Can you briefly show what your DIM_TIME table looks like?
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #5
    Join Date
    Sep 2010
    Posts
    100

    Default

    yes,

    DIM_TIME looks like:

    time_id |year_month_id| year_id| month_id| day |atr_month_name
    20080101| 200801 | 2008 | 1 |1 | January
    20080102| 200801 | 2008 | 1 |2 | January
    20080103| 200801 | 2008 | 1 |3 | January


    ...
    Last edited by Bronson; 11-14-2012 at 04:24 AM.

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    Seems to me your <Dimension type="TimeDimension"...> element is missing the attribute foreignKey="time_id" and your <Level name="Day" visible="true" column="time_id" ...> element should have the column attribute set to "day" if that is indeed the column name of the date number in your database table.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Sep 2010
    Posts
    100

    Default

    Hi,

    I added the foreign key and changed the column in the level day but still not working.

    I've been checking the mondrian logs and when I add the nameColumn then it uses this column in the Where sql clause, so it's like or I use the key column or the name but I can't use both. Is it like that with mondrian????

  8. #8
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by Bronson View Post
    Is it like that with mondrian????
    No. I usually find a schema error when I have that kind of trouble. I think your fact table and dimension table are not joining properly.

    Looking back at your schema again, in the <Hierarchy name="default" visible="true" hasAll="true"> element I think you are also missing the primaryKey="time_id" attribute. Note that the foreignKey attribute in the Dimension element needs to be set to the corresponding date key column in your fact table. That may or may not be "time_id" depending on the structure of your fact table.

    If you are still having trouble after make this change, please post a short sample of your fact table layout.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  9. #9
    Join Date
    Sep 2010
    Posts
    100

    Default

    Hi Darrell thanks for quick reply,

    I'll post the time dimension exactly as it is (cause i'm using spanish I translated it in my previous post for better understand). You'll see I added the primaryKey in the hierarchy, and also the hour level. Thanks


    <Dimension type="TimeDimension" visible="true" highCardinality="false" name="DIM_TIEMPO_CREA">
    <Hierarchy name="Default" visible="true" hasAll="true" primaryKey="ID_FECHA">
    <Table name="DIM_TIEMPO" schema="DW">
    </Table>
    <Level name="Anyos" visible="true" column="ID_ANYO" type="Integer" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never">
    </Level>
    <Level name="Meses" visible="true" column="ID_MES" nameColumn="ATR_NOMBRE_MES" type="Integer" internalType="int" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never">
    </Level>
    <Level name="Dias" visible="true" column="ID_DIA" type="Integer" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never">
    </Level>
    <Level name="Hora" visible="true" column="ATR_HORA" type="String" uniqueMembers="false" levelType="TimeHours">
    </Level>
    </Hierarchy>
    </Dimension>

    Name:  time_dimension.jpg
Views: 30
Size:  16.1 KB
    Last edited by Bronson; 11-16-2012 at 03:53 AM.

  10. #10
    Join Date
    Nov 2008
    Posts
    777

    Default

    Quote Originally Posted by Bronson View Post
    <Dimension type="TimeDimension" visible="true" highCardinality="false" name="DIM_TIEMPO_CREA">
    Your Dimension element still doesn't have the foreignKey="" attribute. It needs to be set to the name of the column in your fact table that contains the references to the ID_FECHA column in your time dimension table.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.