Configuration of Data Sync for BICS

 Oracle Business Intelligence Cloud Service (BICS) with Data Sync

     1. Business Intelligence Cloud Service Data Sync Overview :

The Business Intelligence Cloud Service Data Sync supports the load of on-premises data residing in one or more relational or comma-separated value file sources into the schema provisioned on the Oracle Business Intelligence Cloud Service.

2. Installing the Business Intelligence Cloud Service Data Sync :

To install the Data Sync, you must meet the requirements and prerequisites, then unzip and run the application.
Download Data Sync from:
http://www.oracle.com/technetwork/middleware/bicloud/downloads/index.html

2.1  Prerequisites, Supported Databases, and JDBC Requirements :

       Before installing, you must have the 64 bit machine and Java 1.7 or later version of Java Developer Kit (JDK).
 Install jdk to a directory with no spaces in its name.
 
    Note: Java Developer Kit is required for Business Intelligence Cloud Service Data Sync. 
    The equivalent version of the Java Runtime Environment (JRE) is insufficient to support
    the installation.
 
2.2  Setting Up the Software :

To set up the software, copy the BICSDataSync.Zip file to an installation directory with no spaces in its name, and unzip the files.

2.2.1 Setting the Java Home:
Depending on your operating system, edit either the config.bat or config.sh file, modifying the line that sets the JAVA_HOME. Replace the @JAVA_HOME with the directory where the JDK is installed.

     2.2.2 Execute datasyncclient.bat or datasynccclient.sh :

Execute datasyncclient.bat or datasynccclient.sh depending on the operating system to launch the first time Data Sync Configuration Wizard and click next.

2.2.3 Configure the Repository and JAVA DB :

In Environment Configuration, select configure a new environment and click next to configure the repository and Java DB for Data Sync to store its metadata.

2.2.4 Specify Repository Name and click Next :

2.2.5 Choose Password for Datasync Login and click Next:

2.2.6 Configuration is complete, click finish to close the configuration wizard :

2.2.7 BICS Datasync server gets started :

BICS Datasync server gets started, To get started, please enter the password set for the repository. Skip the create project option. Project can be created from Datasync client. 

  Enter the same password given while Installing Datasync in step 2.2.5

  After successful Login, following screen will appear.

3. Creating Project and Connections :

3.1.1 Creating Projects :

a. To Create a new Project, select File --> Project, In the project creation wizard, specify the New Project Name and click OK.

Please Note: A project is a list of tables/files to upload in a single session. You may need multiple projects to populate a BI Cloud database – either if loading data from multiple sources or if you need to schedule different tables at different times

Give the relevant Project Name.

3.2.2 Creating Connections :

a. Once Project is created, we need to setup Source & Target connections, Source connections can be RDMBS or Files, BICS Datasync supports connections to popular RDMBS like MYSQL, ORACLE, SQL SERVER, DB2 etc.,

1. Click on Connections

2. Select the TARGET Source

3. Enter BICS Username

4. Enter BICS Password

5. URL should be same as the BICS URL excluding /analytics tag at the end.

6. To Test the connection, click on Test Connection button, a pop up window appears with the test result.

7. Click OK to Close the Test result window

8. Save the changes to the TARGET connection, if the connection is successfully tested.

b. Following are the steps for setting up the Source RDBMS connection (e.g. Oracle source).

1. In the Connection Tab, click on New to create a new Source DB connection

2. Enter the Name of the connection, Connection Type, Table owner, Username, Password, Service name, Host & Port number of the Source.

3. Click on Test Connection to check the status of the Test result

4. After reviewing the test results, Click OK to close the test result window

5. Once the connection is tested successfully, save the connection details.

3.2.3 Importing Metadata from Relational, File & Target into Project.

a. Once Connections for source and target is succesfully configured, then we can to create the tasks/ETL.

1. To setup a new datasysnc task, Select the Project Tab

