4.3 ORACLE BI FOR MS OFFICE

                                   ORACLE BI FOR MS OFFICE

            Key features of Oracle Business Intelligence for Microsoft PowerPoint:
                  1. View live, refreshable data from the Oracle Business Intelligence Server.
                      a. Insert BI tables, pivot tables and graphs into PowerPoint or Excel as
                         refreshable, editable objects.

                    b. Apply PowerPoint or excel formatting to BI data; formats are retained
                       when you refresh.

              2. Copy and paste BI analyses from Oracle BI Answers or Oracle BI Interactive
                   Dashboards to PowerPoint or excel . The data, metadata, and view layout are
                    copied.

            3. Secure BI Data from Oracle BI objects in PowerPoint or excel so that users must
                  log in to view the data. Secured objects can be refreshed.

Installation

            1. login to Analytics
            2. Under Get Started -> Click on Download BI Desktop Tools ->Click on Oracle
                   BI for MS Office

            3. Click on Run the executable that you have downloaded and follow the wizard to
                 complete the installation

Configuration

         1. Open Microsoft Power point or Microsoft Excel
        2. In the Menu bar click on OracleBIàPreference
        3. In Preferences window àselect Connections tabàselect New
       4. In the New Connection window, fill the following fields
              Server Name: Give any name (e.g. RRITEC)
            BI Office Server: Enter the URL for the BI Office Server (e.g. RRITEC). You can
            enter “localhost” if the BI Office Server is installed on your local machine.
             Application Name: bioffice (deault)
             Port: 7001

      5. Click on Test connection. It should show the following screen, if not check the
           connection details and correct them.

Note: This does not test the connection between the BI Office Server and
Presentation Services.

        6. Click on Oracle BI MenuàClick Login to login to Presentation Services using
            the appropriate username(weblogic) and password(RRitec123)

       7. You will get the error “Login failed. Please check the username, password
               and Oracle BI Office server availability” It is because in the bioffice.xml
          (C:\OBI11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tm
           p\_WL_user\bioffice_11.1.1\hsq62b\war\WEB-INF\) SawBaseURL is pointing to
                9704 port. Change it to 7001 and then save the file. Restart the all services from
                EM (http://rritec:7001/em) and try login again, it should work.
     8. Once you are logged in, you will see the BI Catalog on the right pane of Microsoft
           Power point.

ODBC DRIVERS of MS EXCEL IN WINDOWS 7

1. Start  || Control Panel || Administrative Tools  || Right Click on On Data Sources (ODBC) || Select the Properties

2 . Change properties as shown below 

3 . Now we can see Excel Drivers  :)

0.8 LEVAL BASED MEASURES

                                      0.8 LEVAL BASED MEASURES
                       1. If a measure column is calculated on one particular level then
                           it is called as level based measure.
                       2. Level based measures are useful to calculate share or percentages.

               Step 1: Creating Level Based Measure

                       1. Open RPD in online mode ->right click on sales fact tables ->new object
                            ->logical column ->total revenue ->column source ->select derived from
                            existing column using expression

                          click on edit expression -> double click
                          on dollars column -> click on ok -> again ok -> drag and drop total revenue
                          onto customer dimension -> customer total level -> drag and drop total
                          revenue into presentation table .

         




     Step 2: Understanding or testing Level Based Measure

                       1. Go to reporting end and develop a report with region ,dollars , total revenue.
                       2. Click on results. Notice that total revenue and dollars column displaying same
                            value for all the rows that mean it is calculating at the level of total customers.

               Step 3: Using Level Based Measure in Share Calculations 
  
                      1. Right click on sales fact table -> New object -> logical column -> name it as
                          share -> click on column source tab -> select derived from existing column
                          using an expression -> Edit expression -> double click on dollars column ->
                          double click on (/) divided by -> double click on total revenue * 100 -> click
                           on ok -> again ok.
                     2. Drag and drop share into sales presentation table -> check in changes.

               Step 4: Testing
                        1. Reload server metadata. In above report (developed in step2 ) add one more
                            column share -> observe the result.

