Viewing Specific Information

To view the list of fields in a table if they are hidden:

  • In the upper right hand corner of the table, click OR right-click the table name and select Show All Fields.
    Note

    Linking from another table to the fact table is not possible: links always need to be created from the fact table (which corresponds to the fact table having a left join to the other table).

To view only the columns from a table that is already being used in the data model:

  1. Right-click the table name and select Show Columns Only.
  2. After a join has been created, right-click it and change the join properties.
    Note

    By default, joins are created as left joins. Right-click the join to change it to an inner join or to delete it.

To display the SQL statement used in the Data Model:

  • Click the SQL Statement icon on the toolbar. A pop-up window is displayed. You can copy the SQL statement if needed.

    Note

    You may notice that the SQL statement ends with "where 1=2" instead of an actual WHERE clause. This modification is made to ensure that the SQL statement is not executed directly by default, without taking into account its potential impact on your source system. The produced SQL code includes all table joins, even if some of them require a filter typically provided by the end-user interface (such as a Selection Page or Generic Filter). In some cases, the SQL query may execute without any issues, but in other cases, it may generate billions of rows depending on the combination of tables if an additional filter is not added to the WHERE clause to limit the rows from certain joins.

    For example, consider a fact table containing all invoice lines with 100,000 rows, joined to a currency rate table with 15,000 rows that contains the exchange rates for each day over 5 years. The join is performed based on the currency but not on the date because the user needs to input the date to generate a report that shows values based on a specific exchange rate. If you execute the SQL query without specifying a date, you will get 5 * 365 * 100,000 = 182,500,000 rows. This number could become much higher if you have 5 million invoices.

    Adding the filter "where 1=2" does not return any rows. Before removing it, make sure that you have added an additional filter where necessary to prevent the SQL query from generating billions of rows and causing issues to your SQL database server.

    Additionally, you can limit the number of records returned by using the TOP argument in the SELECT statement (for example, SELECT TOP 1000).

To filter columns in the fields table, you can use the Search function:

  1. In the Search bar on the left, enter the field you are looking for.

  2. Click Save if you wish to save the table view with this search.