Load rule improvments in Essbse 21.2 (part 2 Data Loads)

 If you have not read part one of this post o n 21.2 Load rule improvements, it would be wise to do so. You can find it HERE. it talks about the general connectivity to multiple sources. I won;t repeat all of that in this post. I'll concentrate on the changes that are specific to Data Load rules.  Again, this has not be documented by Oracle yet and I am sure I'll miss some features, but fr you the gentle reader, it is a good start to getting going. 

It is funny within a day of upgrading to 21.2, I had a question on what happened to the ability to select a data column in the rule. I'll get to it. 

When you create a new rule, Just like Dimension build rules you are presented with the screen to select your source (and like dimension build rules you can defer this to later)

 

 

Once you have selected the data source and actually open the rule, you will see a slight difference from 19c/OAC.Now instead a second drop-down on the fields, you see a little pencil.  As an aside, notice the field names are no longer a drop down list but rather a text box. This means you can manually enter in the name of the dimension or member or select it later in the Member Selector (described next).

  

Clicking on it brings up the box to exit the load properties of the column. Oracle is calling this Member Selector.  This is different from clicking on the properties item in the rule. This member selector allows you to set a couple of things. 

1. If needed, the outline is loaded to select the dimension name of member for the field. 

2. the storage type for the field (I'll get to this later)

3. Is this a data field

4. Is this the one and only Data column (to be tagged as *Data*)

5. A preview of what the data in the columns is (if you opened a data source) 

 

If the column is a data column AND it is the only data column AND you want the column to be tagged as *Data* then you will want to check the box that says "Single Data Field" It will change the column name to *Data*. Remember just like rules in EAS, you can only have a single field tagged as *Data*.

If the column is tagged as a data column, you will see the drop down for "Storage Type" I don't like the name of this column as it is not the storage type, but more an aggregation method.  If you look at the values in the drop down, you will find 

 

What does this mean? I believe it was also in 19c, but again never documented. It is an improvement to load rule functionality. You can now select how data gets loaded by selecting Sum, Min, Max or Count where common intersections of data are evaluated.

Assume you have the following:

Dimension1	Dimension 2	Dimension 3	Data
100-10	New York	Sales	200
100-10	New York	Sales	100
100-10	New York	Sales	300
100-10	New York	Sales	500
100-10	New York	Sales	400

If you select Min, the value of 100 will be loaded to the intersection, Conversely Max will give you 500. Of course sum would give you 1500. Count would be the number of rows with the intersection 5. 

If you select nothing, then the setting in the global property  would take over and, but default that is "override" which would give you the  value of 400 in my example. 

Now suppose, you want to have multiple data columns, like the periods going across, or in my example below, multiple measures

 

In the Member selector, each of the data columns would be tagged as a data column But the single data filed would not be checked. 

 

You will also see some of your old favorites are back in the selections in the rules, making more like EAS.

 

The one thing I wish they had included  is a feature they added to index based dimension build  rules and that is the ability to use expressions to create new columns. I can be very creative using the join and sub-string logic in index based dim build rules, but to create the same thing int he data load rule, I have to use my old clunky methods of splitting columns, creating column text, doing joins or create joins, and moving columns around. 

As you can see the rules editors have gotten much better and Oracle listens to feedback. If I missed a new feature in the editors, I'll blog about it when I find it, or you can comment about it as sharing is caring.