US and Worldwide: +1 (866) 660-7555
Page 4 of 4 FirstFirst ... 234
Results 31 to 35 of 35

Thread: saiku + mondrian using star schema, boolean column showing all results, not unique

  1. #31
    Join Date
    Apr 2011
    Posts
    103

    Default

    Issue 1 (DLU step not working) The dim_customer table (right now) has:
    index_row
    instance_name,
    salesforce_name

    My first table input is:
    Code:
    SELECT	'ofcom' as instance_name, 'Ofcom' as salesforce_name
    FROM 	dual
    which I pass to the DLU step with the default settings and the following:
    Connection : my datasource
    target table: dim_customer
    Dimension field: instance_name
    Field in stream: instance_name
    Technical key field: index_row


    When I play it, I then get:

    Code:
    Couldn't execute SQL: insert into dim_customer(index_row, version) values (0, 1)
    ERROR: column "version" of relation "dim_customer" does not exist
    So I went back into the step and just deleted the 'version' from the Version Field area then when I play it I get:
    [CODE]
    Couldn't execute SQL: insert into dim_customer(index_row, null) values (0, 1)
    ERROR: syntax error at or near "null"
    [CODE]

    So i tried unchecking the Update Dimension check box and I get:
    Code:
    ERROR: column "date_from" does not exist
    So I delete those two value (Date range start field and Table daterange end) and I get:
    Code:
    Unexpected error
    java.lang.NullPointerException
    at org.pentaho.di.core.row.RowMeta.searchValueMeta(RowMeta.java:393)
    at org.pentaho.di.core.row.RowMeta.exists(RowMeta.java:118)
    at org.pentaho.di.core.row.RowMeta.addValueMeta(RowMeta.java:129)
    at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.setDimLookup(DimensionLookup.java:878)
    at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:222)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    at java.lang.Thread.run(Unknown Source)
    (I am running Spoon 4.4 btw.)


    At that point, I just thought I'd try the combination lookup step and that worked (even though I only passed it a non-combination lookup) so I ran with it.

    I did look at the ktr file you sent (again, THANK YOU!) and was trying my best to model after it, but I didn't want to 'run back to the forum' again until I tried a few more things. I would love to get this DLU step working - as it clearly is useful!

    Issue 2 (Regarding the 'what do I expect to see' in Saiku):

    The Saiku application lets one work with the Schema like an excel pivot table. From the default 'new' option, I can select my cube and with nothing on rows/columns, I want to move my 'validity' dimension to the FILTER option of Saiku and it only display the (2) options; instead of what it is (well, was doing...more on that in the next paragraph!) doing and showing VALID (or INVALID) multiple times, in fact, 1:1 with how many external numbers are in the dimension.

    In thinking this through, just a few moments ago, however, I noticed that I had put the isvalid column inside the 'External Number' hierarchy - which I think may have been the culprit. I just rebuild the Schema creating a separate hierarchy for 'Addtl Call Flags' and placed the isvalid column in there and now it's working. I can toss the 'validity' column on the filter tab and it only displays the two unique options.

    So (I think!) at this point, Issue 2 is working and, thus, the original post concern is resolved! BUT...I'd really like to solve this DLU step issue if possible (?)...

    Many thanks!

  2. #32
    Join Date
    Apr 2008
    Posts
    2,233

    Default

    Quote Originally Posted by loganseth View Post
    Issue 1 (DLU step not working) The dim_customer table (right now) has:
    index_row
    instance_name,
    salesforce_name

    My first table input is:
    Code:
    SELECT    'ofcom' as instance_name, 'Ofcom' as salesforce_name
    FROM     dual
    which I pass to the DLU step with the default settings and the following:
    Connection : my datasource
    target table: dim_customer
    Dimension field: instance_name
    Field in stream: instance_name
    Technical key field: index_row


    When I play it, I then get:

    Code:
    Couldn't execute SQL: insert into dim_customer(index_row, version) values (0, 1)
    ERROR: column "version" of relation "dim_customer" does not exist
    So I went back into the step and just deleted the 'version' from the Version Field area then when I play it I get:
    Code:
    Couldn't execute SQL: insert into dim_customer(index_row, null) values (0, 1)
    ERROR: syntax error at or near "null"
    So i tried unchecking the Update Dimension check box and I get:
    Code:
    ERROR: column "date_from" does not exist
    So I delete those two value (Date range start field and Table daterange end) and I get:
    Code:
    Unexpected error
    java.lang.NullPointerException
    at org.pentaho.di.core.row.RowMeta.searchValueMeta(RowMeta.java:393)
    at org.pentaho.di.core.row.RowMeta.exists(RowMeta.java:118)
    at org.pentaho.di.core.row.RowMeta.addValueMeta(RowMeta.java:129)
    at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.setDimLookup(DimensionLookup.java:878)
    at org.pentaho.di.trans.steps.dimensionlookup.DimensionLookup.processRow(DimensionLookup.java:222)
    at org.pentaho.di.trans.step.RunThread.run(RunThread.java:50)
    at java.lang.Thread.run(Unknown Source)
    (I am running Spoon 4.4 btw.)


    At that point, I just thought I'd try the combination lookup step and that worked (even though I only passed it a non-combination lookup) so I ran with it.

    I did look at the ktr file you sent (again, THANK YOU!) and was trying my best to model after it, but I didn't want to 'run back to the forum' again until I tried a few more things. I would love to get this DLU step working - as it clearly is useful!

    ...

    So (I think!) at this point, Issue 2 is working and, thus, the original post concern is resolved! BUT...I'd really like to solve this DLU step issue if possible (?)...

    Many thanks!
    Maybe we want to mark this thread Closed / complete / whatever and start a new one on the DLU.

    In short, DLU is designed to look for a table model with the following:
    Business Key(s) - How to look up the "Unique" record in the table
    Technical Key - How to tell the difference between two of the same Business Key
    Dates - When did this row become valid, and when is it valid until?
    Version - Given multiple copies of a business key, which version are we on?
    Attributes - things that you want to track based on the business key

    eg.
    TK | BK | DateFrom | DateTo | Version | Name | Gender
    1 | 1 | 01-01-1900 | 01-01-1990 | 1 | Billy Smith | M
    2 | 1 | 01-01-1990 | 01-01-2000 | 2 | Pat Smith | M
    3 | 1 | 01-01-2000 | 12-31-2199 | 3 | Pat Smith | F

    So if you are looking up the name for an Invoice for Customer #1, you look up the date (eg. 03-31-1995) along with the customer# (BK) and get the Name "Pat Smith" ... Make Sense?
    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux
    Signature Updated: 2014-04-02

  3. #33
    Join Date
    Apr 2011
    Posts
    103

    Default

    @gutlez - yes. this makes perfect sense to me and the table I have does not look like that (not an SCD).

    I am looking, now, at how to mark this issue as RESOLVED...

    I learned an incredible amount on this thread. Thank you both!

  4. #34
    Join Date
    Nov 2008
    Posts
    722

    Default

    Quote Originally Posted by loganseth View Post
    @gutlez - yes. this makes perfect sense to me and the table I have does not look like that (not an SCD).

    I am looking, now, at how to mark this issue as RESOLVED...

    I learned an incredible amount on this thread. Thank you both!
    Issue 1: Probably just semantics here but if you read Ralph Kimball's books he says that every dimension (excluding junk dimensions) is an SCD - thus they should all have the version, date_from, and date_to columns. The Dimension lookup/update uses those fields differently depending on how you set the "Type of dimension update" values (Punch through, Insert, Update, ...) for each column you list on the "Fields" tab. Note that if you hit the "SQL" button when you are configuring the DLU step, you will get a dialog box with the recommended SQL statements needed to either create or alter the associated database table so it aligns with the setup in the DLU step. It isn't always exactly right but you can make modifications before executing the statements.

    Issue 2: When I took a peek at your schema.xml file I could see that your Level definitions were off. The "Valid" and "Possible" Levels really aren't levels at all - they are more likely Properties. The trick is to align your levels from coarse-to-fine and then define properties for what's left over that doesn't describe another level. From my example transformation, I was thinking the external number dimension should look something like this, however, I have no idea how well Saiku handles properties. There are, of course, other ways to do it.
    Code:
        <Dimension type="StandardDimension" visible="true" foreignKey="ExternalNumberKey" highCardinality="false" name="External">
          <Hierarchy name="By Location" visible="true" hasAll="true" primaryKey="ExternalNumberKey">
            <Table name="dim_external_numbers_view">
            </Table>
            <Level name="CountryRegion" visible="true" column="Region" type="String" internalType="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
            </Level>
            <Level name="StateProvince" visible="true" column="StateProvince" type="String" internalType="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
            </Level>
            <Level name="City" visible="true" column="City" type="String" internalType="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
            </Level>
            <Level name="Raw Number" visible="true" table="dim_external_numbers_view" column="external_number_raw" type="String" internalType="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
              <Property name="Is Valid Number" column="IsValidNumber" type="String">
              </Property>
              <Property name="Validity" column="Validity" type="String">
              </Property>
              <Property name="Validation Result" column="ValidationResult" type="String">
              </Property>
              <Property name="Number Type" column="NumberType">
              </Property>
            </Level>
          </Hierarchy>
          <Hierarchy name="By Number" visible="true" hasAll="true" primaryKey="ExternalNumberKey">
            <Table name="dim_external_numbers_view">
            </Table>
            <Level name="Country Code" visible="true" column="CountryCode" type="Integer" internalType="int" uniqueMembers="false" levelType="Regular" hideMemberIf="IfBlankName">
            </Level>
            <Level name="National Number" visible="true" table="dim_external_numbers_view" column="NationalNumber" type="Integer" internalType="long" uniqueMembers="false" levelType="Regular" hideMemberIf="IfBlankName">
              <Property name="Raw Number" column="external_number_raw" type="String">
              </Property>
              <Property name="Is Valid Number" column="IsValidNumber" type="String">
              </Property>
              <Property name="Validity" column="Validity" type="String">
              </Property>
              <Property name="Validation Result" column="ValidationResult" type="String">
              </Property>
              <Property name="Number Type" column="NumberType" type="String">
              </Property>
            </Level>
          </Hierarchy>
        </Dimension>
    I'm fine with leaving this topic open for a while. We have two interleaving topics but they are closely related.

    Also, let me know if you want to see any more of my example transformation and schema. I've made some more improvements...
    Last edited by darrell.nelson; 12-17-2013 at 05:35 PM.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  5. #35
    Join Date
    Nov 2008
    Posts
    722

    Default

    Quote Originally Posted by gutlez View Post
    eg.
    TK | BK | DateFrom | DateTo | Version | Name | Gender
    1 | 1 | 01-01-1900 | 01-01-1990 | 1 | Billy Smith | M
    2 | 1 | 01-01-1990 | 01-01-2000 | 2 | Pat Smith | M
    3 | 1 | 01-01-2000 | 12-31-2199 | 3 | Pat Smith | F
    OMG did Pat Smith have a sex change in 2000 or was that just a typo? Funny stuff.
    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
  •