Change Visible Row in OBIEE.

Hello Everyone, 

Some time while running the report you will get error “Exceeded configured maximum number of allowed output ROW” In OBIEE Default ResultRowLimit are 65,000. If you are pulling more record then you have to increase the Row size.

Solution: Increase row size in Instanceconfigu.Xml

File Path:

C:\Rakesh\OBIEE11g\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\instanceconfig.xml R

<Views>

<Pivot>

<MaxCells>6500000</MaxCells>

<MaxVisibleColumns>100</MaxVisibleColumns>

<MaxVisiblePages>100</MaxVisiblePages>

<MaxVisibleRows>65000</MaxVisibleRows>

<MaxVisibleSections>25</MaxVisibleSections>

<DefaultRowsDisplayed>500</DefaultRowsDisplayed>

<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>

<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><DisableAutoPreview>false</DisableAutoPreview>

</Pivot>

<Table>

<MaxCells>6500000</MaxCells>

<MaxVisiblePages>100</MaxVisiblePages>

<MaxVisibleRows>100000</MaxVisibleRows>

<MaxVisibleSections>25</MaxVisibleSections>

<DefaultRowsDisplayed>500</DefaultRowsDisplayed>

<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><DefaultRowsDisplayedInDelivery>75</DefaultRowsDisplayedInDelivery>

<!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager--><DefaultRowsDisplayedInDownload>65000</DefaultRowsDisplayedInDownload>

</Table>

</View>

-Rakesh Patil

-rakeshpatil002@gmail.com

Configure Session Time

Today will discuss about the session configure time in OBIEE

1st of all Configuration done in instanceconfig.xml file Below is the path for this file. 

C:OBIEE/Instance/Instance1/config/OracleBIPresentationServicesComponent/coreapplication_obipsn

Open the Instanceconfig.xml file
Please enter the below entries.

<ConnectionExprieMinute>120</ConnectionExprieMinute>

<ClientSessionExprieMinute>120</ClientSessionExprieMinute

<logOncExprieMinutes>120</logOnExprieMinute>

Save the File Restart the BI Server

I have entered 120 min in code which means system will allow me to be idle for 120 min.

Sawguidstate Error

Dear All Today will discuss about the most famous error in the OBIEE. i.e. is Sawguidstate Error.

Error Type : Sawguidstate Error in 11g.

Invalid item \\?\

C:\OCB\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache\rakesh-pc\sawguidstate.atr.

Error Description  :  Due to Sawguidstate file is corrupted..

Solution: For this you have to change the folder name that is CATALOG. Change it into Catlog2.

C:\OCB\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog2\

Restart the BI Server.

Hope this will help for you. 

-Rakesh Patil

-rakeshpatil002@gmail.com

Cannot log into Oracle BI Analytics with correct username and password

Some time you can log into both Enterprise Manager (http:/hostname:port/em) and Console (http:/hostname:port/em/console) on the WebLogic server with the same login credentials that fail on OBIEE (http:/hostname:port/analytic).

Oracle BI Presentation service is not getting started

Some time you will see the OBI Presentation service will is not starting due to some unexpected error and it will show the 20% down and 80% Up in oracle EM.

1st of all you need check the error in Oracle EM (Enterprise Manager) log file then work accordingly.For me few file was corrupted and below is the details.

Error : Invalid sawguidstate.atr and sawguidstate File

I was getting this error because both file was corrupted in below root.

C:\OCB\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache\rakesh-pc

Solution:

You must copy the sawguidstate.atr and sawguidstate file from the path

C:\OCB\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache\dadvmh0229

to

C:\OCB\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\mktgcache\rakesh-pc

The service can start now.

Real Time Scenarios in OBIEE

Dragged the 2 tables from physical layer to BMM layer (along with Joins), which act like fact table and which act like dimension table?

Ans:-Primary key—–Dimension

            Foreign key——-Fact

Same as (c) but without  Joins?

Ans:-Both act like fact tables in BMM layer

I have configured the repository(3 layers) and saved.I have generated some reports and saved. If I change the presentation catalog name then it is showing  that you don’t have permissions to access this subject area in answers.How can you fix this?

Go to Advanced tab(In answers) Change the subject area name and set XML it will  automatically  display the subject are

You recently changed the columns of your presentation catalog as your manager wants to enforce naming standards for all customer-facing applications. What happens to all the dashboard requests written prior to this change? Do they function properly or do they appear broken?”If yes, they will function”, How does they work? “If not, reports appear broken”, what can you do to fix this? Give examples?

