✍ Kashif M
Trends

Saturday, 31 August 2013

OBIEE Event Tables



            An event table which is used to update the table and its columns rows frequently by pooling time.

The wizard selects the table and setting the pooling frequency to update the all the columns rows of the table.

When we selecting the table for “event table” pooling frequency the table will change to yellow color in the physical table.

Just see below.

I am using table here “s_nq_acct” and running the wizard, after completes the table will become as yellow color.

Before:











Using utilities option execute the “oracle BI Event Tables” and select the “s_nq_acct” table to run the utility.





Then check in the physical layer, the frequency sets to every 1 minute. click no here because the table has join should not delete the joins



Then we can see the table become yellow in the physical layer because the table has evented for 1 minute 




OBIEE - Replace Column or Table in Logical Table Sources utility




It is used to replace the columns or tables from physical layer to business model mapping layer.

The Replace Wizard automates the process of replacing physical columns or tables in logical table sources
by allowing the Oracle BI Administrator to select the sources from those displayed.

The wizard prompts the Oracle BI Administrator to replace columns as well as tables in both 10g and 11g.

Using single Column replace from physical to logical it affects the change in the mapping of physical table in BMM layer of logical table.


For example see below:

I am having the table name as “Absence_leave” with 12 columns.


Just I am going to map the physical column Absence_type to asondate.

Replace Column or Table in Logical Table Sources Utility
Replace Column or Table in Logical Table Sources Utility





Check the Asondate mapped with which physical column in BMM layer before replacing. Logical column of “Asondate” with the data type as “date” Mapped with asondate of physical column of absence_leave table of saac.

OBIEE - Replace Column or Table in Logical Table Sources Utility

Now use the replace column or table utility.




Click on execute to run the utility a replace wizard will get display with the options like Replace single column only – It will replace the single column in a table.

Replace the whole table – It will replace the whole table and its columns.

Replace and replace with are the two sides, so from replace to replace with – column will get replace is going to take place.

Replace is the physical column and will get affect on replace with is the BMM logical column.

OBIEE - Replace Column or Table in Logical Table Sources Utility



Select the table “Absence_leave” from both side and the column “asondate to “Absence_type”.
Click next once you selected the column in both sides.




It will ask for making changes in all the logical table of the column related to other business model where the column presents. Click next once you selected.


OBIEE - Replace Column or Table in Logical Table Sources Utility



Objects will get checked. The wizard will get confirmation from you by showing the query. What is getting replaced with what? Like that. Click finish.



OBIEE - Replace Column or Table in Logical Table Sources Utility




Now check the mapping in the BMM layer of the logical column.

So “asondate” mapped with “asondate” before using replace wizard.

After replacing “asondate” have to map with “Absence_type”. And the data type also will get change. Before for asondate the data type is date, now check after the replace.



Asondate column mapping with Absence_type and the data type changed to varchar.

Admin Tool Utilities in OBIEE 10G & OBIEE 11G




What is Utilities?

Utilities involved in maintenance and development of the rpd. Some of the lists of utilities to work on Admin level Tool are,

List of utilities in OBIEE 10G:

·Replace Column or Table in Logical Table Sources

·Oracle BI Event Tables

·Externalize Strings

·Rename Wizard

·Update Physical Layer

·Repository Documentation

·Removed Unused Physical Objects

·Aggregate Persistence Wizard

Replace Column or Table in Logical Table Sources in OBIEE
                                  Admin Tool Utilities in OBIEE 10G & OBIEE 11G

List of Utilities in OBIEE 11G:


·Replace Column or Table in Logical Table Sources

·Oracle BI Event Tables

·Externalize Strings

·Rename Wizard

·Update Physical Layer

·Repository Documentation

·Removed Unused Physical Objects

·Aggregate Persistence Wizard

·Generate Deployment File









Wednesday, 28 August 2013

OBIEE -Hardware Sizing Questionnaire OF OBIEE 11G