07 creating dimensional hierarchies

                                                    07 creating dimensional hierarchies
              Hierarchies are 3 three types.
                                   1. Level based hierarchies.
                                   2. Parent child hierarchies (11g new features)
                                   3. Unbalanced hierarchies (11g new features)
              Level based hierarchies:
                          One to many relationship among columns is called as level based
                           hierarchies.
                                     Eg:
                                        1. Time hierarchies
                                                 Year -> quarter -> month -> day.
                                        2. Customer hierarchies
                                                  Region -> district -> sales rep -> customer
                                        3. Product hierarchies
                                                  Type -> subtype -> generic -> specific.
                   Step 1: Creating time hierarchy Levels
                           1. Right click on period dimension table -> create logical
                                dimension -> dimension with level based hierarchy -> expand periods
                                dimension -> rename period detail as day level.
                          2. Right click on day level -> new object -> parent level -> type: month
                               - click on ok.
                          3. Right click on month level -> new object -> parent level -> quarter
                               click on ok.
                          4. Right click on quarter -> new object -> parent level -> year -> ok.

     











              

                Step 2: Calculating quarter column
                        1. Right click on period dimension table -> new object -> logical column
                             -> name it as quarter.
                        2. Click on columns source tab ->select derived from existing
                             column using an expression.
                        3. Click on edit expression .
                        4. Develop below expression.
                                    Case when “sales”, ”periods” , “month in year” < 4 then 1.
                                     When “sales”, “periods”, “month in year” < 7 then 2
                                      When “sales” ,”periods” ,”month in year” < 10 then 3
                                       Else
                                        4
                                        End.
                         5. Click on ok ->again ok.
                 Step 3: Mapping columns to hierarchy Levels
                         1. From day level drag and drop year column onto year level.
                         2. From periods dimension table drag and drop quarter onto
                           quarter level.
                        3. From day level drag and drop month code onto month level.
                        4. In day level delete all columns except yyyymmdd .
                 Step 4: Defining Logical Keys
                           Each and every level must contains a key column,it is required
                            to identify unique values in that level
                         1. Right click on year level -> year column ->new
                            logical level key -> click on ok.
                         2. Right click on quarter column ->new logical level key -> click on ok.
                         3. Right click on month code -> new logical level key -> click on ok.
                         4. Click on save.
                 Step 5: Defining Content level
                         1. Expand periods
                          2. Double click on D1_calendar logical source -> Click on content ->
                              set logical level as day level
                         3. Click on ok.
                         4. Expand sales -> double click on D1_orders2 logical table source
                            -> Click on content ->logical level as day level -> ok.
                          5. Drag and drop quarter column into presentation layer
                              periods presentation table

                 Step 6: Testing
                         1. Load RPD into oracle BI server.
                         2. Develop a report with year , dollars column.
                         3. Click on year ->column properties -> then interaction
                             -> select primary interaction as drill ->: click on ok -> click on results
                             -> click on 1998 -> Click On Quarter -> click on month.
                    Note : Similarly create product & customer hierarchy with below
                               levels as shown in screen shot and test by developing reports .
                      Customer hierarchies
                                    Region -> district -> sales rep -> customer
                      Product hierarchies
                                    Type -> subtype -> generic -> specific.

           













          Hierarchies are useful to
                            1. Drill down.
                            2. Drill up.
                            3. Drill across (or) drill by.
                            4. Level based measures.
                            5. Aggregate navigation.
                            6. Time series measures.
                       1. Drill down:
                                1. Navigating from high level to low level is called as drilldown.
                                2. Above testing process we can consider as
                                    drill down.(year->quarter->month->day)
                       2. Drill up:
                                   1. Navigating from low level to high level is called as                                                                                          drillup(Day->Month->Quarter->Year)
                          Process:
                                 1. Open RPD in online mode -> double click on day level
                                      -> preferred drill path -> click on add -> select month level
                                      -> click on ok.
                                  2. Check in changes & reload server metadata.
                                  3. Develop a report with yymmdd , dollars.
                                  4. Click on yymmdd -> column properties -> interaction
                                       -> under value ->primary interaction as drill -> click on ok.
                                    5. In results click on any one no. and observe month data.
                         3. Drill across:
                                   1. Navigating from one hierarchy to another hierarchy is called
                                      as drill across.
                                                   Eg: Day -> Region (drill across)
                       Process:
                              1. Go to Rpd -> double click on day level remove month
                                  column -> click on add -> select region -> click on select -> check in.
                               2. Reload server metadata.
                              3. Develop a report with yymmdd , dollars.
                              4. Click on yymmdd -> column properties -> interaction
                                   ->primary interaction as drill -> click on ok -> click on results.
                              5. Click on any value & observe the output.
                              4. Making two or more columns as target:
                                     1. Go to Rpd -> double click on day level -> keys ->select
                                          primary key -> edit -> select two columns(Day and
                                           YYYYMMDD) -> enable use for display -> click on ok
                                           again ok -> check in -> reload server metadata
                                             -> test your work.

