Partitions and Fragments (Performance improvement technique)

Partitioning:
Splitting of one large table into small portions is called as partitioning.

Fragments:
The resultant of the partition is called as fragment.

Say, we have a huge D1_CUSTOMERS2 table with a lot of data. We would like to partition this table based of the customer names. All the customers whose name start from A-N will be in one partition and rest of the customers whose name start with O-Z will fall under 2nd partition.

For that, we will be creating 2 different tables from this huge table.
1) CREATE TABLE D1_CUST_PART1 AS SELECT * FROM D1_CUSTOMERS2 WHERE SUBSTR(NAME,1,1) <N;

2) CREATE TABLE D1_CUST_PART2 AS SELECT * FROM D1_CUSTOMERS2 WHERE SUBSTR(NAME,1,1) >=N;

Now, Open the repository in online mode-->Physical layer-->connection pool---> right click and select "Import Metadata" --> Select these 2 tables D1_CUST_PART1 ,D1_CUST_PART2.

Now select these 3 tables and highlight them :D1_CUST_PART1 ,D1_CUST_PART2,D1_ORDER2 and right click-->Phisical diagram ---> Join these tables on Newkey=Custkey as shown below:

Check in changes and save it.

Now we want to work on BMM layer.
Previously, all the customers data was comming from Customers dimension table but nw it has to come from partitioned tables. thus need need to map all columns of both partition tables to customers table in BMM layer by dragging and dropping them from physical layer to BMM layer for every column.
Now that we have mapped partitioned tables, we do not need D1_CUSTOMERS table in BMM layer anymore, so we will disable this table from logical table source.

Double click the logical source table D1_CUSTOMERS2 and go to the General tab and click the check box of disabled, if this table is needed for future use then just un-check the box:

Managing the keys:
We can only have one key per dimension table. as a result of  mapping 2 partition tables to BMM layer, 2 keys are added to the keys section of Customers table as shown below:

We need to delete these 2 newly created keys and keep the old one.

Until now we have just imported and mapped the partitioned tables. Now we need to fragment them.
to do this go to D1_CUST_PART1 logical table source-->Content tab --->Fragmentation content -->Edit formula button as shown below:

we edit the formula saying name<N.
Also select the check box "This source should be combined with other sources at this level". So that we can combine results of this table with other part2 table when entire data is fetched. Also check "Select distinct values" check box and define the content level as customers.

Similarly, set Fragmentation Content for D1_CUST_PART2, select the check box "This source should be combined with other sources at this level". So that we can combine results of this table with other part1 table when entire data is fetched. Also check "Select distinct values" check box and define the content level as customers..

Now, just verify once the log level is 2 and delete cache (Manage -->cache--->right click and select purge).


Check in changes and save.

Now, login to Analytics with Administrator user and reload server metadata. Create a new report -->Select Name, Revenue-->Add a filter on Name where < C -->Results tab -->Administration-->under Session management click manage sessions --> scroll down and click on view log of our report --> this will show that detailed physical level query. since the data we requested is in D1_CUST_PART1 table, the query is fetching data from the same table.

If we have name filter <V then it would have fetched the data from D1_CUST_PART2 table.

If we put no filter at all then the data will be fetched from Union of both the tables.

This is how we can make the report to run from a particular partition instead of having to go through the entire table. This improves the performance a lot.

Aggregates

The data in the transactional database is very detailed.

 For e.g. For a year 2016, the revenue is stored for every day of a month and quarter as shown below. So to retrieve the revenue for the year 2016, we may require 365 seconds (Number of days in a year, considering 1 record fetch takes 1 second). If we want to sum them all then we may take another 365 seconds. So, the total time taken will be 730 seconds =12 mins. This will cause the performance to go down if the database takes 12 mins to fetch the result.

So to avoid this, we will aggregate the data and store in a table in OLAP. Something like this:

