Database Reference
In-Depth Information
Figure 3.17
Create date elements.
Figure 3.18
Month.
notice that in this example the “quarterAsString” function extracts the quarter
from the date-time stamped column. you can create the same result by creating a new
Dimension Element and using the “quarterAsString” function in the Caption Binding
area. The right-click option is a timesaving shortcut provided in the Studio uI.
Aside from the various date-time options there are a series of functions that are use-
ful for crafting dimension elements. The string functions, for example, are particularly
useful in crafting elements. For instance, you can substring the contents of a column and
concatenate the contents to another column to create a unique set of member names. This
code concatenates the product code to the first four characters of the product vendor code:
'substr'( connection : \'SampleSQL'::'TBC.dbo.SUPPLIER'.'SUPPLIER_
ALIAS', 0, 5 ) || "-" || connection :
\'SampleSQL'::'TBC.dbo.PRODUCT'.'SKU'
There are a few things to note about this example:
1. The substring function starts at the first character in the input column (denoted
by 0) and then proceeds to the fifth character, exclusive.
2. The || character denotes concatenation in PL/SQL; t-SQL uses the + character.
3. The tBC.dbo.ProDuCt.Sku field comes from a different table. As such, this
combined function example leverages the mini-schema and a join from one
dimension table, across the fact table and into another dimension table.
