Rules in Essbase
Rules are kind of partial transformation/staging area which
is available in Essbase to build dimensions and load data into Essbase. With
the concept of Rules most of developers and administrators lives have become
easier. With Rules you can do a bit of transformations in terms of Data &
Metadata changes on your data source which can be a text file/excel file/RDBMS.
We use rule file the data is not in a formatted way which is not understandable
by Essbase Database. For Example You want to load data to Actual scenario
member but how does Essbase know which scenario it has to load the data into !
In writing rules we become a great friend of Essbase and no
way Essbase gets confused with the process unless you confuse the Essbase with
wrong definitions/steps in defining/creating your rule files. J
In reality how it is helpful for our lives, ! we have a requirement of creating a dimension
with 1000 members under the dimension and hierarchies defined. Don’t get ready
for big work of creating them manually , you have a great feature to do it
easily which I told you at start of this post, am I not ?(most of developers and administrators lives have become easier).
Through Rules File
you can build dimensions as well as load data .
There are 3 types of
methods of building dimensions
1)Level References
2)Generation References
3)Parent Child References.
Don’t worry
if you don’t know what there Reference methods are !!?
For example
lets below dimensions image and will try explaining you more practically
through this.
We have all standard dimensions in the above image.
Account is the dimension name, PandL, Balance Sheet etc… are
the members below the dimension. Ok now what is Revenue1 & PandL ? They are
no doubt members but to locate Revenue1
, it’s hard to tell Account child is PandL and its child is Revenue and its child is Revenue1. We have
simple terminology to address & locate Revenue1 .
Account is the dimension name, PandL ,Balance Sheet are
children of Account, Revenue & Expenses are children of PandL and Asset ,
Liability & Equity are children of Balance Sheet. And Revenue is parent of
Revenue1, Revenue2….Revenue4 , to say in terms of parent child relation.
In Generation relation, Account is Generation1(Gen1) and
PandL , Balance Sheet are
Generation2(Gen2) and Revenue, Expense, Asset, Liability, Equity are
Generation3(Gen3) and rest are Generation4(Gen4).Note : Generation always
starts with 1.
In Level relation all the last members
(Revenue1-4,Expense1-4,Asset1-4,Liability1-4,Equity1-4) are Leve0 members,
Revenue , Expense , Asset, Liability , Equity are Level1 members and PandL,
Balance Sheet are Level2 members and finally account is Level3 members .
Let’s start building dimensions shown above through rules
where you get better understanding.
To create a rules file, Right click on Database and Create à Rules file (Rules are files are specific to database).
Let’s build Account dimension through Generation Reference Method. Once you click on Create Rules
File you will get below screen
By Default Data loading option would be selected , as we are
building dimension, select Dimension build fields.
Prepare you data source file as below created in terms of
generations.
Now that your data source for building Accounts dimension is
ready , lets continue creating rule file
When you browse and open you data source file, you will see
the content of your data source as below
By default delimiter will be Tab , change the delimiter to
comma as you have csv as your data source.
When you click OK you will see your data source gets
separated with columns as below.
As we don’t have Accounts dimension in the outline, we have
to define the dimension as we are creating through rules file.
You will get below dimension build setting window
Go to Dimension Definition tab and select Rules file and
give Dimension name which we are building, incase if Accounts dimension was
already there then it would be listed under Outline as below and we need to double click
it to make as chosen dimension which we are building which will be something
like below.
Once you enter Accounts after selecting Rules file radio
button above, press enter , it should come as below.
Now that we have defined dimension that we are creating and
define the method of building dimension.
Goto Dimension Build Settings tab and double click dimension that you are going to build and select Use generation references
as Build method.
Once this is done, we need to set field properties to each
filed like what each field mean to Essbase.
Highlight each column(Fileld) as below and set their
properties as below now in this case, as
we are building accounts dimension through header definition option and we have
already defined that , we need not consider the first field here in this case
which is dimension name so you can ignore this field by selecting FieldàPropertiesàDimension Build
Properties tab and select ignore filed for Field1 during dimension build.
You will get Field properties dialogue box, Double click on Accounts dimension below Dimension: it should be shown beside it
and double click on Generation , it should come as Type : Generation and then
set Generation of the member in that field, as its Dimension name give
Generation number as 2.(Accounts- which is dimension name is Generation1)
Click next and select Generation and set generation number
likewise for other remaining fields once those selections are done, it will
look something like below.
As we don’t need Gen1,Gen2,Gen3,Gen4.. row as they are not
part of outline requirement and we don’t need it, so it can get rid by a
feature of skipping lines in rules file which can be available under OptionsàData Source Properties
and Header tab and give number of lines to skip as 1 as we wish to skip only
the 1st line.
And also we need to ignore first field as we don’t need
Accounts as we have defined it in dimension header definition in dimension
build settings.
Click on Validate and you should see success message.
Save the rule file , and create the dimension with your rule
file by below process
Browse your source file to build accounts dimension and
browse rules file
and click OK.
Done , you have built your accounts dimension. Lets review
it.
Don’t forget to save your outline to save the dimension
accounts created on outline through your rule file.
Now let’s see how to create dimension through Level Reference method .
Let’s try creating Entity dimension through Level Reference
method, before we get started with building rule file, we need to prepare our
source file.
To build Entity Dimension our source file should of below
format
Level0 Level1 Level2
“Arunachal
Pradesh” “East” “Entity”
“Assam”
“East” “Entity”
“Manipur”
“East” “Entity”
“Goa” “West”
“Entity”
“Gujarat”
“West” “Entity”
As I put
through Excel it gives a nice column format data.
Now that we
have prepared our source file, If you confused – how did I manage to
build/create the source file , it’s just simple if you still remember
Generations,Levels,Parent-Child,Children,Parent etc. keywords explained at the
start of this post.
Entity is
the dimension name and rest of all are children for Entity and as we are
building Entity dimension through level references and as said Levels always start with 0 (from bottom
& Generations start with 1 from top with dimension name as Generation 1) so
all my base members (members which doesn’t have children below them) becomes
level0 and their parents become level1
and likewise we traverse till the dimension level numbering all the members
with their respective level numbers.
Got it.
!!!! Still we haven’t built the
dimension we just completed discussion on our source file.
Let’s not do
make this moody conversation anymore and let me make more interesting with
screen shots makes more lively for your practice & understanding.
To build
dimension first and foremost thing we should do is to right click on database and create Rule File.
You will get
Rule file editor/creator window. You will get below window and make sure you
select Dimension build fields which by default would be data load fields.
Load the
source file into your rule file to set field properties for each field.
Browse for
your source file which has your Entity dimension information.
As I have my
source file with tab delimited which is default delimiter in EAS it should all
fields in proper order. If you have your source file fields separated by
different delimiter then you can set your delimiter through Options à Data Source Properties and set your delimiter as below. (# is
delimiter set below)
Now you have
opened your source data in Rule file. We
now need to map each filed of what it should mean to essbase ( after all it’s a
software so we explain the software through our Rule file of how to create our
entity hierarchy by defining each field properties)
Before we
start defining the properties for each filed we need to tell essbase what
method we are using to build Entity dimension. As we have built Account
dimension through Rule file lets build Entity dimension ( dimension name)
manually through outline editor. Once I manully create Entity dimension ,
outline looks like below
Now set
method you chose to build your entity dimension.
Incase if
you are creating your dimension name through Rule file then we need to go to
Dimension Definition and choose Rules File and give dimension name you are
creating through rule file.Now in this case as we have already created Entity
dimension manually in outline we can see it under Dimension and double click on
Entity Dimension , it should look like Dimension Entity then it
implies to Essbase we are building Entity dimension and select Use level references for Build
method and click OK.
Set
properties for each filed, Select field1 and
Number says
the members in the field belongs to which level in your dimension.
Once you set
your field1 properties with above settings, click next and set Field2 & Field3 properties
Once you
click OK after setting properties for each filed you rule file now will look
like below.
Which mean Level0 of Entity
dimension,Level1 of entity dimension and level2
of entity dimension etc.
You should see Validation success
message when you validate your rule file.
Done you have created your rule file ,
save it and try building your entity dimension through saved rule file. You can
build your dimension through Outline à Update outline as below.(This you can see only
when you have outline opened)
Or right click on database and select
Load data
If you choose update outline method,
you will be taken to this below screen to build dimension.
If you choose Load data method, you
will get below screen
Where you browse your entity dimension
data file and rule file, Click ok to start your build process for Essbase to
start building entity dimension with set of rules being defined in your rule
file and making use of entity source data. Using Load data method advantage is
we need not explicitly save outline after the build process unlike through
Update outline method. Now after we have built the dimension through defined
rule file , we have got our entity dimension as below.
The reason we see Level2,Level1,Level0
under Entity dimension is because we have that as first line in our first which
has to be eliminated or ignore from build. (As we have not defined parent for
Level2 it by default takes Entity as the parent).
Lets edit our rule file and ignore the
line and build again.
Now that I have edited the rule file
to ignore first line from the source file by Options à Data Source Properties à Header and Number of lines to skip ,
OK and save the rule file.
And click ok. You have changed your
rule file to ignore first line to be considered for the build method. Let’s try
building the dimension again now through Outline à Update outline (for this you need to
have outline opened).
You should get above screen to confirm
that building dimension Entity is completed successfully and you need to save
your outline by clicking save button on outline editor window.
Let’s review the entity dimension
built now .
Yes exactly this is what we wanted to
build . !!!
Now we have 1 more build method we are
left with .! it’s Parent-Child Reference method.
And let’s how to build Period
dimension through Parent-Child Reference method.
To build
dimension first and foremost thing we should do is to right click on database and create Rule File.
You will get
Rule file editor/creator window. You will get below window and make sure you
select Dimension build fields which by default would be data load fields.
Load the
source file into your rule file to set field properties for each field.
You will see rule file now loaded your
source file data which is used to build period dimension.
Ignore first row by setting option of
Number of lines to skip under Options à Data Source Properties àHeader àNumber of lines to skip 1
Set Field properties now.
Let’s try creating Period dimension
through Rules File and set build method we are going to use to build period
dimension. To do this, Options à Dimension build settings à Dimension Definition
Set the dimension which we wanted to
create through Rules file (Period in this case , select Rules file and type
Period and press enter)
Once you have set the dimension which
you are creating , Come back to previous tab Dimension Build Settings and
double click on the dimension you defined on Dimension Definition tab here Period in this case and select Use parent/child references and
click OK.
Now you have defined number of lines
to skip, method used to build Period dimension and defined Period dimension to
be created through rule file.
Now we have to define each field
properties , select field1 and set the properties by Field à Properties
Click Next and set the properties for
field2, and click Ok.
Now your Rule file will look like
Validate the rule file now.
Save the rule file and build the
dimension through this rule file. Open Outline, and then Otuline --> Update outline will take you to below screen.
Click OK. You should get below screen
which says your build process completed successfully.
Let’s review our outline to see , if
Period dimension is built correctly. Don’t forget to save outline.
In Generation Reference method make sure you don’t have your
dimension name in your data source file, either in case you are creating dimension through rule file
or you already have just dimension name in outline.(In both cases you should
not map Gen1 in Rule file and should be mapping rest of the generations).
Now that we have created dimensions in our outline through
Dimension Build Rules and we have set Account & Period as Dense dimension
and rest of dimensions as Sparse.
No comments:
Post a Comment