If we query this OLAP table then number of seconds to retrieve data for year 2016 will be 4 seconds as there as 4 rows, to add them up it may take another 4 seconds, it means the total of 8 seconds.
Thus, we see that to achieve the same result in different databases we take different amount of time. Transactional database takes 12 mins and OLAP takes 8 seconds to retrieve the total revenue of 2016.
Thus, storing an aggregate table in OLAP makes it faster than a detailed table in transactional database.
Hence, the aggregates are used to summarize the data for quick data retrieval since data has been pre-calculated.

For the purpose of demonstration, create 3 aggregate tables in OLAP from Orders, customers and calendar table.The data in Orders, customers and calendar table should be aggregated to Month level.
Now, import these small aggregated tables into connection pool in repository. Make sure to open repository in online mode.

Following are the aggregate tables:

Join these tables:
In physical layer, ctrl + select all 4 tables and right click physical diagram.

D1_ORDERS_AGG1.typecode =D1_PRODUCT_TYPE.typekey
D1_ORDERS_AGG1.salesrep=D1.SALESREP.repkey
D1_ORDERS_AGG1.monthcode= D1.MONTHS.perkey

After the joins the tables will look like this:

Check in the changes. save.

Now, the columns in these 4 tables should be mapped to corresponding columns in BMM layer.
This is how we map them:
First we drag and drop "District" column from D1_SALESREP table to Customers table in BMM. As soon as we drag and drop this column on to the "District" column of CUSTOMER table, new source table "D1_SALESREP" is added to sources folder in BMM layer as shown below.

Similarly, drag and drop Region column,Salesrep column.
Also, Drag and drop monthcode column in physical layer to monthcode column in BMM layer, this will add D1_MONTHS table in sources folder of Periods table in BMM layer.

Multiple key columns problem:
By mapping physical columns to logical columns, we ended up having two key columns for each table in BMM layer which clearly is an error. thus we must select each table in BMM , go to the keys tab and select only one column as a key and delete the rest of the columns as shown below:

Defining content level:
Now, to perform any aggregate operation on these newly mapped columns, we need to define content level for the BMM layer dimension tables(Customers, Periods, Products) to its lowest level. if we do not do this then the data will be generated from detailed tables from transactional database instead of aggregated high level tables in OLAP.

Thus, to avoid this we need to set the content level.
For e.g. the lowest level of information that D1_Month table has is "Month", so we will map Periods logical dimension table to content level as Month as shown below:

Similarly, set content level for Product dimension as itemtype, for Customers dimension as salesrep.
Also set content level for fact table Orders as follows:

Please note that Content level set for individual dimension table(when we double click and open a dimension table in BMM layer and got to the content tab) should be the same as content level set for all dimension tables in Fact table (when we double click the fact table in BMM layer and got to the content tab).
For e.g. in content level for Product dimension is "Specific Desc" column then in fact table content also it should be "Specific Desc" only for Product dimension.No other column can be specified, or else it will throw an error while check in changes.

Check in the changes and save.

Setting log level:

Make sure that log level is set to "2" to see the detailed SQL generated behind the report by Administrator user. to set this level do the following:
Manage-->Identity --> select Administrator user --->User tab --> Log level=2 -->Ok.
save.


Go to analytics --> reload server metadata.-->New--->analysis --->sales ---> select Year and revenue --> Go to results tab ---> administration --> under session management -->manage sessions --> scroll down and view log ---> detailed SQL can be seen.

it is clear that the data was fetched from high level aggregated OLAP tables instead of original transactional database tables. This is because we are fetching year and its corresponding revenue, so we are asking it to fetch total revenue of each year. Since our OLAP data is aggregated to the lowest level month, until we select Year, Quarter, month and revenue , we will get the data from high level aggregated OLAP tables.
But if we select "day in a month" column to be displayed along with its revenue, we know that the day level data is very detailed and OLAP tables do not have it, thus the sql generated will fetch this level of detail from the transcactional database tables instead of OLAP tables.
Thus the SQL that gets generated will show original table names instead of OLAP tables as follows:

