Load rule improvments in Essbse 21.2 (part 1 Dimension Builds)

 While others are touting the Federated Partitions as the best thing in the recent Essbase 21.2 patch, it should be noted that it is only applicable to OCI Cloud marketplace installations. While I think it is a worthwhile feature, I'm holding judgement until some enhancements are made to it. I'm sure I'll blog about it at some point.  To me, the best thing in the latest patch that is available to both Essbase independent and Essbase Marketplace is the improvements to the Rule editor. Don't get me wrong, There were things I really liked about the rules editor before. Index based load rules, not having to go to a separate screen to set properties, and more, but there things I did not like, for example, not being able to change the delimiter without starting over and only being able to preview data for flat files and datasources. Being so new, there is little written about it in the documentation, so you can use this blog as your introduction to the changes. 

We will start with Dimension build rules although the data connection changes are applicable to both Dimension and data load rules. (I won't repeat it when I discuss the data load changes)

To begin, when you create or open a dimension build rule, you now get a screen to select your source. I know you will tell me you got that before, but this is an expanded selection screen and more importantly if you skip it, you can still access data later. I'll get to that.  But with the new screen there is more to select. Of course you have to give the rule a name but that is the only required information

If you drop down the Source type, you will get a lot more options  

 

Based on what you select, the screen will change. For example I want to connect to a SQL Server DB, so I selected ODBC (DSN-less). When I do, the screen changes to the following:

 

It has a nice hint when you are on the Server input to show you the syntax you need for different sources. Note, you need to input the whole string including sqlserver  

I entered my connection information and the query I wanted to execute

 

I'm using a data source that will build Sample Basic. I would typically not do a select * and explicitly list the columns, but got lazy for the example.  I entered in the other pieces on information like the user, password and rows to preview. Note, the ID and password are not saved and if I needed to refresh the data, I had to reenter them every time.

When I previewed the data , I got:

If I did not like what I got, I could click to go back, I just clicked Proceed. 

This brought my sample data into the editor where I could do the typical things to set up the dimension build rule

There is a difference from before. If I select Source properties, I can go to the SQL tab and change my data source and refresh the data.

Note,as I said before the ID and password are not  saved, to refresh the data, I had to reenter them and kept forgetting to do so.  Also note, If you just click OK and not preview data it will save any changes you made, but not refresh the data on the screen.

I changed my query to add make the parents Dynamic calc (and specify the column names)

select
       [FAMILY]
      ,[FAMILY_ALIAS]
      ,[CONSOLIDATION]
      ,'X' as Data_Storage
      ,[SKU]
      ,[SKU_ALIAS]
 from ProductDim

I then clicked on preview data and the screen changed to 

If you compare the output to the older preview picture, you will notice it added the new column, but my last column is now gone. The editor did not automagically adjust the number of columns it shows. In order to get the last column back, I had to click on Filed 5 and then add a regular column

Once I did that, the column reappeared. 

For completeness, I went ahead and filled in the columns and verified the rule 

  

This post is probably too long already. I do another post on the changes to data load rules. Hopefully you have found this to be helpful...