- Business Intelligence with MicroStrategy Cookbook
- Davide Moraschi
- 318字
- 2021-07-23 15:32:01
Generating constants with SELECT statements
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.
Getting ready
You can find the SELECT
statement for this recipe in the companion code file.
How to do it...
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.
How it works...
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.
There's more...
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;
And the logical table would work just the same.
See also
- The Attribute forms – ID and DESC recipe in Chapter 3, Schema Objects – Attributes
- Managing IaaS and DBaaS Clouds with Oracle Enterprise Manager Cloud Control 12c
- 資本的眼睛
- Citrix XenApp? 7.5 Desktop Virtualization Solutions
- Azure Strategy and Implementation Guide
- 注冊會計師全國統一考試專用教材:審計
- 財務審計實務指南
- Microsoft System Center Data Protection Manager 2012 SP1
- 陜西國家統計調查專題研究集萃(2006—2015)
- 2016年度注冊會計師全國統一考試專用輔導教材(圖解版):審計
- 新編統計學
- 陜西國家統計調查市、縣優秀報告集萃(2006—2015)(上下)
- 行政單位經濟責任審計實務指南
- Implementing VMware Horizon 7.7
- 效益實現管理實踐指南
- 財務會計習題集