Maybe this is well known but wanted to help share.
Consider that you have the following persistent class defintions
An Invoice Class with a property reference to Provider
Class Sample.Invoice Extends (%Persistent, %Populate)
{
Parameter DSTIME = "AUTO";
Property InvoiceNumber As %Integer(MINVAL = 100000) [ Required ];
Property ServiceDate As %Date(MINVAL = "+$h-730") [ Required ];
Index InvoiceNumber On InvoiceNumber;
Property Provider As Sample.Provider [ Required ];
Index Provider On Provider [ Type = bitmap ];
/// Build some invoices, this will firstly create 100 Providers
/// <Example>
/// Set tSC=##class(Sample.Invoice).Build()
/// </example>
ClassMethod Build(pCount As %Integer = 100020, pInit As %Boolean = 0) As %Status
{
#dim tSC As %Status=$$$OK
#dim eException As %Exception.AbstractException
try {
If pInit {
$$$THROWONERROR(tSC,##class(Sample.Provider).%KillExtent())
$$$THROWONERROR(tSC,##class(Sample.Invoice).%KillExtent())
}
$$$THROWONERROR(tSC,##class(Sample.Provider).Populate(100))
$$$THROWONERROR(tSC,##class(Sample.Invoice).Populate(pCount))
}
catch eException {
Set tSC=eException.AsStatus()
}
Quit tSC
}
}and Provider
Class Sample.Provider Extends (%Persistent, %Populate)
{
Property Name As %String [ Required ];
Property NPI As %Integer(MAXVAL = 9000000000, MINVAL = 100000000) [ Required ];
}If you call the Build method in Sample.Invoice you can query this with SQL
SELECT
InvoiceNumber,Provider->Name, Provider As ProviderId,ServiceDate
FROM Sample.Invoice
and see
.png)
The area this article discusses is deciding how to create a dimension on Provider.
What I have found works well is to following this pattern
.png)
What this does is
1. Define the dimension Unique Id on Provider(which is the Id from Sample.Provider). This is important because it's entirely possible that there is more than one Provider with the name SMITH,JOHN. By defining the dimension Level on the property Provider we are saying make the dimension table based on a unique Provider. If we look in the generated dimension table we see
.png)
2. Define a Property for the Level that
a. Identifies the Property = Provider.Name
b. Get value at runtime= Yes
c. Use as member names = Yes
This has the side effect of defining in the dimension table the following Property declaration
/// Dimension property: Name<br/>
/// Source: Provider.Name
Property Name As %String(COLLATION = "SQLUPPER(113)", MAXLEN = 2000)
[ Calculated,
SqlComputeCode = {Set {Name}=##class(Sample.BI.Cube.Invoice.Provider).%FetchName({Provider})}, SqlComputed ];
with the %FetchName method looking like
/// Fetch the current value of %FetchName.<br/>
/// Generated by %DeepSee.Generator:%CreateStarTable.
ClassMethod %FetchName(pKey As %String) As %String
{
// If we don't a value, show key as this is most likely the NULL substitute
Set tValue=pKey
&SQL(SELECT Name INTO :tValue FROM Sample.Provider WHERE %ID = :pKey)
Quit tValue
}
What this means is that when a dimension members are retrieved it will return the Provider Name and not the Provider Id.
Using Analyzer we can see
.png)
Why is this important?
- If the Provider name changes in Sample.Provider the cube does not have to be rebuilt or synchronized. If we have 100s of millions of invoices and the Provider names changes we dont want to have to rebuild or synchronize the Invoice cube due to a single Provider name change
- The Dimension table for Provider is based on the Provider(id) so this allows us to have more than one provider with the same name in the dimension table/cube.
If instead of defining a Dimension Level Property we define the Level Property= Provider.Name this means when the cube is built or syncrhonized
- the dimension unique index is based on Provider.Name which means all of the providers with the same name get aggregated under the same name
- If the Provider.Name changes we are required to rebuild the cube
.png)
.png)
.png)
.png)
.png)
.png)
.png)