Sizing a Business Intelligence System involves knowing the nature and architecture of the system. These architectures have been broadly classified as Enterprise Data Warehouses, Data Marts and Extraction Engines [ETL].


Business Intelligence Architectures

1.Estimate the size of the Production Data Warehouse database

2.Estimate the rate of your database growth (i.e. 10% per year)

3.Estimate the update characteristics of the production database

Data Mart Requirements 

1.Estimate the size of the raw (source) data for the Data Mart

2.Estimate the size of the Production Data Mart database

3.Estimate the rate of your database growth (i.e. 10% per year)

4.Estimate the update characteristics of the production database

5.Estimate the rate of concurrent user base growth

ETL [Extraction, Transformation and Load] Requirements

1.Indicate what ETL Tool will be used

2.What is the source of data extraction [Oracle, DB2, IMS, SQL Server, Flat files etc.]?

3.What is the Hardware platform on which the source databases and applications run on?

4.Estimate the data extraction characteristics

5.Will extracts be required from Legacy system audit logs?

6.Will data be presented to load/cleansing programs using fixed length records, CSV or any other means?

7.Indicate the method by which data will be input into the Data Warehouse

8.Estimate the data insertion [into Data Warehouse] characteristics

Existing Infrastructure 

1.Server platform with hardware and operating system details

2.Please specfiy any ETL tool that is getting used at present with product name and it's   details

3.Existing DBMS platform

4.Security Assigned

Backup/Restore Requirements

1.Backup strategy (Enterprise level, etc.):

2.Is an on-line backup of the production system required? (Y/N)

3.Available backup window in hours:

4.Maximum restore time in hours:


OBIEE Database

1.What version of OBIEE will you be using?

2.What DB and version will be used for this workload?

3.Will unicode be used?

4.Specify the size of your OLAP database.

5.Specify the size of the source OLTP database.

6.Specify the percentage of OLTP DB that will be imported into your OLAP DB.

7.How many periods of growth should be planned for?

8.Specify the periodic oltp data growth percentage you want to plan for.

OBIEE User Profile

1.Number of licensed users.

2.Number of active users.

3.Number of inactive users.

4.Total number of users.

Tuesday, 27 August 2013

OBIEE -Time Series in OBIEE


Time Series in obiee


Time series functions present the capability to evaluate and comparing business performance by current period of time to previous time periods, allowing you to analyze data for multiple time periods.

Comparison between 10g and 11g

·         Only two series functions like AGO, TO DATE

·         In answer side we don’t have the option to implement time series functions

·         Only in RPD level have the function of time series in 10g

·         Three time series functions like AGO, TO DATE, PERIOD ROLLING

·         Here in answer side we have the function to implement time series functions

·         Both RPD and Answer side have the functions of time series in 11g

Procedures where same for AGO and TODATE function in 10g as well as 11g.



1. Ago:


Ago time series function defines the sale or revenue of business statistics according to the period over months or year. It calculates the last year amount on the same date of present year.

Syntax:

Ago (<<Measure>>, <<Level>>, <<Number of Periods>>)

·         Measure: A measure column which you to make use in this function

·         Level: On which level you want to calculate this measure. This level is selected from the Time Dimension hierarchy

·         Number of Period: A numerical value which will go for how many levels you want to go before from current time.

Description:

Ago is the function to calculate the revenue over the period, it requires the measure column, level of hierarchy, and how many periods of revenue you need.


Measure should be summed up sum function in the logical column.


Hierarchy is used here in ago function are time dimension hierarchy only.


For example: time dimension hierarchy created like
Year Quarter Month  Day


So according to the year if we need the sales or revenue means just assign the ago function like this Ago (“Revenue”, “year”, 1) this denotes the last year revenue value.

For Example Look Below screen shots:




Look the vales for the year 2005



Look the values for the year 2006



Using ago function in logical columns, we have to show the fact value for 2005 in the year of 2006 with new column.



You can use this level of hierarchy as well as year also. Here I am going to calculate for the sale before 12 months from the present month.


In the levels tab you must have the select the any column key as “Chronological Key “.