Delivers

Oracle BI delivers is used to automate the process of running the reports and sending its success/failure report automatically to the concerned people through email, text etc.
Thus, basically a dashboard user will see an alert on his analytics home page. it is similar to the notifications we get on our mobile.

To create Oracle BI delivers we need to do following:
1) Configuring scheduling tables
2) Delivery devices
3) Delivery profiles
4) Create Agent(Previously IBOT)
5) Create alerts
6) Alert section Dashboard object
7) Job Manager

Let us see each one in detail:
1) Configuring scheduling tables

• In OBIEE 11g installation, RCU(Repository Creation Utility) creates the scheduler tables. and they are by default stored in DEV_BIPLATFORM schema.
• following are scheduler tables:

1. S_NQ_JOB :- It stores the list of job to run. The columns in this table store JOB_ID, AGENT_NAME that executed the job, SCRIPT_TYPE is IBOT, USER_ID who executed the report, LAST_RUNTIME_TS,START_TIME etc.
2. S_NQ_JOB_PARAM :- It stores the parameters required to run the job. The columns in this table store JOB_ID, RELATIVE_ORDER, JOB_PARAM,DELETE FLG etc.
3. S_NQ_INSTANCE :- It stores information regarding time and date of execution of report. The columns of this table store JOB_ID, INSTANCE_ID, STATUS, BEGIN, END, EXIT_FLAG,DELETE_FLAG ,ERR_MSG_FLAG etc.
4. S_NQ_ERR_MSG:- It store the error msgs to be delivered to the end user, also the information whether the message delivered to end user or not. thus, basically this table is used for debugging. The columns of this table store JOB_ID, INSTANCE_ID, RELATIVE_ORDER, ERROR_MSG_TEXT, DELETE_FLAG etc.

2) Delivery devices

• Following devices can be configured to receive messages.
• Web browser
• Email
• Cell phone
• Wireless PDA
• Login to Analytics using Administrator credentials ---> Click on Administartor --> My account ---> Delivery options tab --> select device as Email from drop down --> click on green plus add button--> Name: OBIEEDev ,Category: Email , Device Type: HTML_Email , Address/Number: gauri.odi@gmail.com -->ok.

• Similarly, we can create a mobile device called "OBIEEMobile"

3) Delivery profiles

• It shows us the user roles like Dev, admin etc to whom the results will be delivered.
• This is how we create a delivery profile.
• Login to Analytics using Administrator credentials ---> Click on Administartor --> My account ---> Delivery options tab --> Delivery Profiles section -->click on green plus add button--> Name:"Office Profile" --> there will be list of all the devices we just created and the device priorities. we need to select these priorities, say "OBIEEDev" gets high priority in Office time 9am to 5pm and "OBIEEMobile" gets high priority after office hours. it simply means that While the user is in office,he will receive the notifications on email, when the user is at home he will receive the notifications on Mobile.--->ok.
• Similarly we can create Out of office profile in which OBIEEMobile gets high priority.

4) Create Agent

• Useful to schedule a report and generate the alert.
• Create sample report with Year,Region, Revenue and name it as Agent report.
• Now go to new--> Agent-->schedule tab --> Frequency: Once/Never/Weekly etc, Start: time and date, re-run agent every: 1 min, Until: End time and date.--> Delivery content tab--> Subject :Welcome to OBIEE Delivers , Content: Browse to the Agent report, Deliver results directly or as an attachment.-->Recepients tab--> Add administrator --->Destinations tab---> select where wuld you like to see the notifications / alerts. --> Actions tab-->Here we can specify actions to be done whenever agents completes.-->Save as "First Agent"
• Now on home page at designated, we can see an alert with a Bell symbol.
• If we click this alert, we will get list of alerts and we can choose from them and see the reports generated.

6) Alert section Dashboard object

