Populate Physical Columns with a Stored Procedure or Select Statement

If you chose the stored procedure or select statement as the physical table's source type, then you create physical columns and write a stored procedure or select statement to populate them.

Preview isn't available for columns populated by a stored procedure.

Use this information to help you write a stored procedure or select statement:

  • Stored procedure - Provides a default stored procedure and database-specific stored procedures. Requests for this table call the stored procedure. The default initialization string is run when the queried database type doesn't have a corresponding database-specific string defined.

    Stored procedures within an Oracle Database might not return result sets. You can't initiate stored procedures from within Analytics Cloud. You need to rewrite the procedure as an Oracle function, use the Oracle function in a SELECT statement in the initialization block, and associate the Oracle function with the appropriate session variables.

    The following example shows a SQL initialization string using the GET_ROLES function that's associated with the USER, GROUP, and DISPLAYNAME variables. The function takes a user Id as a parameter and returns a semicolon-delimited list of group names: 

    SELECT user_id, get_roles(user_id), first_name || ' ' || last_name
FROM csx_security_table
WHERE user_id = ':USER' and password = ':PASSWORD'

  • Select statement - Provides a default SELECT statement and a SELECT statement for any databases that you select. You need to manually create the table columns. The column names must match the ones specified in the SELECT statement. Column aliases are required for advanced SQL functions, such as aggregates and CASE statements.

    The default SELECT statement is run when the queried database type doesn't have a corresponding database-specific SELECT statement defined.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Physical Layer Physical layer icon.
  4. In the database pane, browse for and double-click the table where you want to add the stored procedure or select statement.
  5. In the physical table, click the General tab and in the Source field, select either Stored Procedure or Select Statement.
  6. Click the Columns tab.
  7. Click Add Column and select Create New Column to add the new physical column needed to store the data. Add more columns as needed.
  8. Depending on the source you selected, go to the DEFAULT field and enter your default stored procedure or select statement.
  9. Optional: To add stored procedures or select statements written for specific database types, click Specify query for additional databases and then click Add additional databases and click to select the needed databases.
  10. Optional: In the list of databases, click a database and enter its required stored procedure or select statement.
  11. Click Save.