If alias table is available for presentation table then all the reports work fine.

Replace repository with oracle enterprise manager

Go to BI > Coreapplication  > Deployement > repository >

Note : check for local configuration make sure no buddy is changing any thing

>Lock and edit configuration: Msg Successfully  completed

>Brows RPD file

> change the catalog file location

>Apply

>Release configuration

>Activate Changes  – Completed success fully

>Restart the OBIEE Server (click on Restart to Recent Changes)

>Restart (Msg : Are Sure want to Restart the OBI Server)

OBIEE 10g RPD upgrade to OBIEE 11g

Please go through Step :  

Oracle >Middleware >Oracle_BI>Bin

Note : Allow to Upgraded 10g RDF and Catalog file to 11g

Click ON UA > Den Oracle Fusion Middleware Upgrade Wizard will open

>Next (Volgende)>

Type of Upgraded

1. Upgrade Oracle BI RPD and Presentation Catlog
2. Upgrade Oracle BI Scheduler Schema
3. Upgrade Oracle BI Publisher Repository
4. Upgrade Oracle BI Publisher Scheduler Schema
5. Verify Instance

Select type 1

>Select what you need to Upgrad Repository or Catalog

>For example Selected Repository

>Browse the 10g rpd > Administrator User Name and Password

>Enter a password which will be use for 11g

>Weblogic Admin Server Connection : HOST:localhost Port :7001(Default) Username:Weblogice Password : rakesh$123

OBIEE 11.1.1.6.0 – Add to Favorites New enhancements

he new enhancements for the version 11.1.1.6.0- Favorites Menu, global header links, BI client installer, and sorting are covered under this topic…

Favorites, which allows you to bookmark your favorite Catalog objects, such as analyses, dashboards, and reports.

Below is the Screen Shot.

Check below screen shot  so you will understand.

selected analysis or object is displayed with a gold star symbol on the Home page.

Object in the Physical Layer

Object of Business Model and Mapping

Stop BI Service : No prompting for UserId and Password : OBIEE 11g

Thanks to Nachiket Mehendale for wonderfully notes on Stop BI, service

Link : http://obiee1000.blogspot.in/2012/03/stop-bi-service-no-prompting-for-userid.html

I am sharing here with you.

We wanted this process to happen automatically.The way suggested by Oracle Support is –

Change in the
..\instances\instance1\bifoundation\OracleBIApplication\coreapplication\StartStopServices.xml

<condition property=”should.get.credentials”>
<or>
<isset property=”should.create.credentials”/>
<isset property=”is.stop.all”/>
</or>

to

<condition property=”should.get.credentials”>
<or>
<isset property=”should.create.credentials”/>
<isset property=”should.create.credentials”/>
</or>
</condition>

Save the changes and test.
This time we were not asked to type user id and password.

Multi User Development of Repositories (MUD)

MUD is a feature of the BI EE admin tool where in multiple users can work on the repository at the same time

Oracle BI repository development environment is not set up for multiple users. A more efficient development environment would permit developers to modify a repository simultaneously and then check in changes

Steps :

1. Copy the Master RPD in Shared file.
2. Extract the RPD to Local Machine
3. Make local change in the local machine rpd
4. Merge local changes to master RPD
5. Resolve conflicts
6. Save and Check the Consistency
7. Publish changes

Creating Projects

•      File > Open > Offline

•      Manage > Projects>New Project

•      Provide a Name to the project

•      Select rpd objects to be part of the project and save

Located at the Dir :

ORACLE_INSTANCE/bifoundation/OracleBIApplication/coreapplication/setup

MUD Menu Options

Compare with Original. Compares the working extracted local repository to the original extracted repository. When this option is selected, the Compare repositories dialog opens and lists all the changes made to the working extracted repository since you checked out the projects.

•      Merge Local Changes. Locks the master repository on the network multiuser directory to allow you to check in your changes.

•      Publish to Network. After you successfully merge your changes, the master repository opens locally and the Publish to Network submenu item is available. When you select this option, the lock is removed, the repository is published, and the repository closes.

•      Undo Merge Local Changes. Rolls back any previously merged local changes, and leaves the repository checked out so that you can make additional changes and then merge your local changes again. This option is only available after you have already merged local changes.

•      Discard Local Changes. Any time after check out and before check in, you can discard your changes. When you select this option, the working repository closes without giving you an opportunity to save your work.

Below go through Below Sample file screen shot