• Open OBIEE Dashboard--> Edit--> Drag and drop Alert section --> Our alert will be shown in the dashboard once we save and run it.

 7) Job Manager

• It is web based component useful to monitor status of the agent.
• Say for example we were expecting an email to be sent to every developer on 7pm, but we did not receive it, then to debug the problem we would go to the Job manager to check what is the issue.
• Go to start--> Oracle Business Intelligence --> Job Manager --> A job manager window appears. click on File menu--> Open scheduler Information --> Give administrator password --> ok.--> In a filter view, all jobs will be displayed.--> We can go to By User section, click on Administrator and see all the agents started by administrator user along with all the details.--> we can select particular job , right click and select view job. This will display all the details about that job.

How to develop and agent based on a condition:

• Create one sample report with a condition on revenue as Revenue>3 million , save and call it "Agent report 2".
• Create another report with no measurable column, add filter on Newkey, where operator: Based on results of another analysis, browse to "Agent report 2". Save this report as "Agent report 3".
• Now create an agent : New-->Agent -->go to condition tab--> Use a condition radio button-->create--> Analysis (Browse) --> select Agent Report 2 --> True if row count is: select the row count you expect to be generated from "Agent Report 2 ".--> fill up all other information in other tabs including start and end time and save the agent.
• The agent will send an alter at designated time and we will see alert on homepage.
• Note that if the condition in "Agent report 2" evaluates to false then the report will not run and the alert will never be generated.

Dashboard Objects

• The dashboard objects are used to organize the reports.

Following are dashboard objects.
1) Column
2) Section
3) Alert Section
4) Action Link
5) Action Link Menu
6) Link or Image
7) Embedded content
8) Text
9) Folder

How to reach these objects?
Go to Dashboards --> OBIEE Dashoards ---> Right hand side Edit Dashboard ---> on left hand side we see dashboard objects.

1) Column Dashboard Object

• Create new dashboard page by clicking on green + sign. name it "New Page".
• Drag and drop column object on blank panel.
• We can see a big area created where other objects can be dropped and organised.
• The column is the largest Dashboard object.
• If we drag and drop reports on the column object, it will create sections. Sections is the second largest object in dashboard.

2) Section Dashboard Object

• Section is 2nd largest object in a dashboard.
• if we drag and drop report on section, we can arrange them either horizontally or vertically.
• Permissions can also be set for the sections by clicking on edit section and then clicking Permissions... . Here we can see accounts/user roles with the access they have.

3) Link or image

• This is used to navigate from one report to another.
• Drag and drop Link or image object onto one of the section--> then edit the link or image object -->provide caption--> browse and select the report to which you want to navigate--> select target as current window --> ok --> save and run.
• after running we will see "Report name" as a blue clickable link.  if we click on this report name we will be navigated to that particular report on the same dashboard page.
• On the bottom, a small "Return" link will allow us to go back to the original dashboard.
• Similarly, we can add an image when we edit the Link and image object. 
• simply, in an image text field type fmap:images/clock.jpg
• Make sure you have an image in OBIEE installation folder under images sub folder. 

4) Embedded Content

• It is useful to display a webpage in a dashboard.
• Drag and drop the Embedded content object onto the section-->Edit -->specify URL that you want to navigate to--> Save and run.
• You will see the webpage displayed on a dashboard.

5) Text Dashboard Object

• This object is used to display some comments about the report on the dashboard.
• Drag and drop the Text Dashboard Object on to a Column object --> edit ---> type in the text. we can also add html tags.--> save and run and you will see a Text message on the dashboard.

6) Folder Dashboard Object

• It is useful to present the saved content on the dashboard.
• Drag and drop the Folder Dashboard Object on any section--> Edit ---> select any sub-folder from shared folders catalog.--> save and run.
• This will show the folders and its contents on the dashboard itself. we can expand the sub folders and see the list of all reports saved under it. If we click on any saved report, we will be navigated to that particular report.
• This is the simplest method of displaying any report at run-time.