During the development of a BI project, sometimes we need to use values which do not necessarily come from tables; these can be constants such as Data refreshed at or Today. The logical tables come in very handy for this purpose.
To create a logical table that returns the current date as a constant, do the following:
- Go to the Schema Objects | Tables folder and right-click on an empty space in the right pane. From the context menu, choose New | Logical Table.
- In the SQL statement text area, paste this sentence:
select DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) DateValue - Add one Column Object and name it
DateValue, and select Date from Data Type. - Click on Save and Close and name it
Today. - Update the schema.
We created a very simple table with one row and one column. This table will always return the current date at midnight; it is useful to filter records based on moving dates, or simply to display it in a report header. Moreover, since the table has only one row, we can join it anywhere without creating dangerous Cartesian products. Even with a CROSS JOIN, it will always multiply the results by one.
In SQL Server constants are generated with a SELECT statement without the FROM clause. If you want to display Eat at Joe's, it's as simple as:
select "Eat at Joe's"
(Sorry, blinking font not included)
In other RDBMS, you may find a different syntax: in Oracle, for example, the current date at midnight can be obtained with the sentence:
SELECT TRUNC(SYSDATE) DateValue FROM DUAL;
- The Attribute forms – ID and DESC recipe in Chapter 3, Schema Objects – Attributes