06 calculations

                                                        06 calculations
           In OBIEE we can do calculations in 3 ways.
              1. Creating calculation based on logical columns.
              2. Creating calculation based on physical columns.
              3. Creating calculations based on by using calculation wizard.

         0.6.1 Creating calculation based on logical columns
               1. Open RPD in online mode.
               2. Right click on BMM layer sales fact table ->New object -> logical column.
               3. Name it as cuts.
               4. Click on column source tab.
               5. Select radio button Derived from existing columns using an expression.
               6. Click on edit expression
                           Select category -> logical tables.
                           Logical table -> sales
                           Columns -> double click on unitord column ->type Minus(-) ->
                            double click on unitship columns.
               7. Click on ok ->again ok

 







             8. Drag and drop into presentation layer sales presentation table.

          0.6.2 Creating calculation based on physical columns
                      1. Expand sales logical table
                      2. Double click on D1_orders2 LTS.
                      3. Click on add new column.
                      4. Name it as cutsp.
                      5. Click on ok.
                      6. Click on edit expression.
                      7. Double click on unitord -> type minus (-) -> double click on unitship.
                      8. Click on ok-> again ok.

              9. Double click on cutsp ->aggregation tab ->sum
              10. Click on ok .
              11. Drag and drop onto presentation layer sales table.

          0.6.3 calculation by using calculation wizard
              1. Right click on unitord -> click on calculation wizard ->next.
              2. Select unitship ->next.
              3. Disable percentage change.
              4. Rename it as Cutsw.

              5. Click on next -> click on Finish.
              6. Drag and drop into presentation layer sales table.
              7. Check in changes.
              8. Reload server metadata.

           Testing :
                            Develop a report with year, month code, unitord, unitship, cuts,
                             cutsp ,cutsw & observethe output.
          Notes:
                     1. In logical calculations pre aggregation will take place
                     2. In Physical calculations post aggregation will take place
                     3. As a best practice use logical columns in calculations
                     4. Calculation Wizard is useful to handle NULL values and 
                          DIVIDE WITH ZEROerror
                     5. Calculation wizard is useful to calculate percentages easily.

