Cognos Analytics with Watson

 View Only
  • 1.  Dynamic Cube Joins

    Posted Wed August 10, 2022 04:28 PM
    Trying to build a Dynamic Cube.
    The fact table has all required join columns and is working in a Framework Manager package.
    When creating any relationship in Cube Designer, no columns are listed in the measures table.
    Looking for suggestions on why the join columns are not listed under the Facts table.


  • 2.  RE: Dynamic Cube Joins

    Posted Wed August 10, 2022 08:00 PM

    I can't say that I recall encountering this problem.  The list should consist of all the columns of the table being used as the fact table.  You can see those columns in the source pane, which you can see by clicking on the show metadata checkbox in the view menu.

    You will notice that there is an error overlay icon on the measure dimension (it gets propagated up to the cube and to the model namespace as well).  Click on the measure and view the issues tab.  Tell us what errors say.  That could help us understand what cube designer thinks is a problem.

    If you click on one of the issues you will be able to move the mouse pointer to select all the text and copy it.


  • 3.  RE: Dynamic Cube Joins

    Posted Thu August 11, 2022 11:38 AM
    Interestingly, there is an error message: "Unable to find the table 'factLedgerAccounts_Statistics' that is referenced by 'Facts'."
    The way that this table was brought into the model:
    1. Create FM Package with 4 dimensions and one fact.
    2. Add joins in FM.
    3. Publish.
    4. In Cube Designer, Get Metadata from FM Package.
    5. Set required attributes for Dimensions.
    6. Right click Fact table > Import > As the Measure Dimension of a new Cube.
    7. Check issues for the Fact table - get Unable to find the table...


  • 4.  RE: Dynamic Cube Joins

    Posted Thu August 11, 2022 03:18 PM

    How is this fact table (Segments) modelled in FM? Are there more than one table in it?  What other characteristics of it are there?  Is the schema defined by any macros?  Macros in the metadata are not resolved, unlike during the running of queries.   (see 16.3.5 Model cleanup and refinement in the dynamic cubes redbook  IBM Cognos Dynamic Cubes | IBM Redbooks)

    Can you see factLedgerAccounts_Statistics in the package and the sources (when you import the FM package the schemas used in the package are brought into the sources pane as well)?  In this picture you can see them at the top left.


  • 5.  RE: Dynamic Cube Joins

    Posted Fri August 12, 2022 10:04 AM
    Good morning Ian,

    Thanks for your help. I've simplified the problem as follows:

    1. Create new FM model.
    2. Import 5 tables (1 fact, 4 dimensions) (the fact table has only measures and FKs, no macros anywhere)
    3. Create joins between the tables.
    4. Create and publish a package.
    5. Launch DCD and "Get Metadata" from FM Pkg.
    6. Right-click the Fact table and select "Import > As the Measure Dimension in a new Cube". 
    7. Expand the cube.
    8. Select the Measure dimension and click Issues.
    9. Error "Unable to find the table..." is returned.


  • 6.  RE: Dynamic Cube Joins

    IBM Champion
    Posted Thu August 11, 2022 03:01 AM
    Edited by Robert Dostal Thu August 11, 2022 03:03 AM
    Hi David,

    what kind of measures have you created in the "Facts" section of your cube? As soon as yo drag in any column from your fact table to a measure, you should have the list for the join columns. As Ian mentioned already: check your "Issues" tab on the bottom of the scree and tell us what's going on there.

    You can also select your cube in the model tree and double click the "Implementation" pane on the top right. Do all tables appear here?

    Good luck.

    Robert Dostal
    Team Leader BI

  • 7.  RE: Dynamic Cube Joins

    Posted Thu August 11, 2022 11:40 AM
    In this particular case, I've not created any measures - I'm only using the existing measures in the fact table.


  • 8.  RE: Dynamic Cube Joins

    Posted Fri August 12, 2022 11:42 AM
    Issue Resolved

    The problem was the cache of metadata from the underlying source.  The table was showing up in the Package but when expanding the source section of the metadata tree, the table did not show up there.  
    After refreshing the metadata for the source, the table showed up and the measure no longer has the issue with "Unable to find the table".

    Thanks @IAN HENDERSON​​ and @Robert Dostal for your assistance!​


  • 9.  RE: Dynamic Cube Joins

    Posted Tue August 16, 2022 07:50 AM
    There's more about the sources pane in sections 5.9, 6.10.2, and 6.10.4 of the Dynamic cubes redbook.