I was faced with a problem where normalization of a table was somewhat impractical due to the sheer size of the data. It was perceived that any indexing strategy, coupled with the size of the data and physical infrastructure, would hurt query performance. The decision was made by the modelers to leave a dimension off of the table and create a table for each unique value for the dimension. This created a challenge in that satisfying the requirements would require potentially hundreds of tables to be added to the universe. The solution: use the @prompt syntax to enable the user to choose the appropriate table to segment report data with. This will ensure that before the query is run, the table needed is selected.
Consider the following screenshot as a demo of what I was facing, where the statistical model data had identical structures in each table, but each table stored data for a different model.
To kick this off, we’ll add just one of these tables (doesn’t really matter which) to the universe structure window. This isn’t anything special. It is important at this step (in order to save time later) to create your objects for this table and any associated lookup tables associated with it.
Next, right click on the table in the structure view and choose Rename to alter the table name. If you’ve never renamed a table, this window is pretty self explanatory.
The key here now is to tweak the table name so that the @prompt syntax is utilized. You’ll observe that since the table names follow a common naming convention (that is very important), it is easy to replace that incremental number with a list of values in a prompt, or simply allow the users to enter the text free form. In my prompt syntax, I am not selecting a list of values to use, but am constraining the user to use only one value and enter the table number manually.
@prompt(‘Enter Model Number’,’A’,,mono,free)
That’s it. In the structure view, the table now looks like a weird table name.
In addition, objects created early on inherit the change in table name and will also take in the @prompt values when selected by the user.
That’s really it. A few points to remember:
- Set of a list of values if possible to clearly define what each of the table IDs represents to the users instead of making them guess. That way your @prompt can reference the LOV.
- Viewing the SQL will still show the @prompt in the SQL, but it will pass through to the database correctly.
- Remember that this solution will not pass a universe integrity check. Fear not. It still works at query run time.