05 Adding multiple sources

                                                   05  Adding multiple sources
                     1. Add physical tables to an LTS or LT.
                     2. If data is not duplicated then that physical table can add to LTS or LT
                        (As a best practice  add to LTS).
                     3. If data is duplicated then we need to add physical table to LT. In this
                         case we need to define content logical level. (It is used in aggregate
                          tables and partition tables chapters )
                          Note: To add Physical table to LTS, the adding physical table 
                          and existing  physical table of LTS must have direct join.

               Process:
               Step 1 : Import below tables into physical layer.
                        1. D1_PRICELIST.
                        2. D1_PROD_DIET_TYPES.
                        3. D1_PROD_SUBTYPE.
                        4. D1_PRODUCT_TYPE.
                        5. D1_SUPPLIERS.

             Step2 : Create joins as per below.
                       1. D1_products.suppliercode = D1_suppliers.suppliercode.
                       2. D1_products.productkey = D1_pricelist.productkey.
                       3. D1_products.dietcode = D1_productype.dietcode.
                       4. D1_products.subtypecode = D1_productsubtype.subtypecode.
                       5. D1_product_subtype.typecode = D1_productype.typecode.

             Step 3 : Mapping multiple sources to an LTS.
                       1. Drag and drop D1_PROD_SUBTYPE physical table onto D1_Product LTS.
                       2. Similarly drag and drop
                           D1_PRICELIST,D1_PROD_DIET_TYPES,D1_PROD_SUBTYPE,
                           D1_PRODUCT_TYPE,D1_SUPPLIERS.

             Step 4 :
                                 Drag and drop newly added 5 columns onto products 
                                  presentation table.

04 TESTING RPD

                                          0.4 Hands on 4 testing RPD
                         0.4.1 Checking repository for consistency

It is useful to check entire RPD, mainly it will check below points
           1. All logical columns are mapped directly or indirectly to one or more physical
               columns.
           2. All logical dimension tables have a logical key.
           3. All logical tables have a logical join relationship to another logical table.
           4. There are at least two logical tables in the business model: a logical fact table and a logical                         dimension table. Both can map to the same physical table.
           5. There are no circular logical join relationships.
           6. A presentation catalog exists for the business model
Consistency Check Manager
          1. Displays consistency check messages
                a. Errors: Must be fixed to make the repository consistent
                 b.Warnings: Condition that may or may not be an error
                    We must make sure there are no errors and if possible debug warnings.
                Note: Till 11.1.1.5 version it used to display best practices in 11.1.1.6 it is internally                                    taken care
          Process:
               Do by using any one way

        















Servers in OBIEE 11G:
1. Oracle BI server:
                  1. It is a main server.
                  2. It controls all data security rules.
                  3. RPD will be loaded in this server .
2. Oracle BI presentation server:
                  1. It is used to develop reports, dashboards, key performance indicator (KPI),
                      score cards etc…
3. Oracle BI Java host:
                  1. It is used to display charts (graphs.)
4. Oracle BI scheduler:
                  1. It is used to schedule reports (Agents).
5. Oracle BI cluster controller:
                  1. It is useful to integrate ‘n’ number of servers.
6. Web logic:
                  1. It is web server useful to access reports and dashboards globally.
                      Above 1 to 5 servers are integrate in OPMNCTL (oracle process management network                             control).

               0.4.2 Loading repository
                          In loading repository into oracle BI server we have below six steps.
              1. Close RPD.
              2. Start the web logic server alone or all OBI severs.
              3. Configure RPD in EM (Enterprise manager).
              4. Start or restart oracle BI server.
              5. Confirm RPD loaded or not by checking log file.
              6. Confirm RPD loaded or not by developing report.

Step 1 : Close RPD
           1. It is not mandatory in OBIEE 11g, where as in 10g it is mandatory.
           2. In 10g RPD opened and if we load RPD in Oracle BI Server then we can not
               open RPD in online mode with write permissions (it opens only read only mode)
Process:
            Go to file menu of Administration tool ->click on close.
Step 2: Start the web logic server alone or All OBI severs
            Go to path BI11g/user_projects/domains/bifoundation_domain/startweblogic.cmd

OR





         Start ->all programs ->oracle business intelligence ->start BI services.
                         Provide user name : web logic.
                         Password:RRitec123.(RRitec is optional)