Using a Presentation Variable in a Static Text View

August 23, 2012 Leave a comment

Using a Presentation Variable in a Static Text View

Create an analysis that uses the VarSalesRep presentation variable in a Static Text view and in a Filter.
A Static Text view adds static text in the results.

1.	Create an analysis by selecting the following columns: Customers > Cust Regions > C50 Region Customers > Cust Regions > C52 Country Name Sales Person > E1 Sales Rep Name Base Facts > 1 Revenue
2 .	Add ascending column sorts in this sequence: C50 Region, C52 Country Name, and E1 Sales Rep Nameas shown below: The Criteria tabbed page should look like this after adding the sorts:
3 .	Select the Results tabbed page.
4 .	Select New View > Other Views > Static Text.
5 .	Move the Static Text view above the Table view. Click the Edit View icon on the Static Text view. The Static Text editor appears.
6 .	In the Static Text editor, reference the VarSalesRep variable. Enter the following syntax in the Static Text pane: This analysis is for the Sales Rep @{VarSalesRep}. The syntax for referencing a Presentation variable is as follows: @{variables.variablename}[format]{defaultvalue} or @{scope.variables['variablename']} Where: variablename is the name of the presentation or request variable format (optional) is a format mask dependent upon the data type of the variable, for example #, ##0, MM/DD/YY hh:mm:ss. (Note that the format is not applied to the default value.) default value (optional) is a constant or variable reference, indicating a value to be used if the variable referenced by variablename is not populated scope identifies the qualifiers for the variable. You need to specify the scope when a variable is used at multiple levels (analyses, dashboard pages, and dashboards) and you want to access a specific value. (If you do not specify the scope, then the order of precedence is analyses, dashboard pages, and dashboards. Examples: @{variables.MyFavoriteRegion}{EASTERN REGION} or @{dashboard.variables['MyFavoriteRegion']} Observe that your entry is previewed below the Static Text text box. Click Done.
7 .	Save your analysis as Sales Reps by Region and Country. Your analysis should look like this:
8 .	a. Select the Criteria tabbed page. b. In the Filters pane, click the Create a Filter icon and select “Sales Person “.”E1 Sales Rep Name “.
9 .	a. In the New Filter dialog box, click Add More Options and select Presentation Variable. b. In the Variable Expr field, enter the variable name, VarSalesRep. Notice that you can also specify a default for the variable, but in this case the default is driven by the variable prompt, which is set to default to “Angela Richards”. c. Click OK. The filter should look like this:
10 .	Select the Results tabbed page. Because the variable dashboard prompt has not been run, the VarSalesRep presentation variable has not been populated with a value. Because of this, no results from the analysis meet the filter requirement. Save the analysis.
11 .	Add the analysis Sales Reps by Region and Country and the newly created variable prompt to the Customer Detail dashboard.
12 .	Open the dashboard Customer Detail from the Regional Revenue folder, then click Page Options > Edit Dashboard.
13 .	Add a new column next to Column 1, and then navigate to the Sales Reps by Region and Country analysis and drag it to the new column.
14 .	Navigate to SalesRep Variable Prompt in the catalog pane, and drag the prompt above the Sales Reps by Region and Country analysis. Save the dashboard and run it. The Dashboard view looks like this:
15 .	Click the Collapse icon for the first column to minimize it. The dashboard runs and the variable dashboard prompt is preset to the default value, Angela Richards, which in turn appears in the Static Text view as expected and is used to filter the embedded analysis results. The value of a presentation variable is populated by the variable prompt. That is, each time you select a value in the variable prompt, the value of the presentation variable is set to that value.
	Click the drop-down list for the dashboard prompt, and select Chris Jones. Click Apply. The dashboard displays the presentation variable as Chris Jones. This concludes the topic of Presentation Variables and Filters.

Facts & Dimensions

Facts & Dimensions form the heart of a data warehouse. Facts are the metrics that business users would use for making business decisions. Generally, facts are mere numbers. The facts cannot be used without their dimensions.

Dimension data is denormalized where as fact table contains normalized data
Dimension table contains many columns where as fact table contains less columns only 2 in fact
The data in the dimension tables are less compared to the data in the fact tables
The data in the dimension table is static and descriptive in nature where as the fact table contains numeric and will change regularly
Dimension tables generally called as lookup or reference table as well. facts tables are the key performance indicators of the business