Compile and save the rpd. Login to answer side and clear cache and manage session and reload the server metadata.


And see the report. We can see the 2005 year values in 2006 year according to the month.



The same thing I'm doing in OBIEE 11g RPD side (Admin):





OBIEE 11g Answer side:




2.TO DATE:



TODATE function is used to calculate the revenue for 2 months or 3 months from the beginning time of the year. A time series aggregation function that aggregates a measure attribute from the beginning of a specified time period to the current time. For example, this function can calculate Year to Date sales.

If we need to see the revenue up to now (Current Month) @ Year. For example we are in the month of august means it calculates the revenue from starting of the year January to august.


Syntax:
ToDate (<Measure>, <Level>)
·    
        Measure: A measure column which you to make use in this function
·    
     Level: On which level you want to calculate this measure. This level is selected from the Time Dimension hierarchy


Description:

ToDate ("SH"."SALES"."AMOUNT_SOLD”, "SH"."Time Dimension"."Year”)


    With the above example 'Amount Sold' Column is the measure and 'Year' is the level which will calculate the measure from the beginning of the year to a specified time.


Example: Create a logical column and use the ToDate function to calculate the revenue according to the month wise.




Just look and compare in answer side with actual value and To date calculated value.



the above picture you can see 2 columns one is Actual sum and actual _YTD.


For January the Actual sum is -1162298.2 and actual_YTD is-1162298.2


For February the Actual sum is -1325146.4 and actual_YTD is calculated by Jan+Feb = -3572256.2 and 

same for all the other months. So it calculated by month + month.



Period rolling a new feature present in OBIEE 11g:



Period rolling is the time series function which is used to calculate the revenue over the period for months and year from starting year or month and ending period or month. Interval of calculation of the measure.

This function allows us to create a aggregated measure across a specified set of query grain period, rather than within a fixed time series grain. The common use of this function is to create a Rolling Average such '10-Week Rolling Average'


Syntax:

Period Rolling (<<Measure>>, <<Starting Period Offset>>, <<Ending Period Offset>>)


     Measure: represents the logical measure column from which you want to derive


    Starting Period Offset: identify the first period used in the rolling aggregation


    Ending Period Offset: Identify the last period used in the rolling aggregation


     For e.g.: Period Rolling ( "SH"."SALES"."AMOUNT_SOLD", -2, 0)

    With the above example I'm creating three months rolling sum of column 'Amount Sold. It includes past two months with the current month. The numerical value '-2' in the offset indicates the month Jan-12, Feb-12 if our current month is 'Mar-12'.The numerical value '0' in the offset indicates the Current Month

Note:

Period Rolling( ) function which will not the calculate the Average sum of three months for the measure. Average (AVEG ( ) function in OBIEE) computes the average of the database rows accessed at the storage grain. So If you want to calculate the 3-Month sales average for the above explained Period Rolling 

example 

we have to write the syntax like below:

3-Month Sales Average =
(Period Rolling (“SH"."SALES"."AMOUNT_SOLD", -2, 0))/3


Example:


We have the measure columns Actual _sum, using this column I used period rolling for 2 months before from the current month. So it should calculate the current month by summing of previous 2 months. For reference I created the period rolling function in answer side and calculated see the below pictures.


Period rolling (“ADSS”.”Actual_sum”,-2, 0)



See in the result tab:



Check here, the period rolling values should add the 2 months values + current month.


January – Actual sum -1162298.2      period rolling -1162298.2


February – Actual sum -1084811.6    period rolling (Added Jan+Feb)=-2247109.8


March – Actual sum -1325146.4        period rolling (summed Jan+Feb+March)=-3572256.2


April – Actual sum -1203334.7          Period rolling (Summed Feb+March+April)=-3613292.


Blog Archive

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) About Kashif (1) BI Apps Installation (1) BI Publisher (2) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (47) Informatica Online Training (1) informatica scenarios questions (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) mkashu (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (2) OBIEE 10G (8) OBIEE 11G (138) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) Power BI (5) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)