Note: After installation first time if we start then only it will ask user name and password
Step 3 : Configure RPD in EM (Enterprise manager)
           Open internet explorer type: http://localhost:7001/em
                                                      Provide user name: web logic
                                                      Pass word: RRitec123.
            1. Click on login.
            2. Click on business intelligence ->core application ->Deployment>repository
               ->lock and edit configuration ->close ->click on repository file browse->Select
               RRITEC.rpd ->open ->Repository password:RRitec123->Confirm
               password:RRitec123->Click on apply.
          3. Click on activate changes

Step 4: Start or Restart oracle BI server
          Method 1: Using EM
             1. Click on capacity management ->availability
             2. Select BI servers->Click on restart selected ->yes -> after sometime close.
           Method 2: Using Command prompt
            1. Start -> Run -> cmd
            2. Type cd E:/BI11g/instances/instances1/bin àenter.
            3. To see all the commands of opmnctl type opmnctl ->Enter.
            4. To stop the oracle BI server type
              opmnctl stopproc ias-component=coreapplication_obis1
           5. To start the oracle bi server replace stopproc in above command with startproc

Step 5 :Confirm RPD loaded or not by checking log file
           1. Go to below path
        BI11g\instances\instance1\diagnostics\logs\OracleBIServerComponent\coreapplication_obis1
           2. Open Nq server.log file observe our rpd name in the bottom of the notepad
Step 6 :Confirm RPD loaded or not by developing a report
         1. Open Intrenet Explorer
         2. Type URL http://localhost:7001/analytics press enter
             User id: web logic.
             Password: RRitec123.
        3. Click on new ->analysis -> select subject area supplier sales
        4. From subject area pane double click on year, dollars columns
        5. Click on results.
Note1:
        1. Whenever we click in activate changes in backend RPD Xerox copy will be created in repository                 folder. this process is called as version control of RPD
        2. In Nqsconfig.ini file RPD name will be modified.
        3. we can find Nqsconfig.ini file in below path
           E: /bi11g/instances/instance1/config/oracle BI server component/coreapplication_obis1.

                      0.4.3 Log level
       1. Oracle BI Server provides a facility for logging query activity at the individual user level
       2. Log levels are available from 0 to 7.
       3. In production we use ‘0’ i.e., No log level .This process is useful to improve the performance of                   environment by reducing log creation work of oracle BI server.
       4. In development and testing environments we use log level ‘2’.
       5. The query log file is named Nq query.log and is located in the directory
           Bi11g/instances/instance1/diagnostics/logs/oracle-bi servercomponent/coreapplication                     obis1.
                                               Log level 1 vs log level 2

Log level 1	Log level 2
1. User name, session id & required id for each query.	1. All items for level 1,plus those mentioned below.
2. Sql for the request using business model names.	2. Repository name, business model name, presentation catalog name.
3. Query status (success, failure, termination or timeout.)	3. Sql for the request using physical data source syntax.Queries issued against the cache.
4. Elapsed times for query compilation, execution, query cache & backend database processing.	4. Number of rows returned from physical database
	5. Number of rows returned to client or report

03 PRESENTATION LAYER

                                     03 PRESENTATION LAYER

          1. Presentation layer is the only layer visible to end users (Report Developer or clients).
          2. In presentation layer we have 4 types of objects.
                         1. Subject area.
                         2. Presentation table.
                         3. Presentation columns.
                         4. Hierarchy object.
          3. Subject area in 11g is called as presentation catalog in 10g.
          4. Hierarchy object is newly introduced in 11g
          5. Single Subject Area must be populated with content from a single business model; cannot span                      business models.
          6. Multiple Subject Area can reference the same business model.
Step 1: Creating Subject Area
         1. Right click on presentation layer -><- new subject area -><- name it as supplier sales-><-                  click on  ok.
         2. From sales business model select all the tables drag and drop on to supplier sales..
         3. Similarly create one more subject area with the name of supplier sales DM. drag and drop                          periods,products and salesfacts tables.