Dimension Table features	Fact Table features
1. It provides the context /descriptive information for a fact table measurements.	1. It provides measurement of an enterprise.
2. Provides entry points to data.	2. Measurement is the amount determined by observation.
3. Structure of Dimension – Surrogate key , one or more other fields that compose the natural key (nk) and set of Attributes.	3. Structure of Fact Table – foreign key (fk), Degenerated Dimension and Measurements.
4. Size of Dimension Table is smaller than Fact Table.	4. Size of Fact Table is larger than Dimension Table.
5. In a schema more number of dimensions are presented than Fact Table.	5. In a schema less number of Fact Tables observed compared to Dimension Tables.
6. Surrogate Key is used to prevent the primary key (pk) violation(store historical data).	6. Compose of Degenerate Dimension fields act as Primary Key.
7. Values of fields are in numeric and text representation.	7. Values of the fields always in numeric or integer form.

Data Modelling – Key

FOREIGN KEY:

Foreign key is a referential constraint between two tables, A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when the column or columns in another table reference the column or columns that hold the primary key value for one table. This column becomes a foreign key in the second table.

For example, the Sales.SalesOrderHeader table in the AdventureWorks2008R2 database has a link to the Sales.SalesPerson table because there is a logical relationship between sales orders and salespeople. The SalesPersonID column in theSalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in theSalesOrderHeader table is the foreign key to the SalesPerson table.

Surrogate

It is a substitute primary key – for when you don’t have a real one. An ID column populated via a sequence is known as a surrogate key.

The use of SKs in Datawarehouse environments is not only simplicity of star design or performance.

You often need to populate your dimensions with extra records not found in the source table, such as “UNKNOWN” or “NOT APPLIES” records. If you don’t use SKs, What PK value would you use for this extra records?

How can you be sure that the values you choose won’t collide with future records from the source table?

Also, taking your dimensional schemas away from possible source key changes is a good idea. Of course creating and maintaining SKs adds complexity and is extra work for ETL processes.

Natural

If the table has a true primary key – it is known as a “natural key”

Business

Unique key that use the business users to identify uniquely an entity. The business key is a meaningful/business code based generally on the concatenation of attributes value of an entity instance.

OBIEE 11g installation

NOTE: OBIEE 11g (11.1.1.3) BI Administration Tool does not work under Windows 64-bit OS. It is expected that the next release of OBIEE 11g (11.1.1.4) will support 64-bit Operating Systems for the OBI Client applications. But Presentation Services work with the present release. So, think twice before you take decision to install 11g(11.1.1.3) on 64-bit OS.
So, if you have decided to install on 64-bit machine…follow these steps:

This installation post is going to be series of 3 parts. This is the first of the 3 posts.

Part-1 –> Download software and configuring RCU (Repository Creation Utility)
Part-2–> Installing OBIEE 11g
Part-3–> Configuration

Part-1:
Download OBIEE 11g(11.1.1.3.0) software and RCU(for 32 bits)from here. As there is no RCU for 64bits, download the 32bits one and it works)

also download RCU

Once you download all the files, extract them and keep them under one directory(Keeping RCU in the same directory is not mandatory, just for convinience).

Before starting the installation keep your have got database up and running. RCU will create two schemas in the installation process…

First step of installaton is running RCU (Repository Creation Utility), go to C:\Software\OBIEE11gR1\bishiphome\rcuHome\BIN and double click on rcu.bat file.

Welcome page of the installation wizard will open …click Next. Select Create

Click Next

Enter all the required information, be sure that the database user has got sysdba previlages…Click Next

Click OK

Enter the Prefix (or leave the default one DEV) and select the components install…

Click OK and enter the passwords for all the users (2 users) that created in the following screen…

Click Next

Click OK

Click OK

Click Create

Click Close..we are done with RCU

Once the RCU install : Go to OBIEE11g

OBIEE11g (11.1.1.6) Installation Steps

Download all the OBIEE11g 64 bit files from Oracle site and unzip all the files and click on “setup.exe” on disk 1 to start the installation process.

Click on next

Click on Skip software updates and click next

Click on simple install and click next

Click next

Create a OBIEE11g home directory and ensure there are no spaces on the folder name. Locate the folder and click next.

Provide the Administration user name and password.

Click next

Provide the connect string and metadata schema name (DEV_BIPLATFORM)

Click next

Uncheck the security update and click next.

Click on Install

Once the configuration is 100% complete, click on next.

Once the installation is complete click finish. The system will open up the analytics page.

Login with the weblogic user ID and Password and check the sample reports.