2. In the Relational Data Tab, click on Data from Table to import the metadata of the source table(s)

3. In Import Table into Datasync window, select the appropriate data sources.

4. Search/Select the list of tables displayed and Import tables.

b. Review Target Metadata.

Note: By default BICS Datasync adds additional 3 columns in the Target table namely:

1. DSYS_BATCH_ID: Tracks the batch that is trying to upload the data. Each table load streams multiple batches (currently of 3,000 rows), with each batch assigned a unique number.

2. DSYS_INSTANCE_ID: Tracks the Data Sync installation instance ID

3. DSYS_PROCESS_ID:  Tracks the process ID assigned to a certain run of the job.

4. Creating Jobs & Loading On-Premise data to BICS Schema :

4.1.1 Creating & Executing Jobs :

a. A job is an instance of running a particular project and can be associated with a schedule to regularly run it. Clicking on the Jobs button in the top menu bar opens the Jobs window. To create a new executable job, click New & Enter a Job Name.

b.  To Execute the Job

1. Click on Run Job button.

2. Go to the Current Jobs.

3. All Tasks would be queued.

c. You can also view the Job History by selecting the History tab to review the status of the load.

d. Finally we can check the data that is been loaded in Oracle Cloud Schema by logging on to BICS Myservices and browsing through the schema objects as shown below, notice the extra columns created by BICS Datasync.

 5. Conclusion :

The Business Intelligence Cloud Service Data Sync supports the load of on-premises data residing in one or more relational or comma-separated value file sources into the schema provisioned on the Oracle Business Intelligence Cloud Service.

Thanks for visiting..Keep Learning..:)

Master detail view in OBIEE

Master detail linking of views enables you to establish a relationship between two or more views such that one view, called the master view, drives data changes in one or more other views, called detail views.

In our example graph is master report and table view is detailed report.

What is master detail ?

Master Views: A master view is one which, primary interaction value in the column properties have set to Send Master-Detail Events using a channel name.

Detail Views: Explores the whole data as based on the master event.

The following types of views can be master views: Graph, Funnel graph, Gauge, Map, Pivot table & Table.
The following types of views can be detail views: Graph, Funnel graph, Gauge, Table & Pivot table

To create master detail event we can use following steps:

1.I have created analysis as shown in screen shot.

2. Select Revenue metrics interaction property, Select primary selection as Send Master-Detail Events.
    Specify channel name here we used master_detail 

3. Create master and detail reports refer screen shot A and B.

A. Master Report

B. Detail Report 

4. Now select detail reports properties. Select checkbox in front of Master-Detail. Specify Event Channels. You can     add multiple event channels separating by coma.

5. This is the output of reports on dashboard page. To see the master detail event click anywhere on the bar graph. You will see same changes will be reflected into table.

6. Here we have selected Audio. Now we are able to see two sub types of Audio product.

7. If you want see sub types in more details, again click on the sub types. You will see all detailed information related to products in table. Refer bellow image.

8. In this way we have implemented master detail view in OBIEE.

Note:

1.Master View:
a. Master column cannot be displayed on the prompt section or on the section slider in the master view. It must be displayed in the body of the view. In our example, the revenue column is the master view and it is present in the body of the view.

2.Detail View:
a. Can listen to master-detail events from multiple master views
b. Can be in the same analysis as the master view or in a different analysis
c. Cannot act as a master to another view

Creating users in OBIEE 11g

User authentication is the important part in OBIEE security. For any user we need to assign some kind of security levels in the sense of read, write and modify application roles.

The default application roles are grouped into broad categories of functional usage: administrator (BIAdministrator), author (BIAuthor), and consumer (BIConsumer). 

Following are the default application roles provided by the OBIEE system:

1. BIAdministrator Role

              The BIAdministrator role grants administrative permissions necessary to configure and manage the Oracle Business Intelligence installation. Any member of the BIAdministrators group is explicitly granted this role and implicitly granted the BIAuthor and BIConsumer roles