Step 2: Reorder Columns
            · Double click on sales presentation tables.
            · Click on columns.
            · Select .dollars ,unitord, unitship
            · Click on up arrow mark and make sure these are in first,second and third positions respectively
Step 3 : Reorder Tables
            · Double click on .supplier sales subject area
            · Select sales fact table and make sure it is available in bottom.
Step 4 : Create Nested folders (or) nested tables
           • Prefix the name of the presentation folder to be nested with a hyphen and a space or type -> in                       description of table
           • Place it after the folder in which it nests.
Process:
          · Double click on supplier sales subject area.
          · Click on presentation tables tab.
          · Click on add (+).
          · Name it as keys and in the description type -> stores key columns and click on ok.
          · Again click on add (+).
          · Name it as - measures (prefix the table name with hyphen and a space)
          · Click on ok
          · Make sure these two tables are available under sales fact tables .

02 BMM LAYER

                                                              02 BMM LAYER



      · It is also called as logical layer (or) business layer.
      · In this layer we will convert Data base technical terminology into business
        terminology
             Eg: ACTLEXTND column into Dollars.
     · BMM layer contains ‘n’ number of business models.
     · Each business model called as subject area or data mart.
     · In development of BMM layer we have below steps.
               1. Creating business model
               2. Creating logical tables and logical columns
               3. Creating logical joins.
              4. Renaming logical tables and logical columns
              5. Defining measures.
Step 1: Creating Business Model
            1. Right click in BMM layer -><- new business model
            2. Name it as suppliersales -><- click on ok

Step 2: Creating logical tables and Logical columns
          · Right click on suppliersales business model -><- new object -><- logical table.
          · Name it as periods.
          · Click on ok.
          · Drag and drop D1_calendar2 physical table onto periods.
       Similarly create below three tables and map it
                Customers -><- D1_customers2
                Sales -><- D1_orders2
                Products -><- D1_products

Note: Wherever you drag and drop physical table then automatically logical column will
be created. More we will discuss in hands on 6 calculations
Step 3: Creating logical joins
        1. Logical join is useful to define driving table.
        2. Logical join is useful to define Inner Join, Left Outer Join,Right Outer Join and Full Outer                               Join
       3. Logical join is useful to define cardinality. more used cardinality is one to many and never                              used cardinality is Many to Many
      4. Logical join is useful to identify dimension tables and fact tables.
      5. Logical join allows Oracle BI Server to make the best decision about the exact physical SQL                       to generate based on the logical query path

Process:
        · Right click on BMM layer-><- business model diagram -><- whole diagram.
        · Click on new join.
       ·  Drag and drop sales onto periods
       ·  Click on ok.
       ·  Similarly create below joins
                   supplier sales to customers
                   supplier sales to products.
      · Close business model diagram.

Step 5: Renaming columns:
      · Go to tools -><- Utilities -><-Rename Wizard -><-Execute -><- select Sales Business Model -><-              Click on Add hierarchy -><- Click on next -><- again next.
      · select All text lower case -><- add
      · Select First letter of each word capital -><- add
      · Select Change each occurrence of ‘-‘into a space -><- add.
      · Click on next and click on finish.
      · Save it and click on No.

Step 6: Defining measures
       Each and every measure should associated with one aggregation rule
       Table Source Data :

Deptno	Empno	Sal
10	101	1000
10	102	2000
20	103	500
20	104	1000

If we are not mentioning the sal as measure column the output is like this.

Deptno	Sal
10	1000
20	2000
20	500
20	1000

  

If we mention the Sal column as Measure (Sal is measure and is associated with
aggregate rule sum) then output is

Deptno	Sal
10	3000
20	1500

Process:
In the BMM layer expand sales fact table double click on dollars column-><- select
aggregation tab -><-select aggregation rule as sum -><- Click on ok.
Note1: one business model may map to multiple data bases.
Note2: one logical Table may map to multiple LTS
Note3: One logical tables source may map to multiple physical tables
Note4: one logical column may map to ‘n’ number of physical columns.