Business Rules vs. Metabase changes

Certain fields in the eProphet schema are created as computed columns on the business entity.  These fields ship with a standard calculation that you can change to meet your business needs. 

For example:   if you look at Figure 1 in the attached document you will see that the business rule for the field on the Project Dimension named Project_Id_Combi is (((isnull([PROJECT_DESCR],'')+' - ')+isnull([PROJECT_ID],''))).    This rule can be easily changed and saved to the schema   [The syntax for this statement is standard ANSI SQL or TSQL].  The beauty of a computed column is that the changes take effect immediately without reprocessing the entity.  The downside of computed columns is that not all scenarios can be handled.  For example, I received a request from a client that wanted to change the computation to use the first five digits of the Project_ID and concatenate that with the description from the first matching record.  Looking at the dataset below you see that they wanted to create a master grouping based on the first five digits of the Project ID

Project ID                                            Description                                                        New Column

ABCDE100                                           Home Theater Install                                     ABCDE – Home Theater Install

ABCDE200                                           Home Theater Install  - Design                  ABCDE – Home Theater Install

ABCDE300                                           Home Theater Install  - Sound Test          ABCDE – Home Theater Install

Here is the sql statement required to do this: 

(isnull(substring(Project_id,1,5),'')+' - '  +

(Select Top 1 isnull(B.[PROJECT_DESCR],'') from DimPROJECT B where isnull(substring(B.Project_id,1,5),'')  = isnull(substring(DimProject.Project_id,1,5),'')))

Unfortunately, if you tried to add this statement as the business rule for a computed column you will receive the following error:    “Subqueries are not allowed in this context.  Only scalar expressions are allowed.” 

Fortunately, eProphet offers you the ability to override the ETL business logic and therefore you can add this statement to the default expression for a user field on the Project Dimension.  See Figure 2 in the attached document for details.  Remember, that any changes made to the business rules on the Metabase will not take effect until the Entity’s data load process is executed.  The key difference between modifying a business rule vs. the Metabase is that when changing an attribute via the Metabase you are actually storing data in the table associated with the entity rather than modifying a computation {which is the result of changing a business rule}.  The upside of modifying the Metabase attribute is that any valid sub-query can be used.