2. BIAuthor Role

              The BIAuthor role grants permissions necessary to create and edit content for other users to use, or to consume. Any member of the BIAuthors group is explicitly granted this role and implicitly granted the BIConsumer role.

3. BIConsumer Role

              The BIConsumer role grants permissions necessary to use, or to consume, content created by other users.

4. BISystem Role

              The BISystem role grants the permissions necessary to impersonate other users. This role is required by Oracle Business Intelligence system components for inter-component communication.

5. Authenticated Role

               Oracle Business Intelligence uses the authenticated application role to grant permissions implicitly derived by the role and group hierarchy of which the Authenticated role is a member. The Authenticated role is a member of the BIConsumer role by default and, as such, all Authenticated role members are granted the permissions of the BIConsumer role implicitly.

You can see all these roles in em using following path:

Coreapplication --->Configure and Manage Application roles--->Application Roles

See screen shot for application roles:

Steps for creating new user and assigning application roles:

1. Login to the console using weblogic user credentials.

2. Select Security Realms from Services as marked in the screen shot.

3. Select Myrealms as shown in screenshot.

4. Select User and Groups, you will find total no of system users. For creating new user you have to select New option as marked in the screen shot.

5. Enter user details like user name, description and password and click OK.

6. New user is created admin with default authentication role. To assign new roles to user. Click on admin user. 

7. Assign new role and save changes i.e BIAdministrators and Administrators.

8. New user is created with administrator application role. To Verify user login to analytics with admin user name and it's password. Before login to the system restart services to get changes.

9. User is logged in successfully and we have observed assigned application roles are available for admin user.

In this way we learned to create new user. Keep visiting for new posts.

Thanks...!!

Upgrading the Oracle BI Repository and Catalog

To upgrade the Sample Sales repository and presentation catalog, perform the following steps by using the Upgrade Assistant. Please note that the WebLogic Administration Server and the Managed Server in the 11g environment must remain running during the upgrade process.

1. Navigate to the ORACLE_HOME\bin directory of the Oracle Fusion Middleware installation. In this example, the directory path is D:\bi\Oracle_BI1\bin.

2. Double-click ua.bat to open Upgrade Assistant in the graphical user interface mode. Refer screen shot:

3. On the Welcome screen, click Next.

4. On the Specify Operation screen, select Upgrade Oracle BI RPD and Presentation Catalog and click Next.

5. On the Specify Source Details screen, select Upgrade Repository (RPD).

6. Click Browse next to the RPD File field.

7. Browse to the location of the 10g version of the Sample Sales repository and click Open. In this example, the path is D:\SampleSales\samplesales.rpd.

 8. Enter the Administrator User Name and Administrator Password for the 10g samplesales repository. In this    example, the username and password are both Administrator.

9. Enter new password for rpd which doesn't match with the previous 10g rpd password.

10. Select Upgrade Catalog.

11. Browse to the location of the 10g version of the Sample Sales presentation catalog and click Open. In this example  the path is D:\SampleSales\Sample_Sales_V1.3_Webcat\samplesales.

12. The path to the Sample Sales presentation catalog is added to the Catalog Directory field. The path to  the deliveries directory is automatically added to the Catalog Deliveries Directory field.

13.Enter the weblogic admin server details. Port number, user name and password. 

14. Click Next to continue. On the Examining Components screen, wait until you see a succeeded message. During the examination process, Upgrade Assistant checks whether the source directory exists, the source directory is readable, and contains a file for upgrade.

15. Click Next to continue. On the Upgrade Summary screen, review the summary of the upgrade to be performed.

16. Click Next to continue. When the upgrade completes the status changes to succeeded.

17. Click Next to continue. n the Upgrade Success screen, review information related to the upgrade task and  click Close.

             In this way have upgraded rpd and catalog to 11g. Comment if it is helpful and keep visiting  for new posts.

Thanks....!!