I can’t even begin to count the number of times I’ve been asked to create a reporting solution before the data, or even the tables, are 100% completed. One trivial issue encountered may be missing reference data in lookups. There’s an inexpensive option to help move the development lifecycle along.
In Designer, right click in the universe structure and choose Derived Table. In the Derived Table SQL, we’ll start to stub out a query that creates the reference data for us. In this example, we’ll start building out a product category table. The product category table, product_category, displays a category_id, a category_name and a category_description. This example assumes the database is Oracle, but this technique is also possible in other databases.
First, realize that the anatomy of this Derived Table is to use a little select statement for each row of your desired result set. For example:
select
‘1’ as category_id,
‘Shirts’ as category_name,
‘First category row for our derived table’ as category_desc
from
dual
In this query, we’ve simply forced the result into three columns creating the desired result set.
The next step is to append all the other rows we need to stub out our Derived Table. Consider the SQL for the next row of data.
select
‘2’ as category_id,
‘Pants’ as category_name,
‘Second category row for our derived table’ as category_desc
from
dual
With two SQL statements in place now, we need the glue to put them together. To turn this into a practical result set that Designer can use, slap a UNION in between.
select
‘1’ as category_id,
‘Shirts’ as category_name,
‘First category row for our derived table’ as category_desc
from
dual
union
select
‘2’ as category_id,
‘Pants’ as category_name,
‘Second category row for our derived table’ as category_desc
from
dual
The great part is this is not a database intensive query. It barely even needs to think. It just takes the strings you give it and it munges them together into a query result. In addition, you can shape your lookup data to whatever junk data has been inserted into the tables by testers.
This is really not advocated for huge lookup tables or even production reporting solutions necessarily. However, we’ve all had to deal with that “fire drill” at 5 PM on a Friday with no DBA or ETL resource in sight. Creating a quick lookup table for a universe is a quick fixer upper (couldn’t wait to use that in a blog) for reports that need some reference data.