[38083] The Attribute ..... defines a measure using an obsolete method.

[38083] The Attribute '<Column Name>' defines a measure using an obsolete method.

This error occurs whenever there is an obsolete method used in BMM layer esp aggregate function like SUM, COUNT, MIN, MAX etc.

Since version 10.1.3.2, Aggregate functions in logical column definitions which use other logical columns are not supported anymore.

Solution 1:

    You can change this behaviour by setting the parameter SUPPORT_OBSOLETE_MEASURES to YES
    This parameter is not found in NQSConfig.ini file. We need to add this in the 'General' section.

Solution 2: 

  We can use Evaluate, Evaluate_aggr, Evaluate_Predicate, Evaluate_Analytic functions to achieve the respective obsolete functionality.

Data Warehosuing Hierarchies

Types of Hierarchies in Datawarehousing

Exceeded Maximum number of allowed Input Records

Recently I stumbled upon the following error: 

     Exceeded Maximum number of allowed Input Records

Reason: 

 1) OBI Server couldn't process/display the records returned from server. 


Solution: 

 In EM, Change the settings of 'Maximum Number of Rows Processed when Rendering a Table View' from 65000 to more, say 100000.

This should fix the error. 

FETCH FIRST 65001 ROWS ONLY OBIEE 11g

FETCH FIRST 65001 ROWS ONLY OBIEE 11g 

By Default, OBI renders only 65000 rows for a report. This value can be modified
to increase the performance.However, this would be a last option to optomize performance.


To change the setting for all User:

1. Log into EM
2. Go to "Business Intelligence"
3. Click on "coreapplication" ->   "Capacity Management" Tab -> "Performance" tab

4. Under section "Maximum Number of Rows Processed when Rendering a Table View"
    Lock and edit configuration
    Enter appropriate value as per your business request 

5. Restart services.

OBI 11g Column Permission Setting: PROJECT_INACCESSIBLE_COLUMN_AS_NULL

In Oracle BI 10g, If a column is hidden from a user and when the User logs in there would be an error.

To fix this error, we have to change the parameter  PROJECT_INACCESSIBLE_COLUMN_AS_NULL in NQSConfig.ini

In Oracle BI 11g,  the parameter value is set to ‘YES’ :

[SECURITY]

DEFAULT_PRIVILEGES = READ;  

PROJECT_INACCESSIBLE_COLUMN_AS_NULL = YES;  # default is no

# Note that the paths below should be relative to the instance

# config directories if specified.  e.g.,

# $(ORACLE_INSTANCE)/config/OracleBIServerComponent/<instance_name>

#IGNORE_LDAP_PWD_EXPIRY_WARNING = NO;     // default is no.

SCD Types .... Part III

Here is the final post for the last  SCD type ...

Type 6

The Type 6 method combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6).

The Customer table starts out with one record for our example Customer:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NJ	CA	01-Jan-2005	31-Dec-9999	Y

The Current_State and the Historical_State are the same. The Current_Flag attribute indicates that this is the current or most recent record for this Customer.

When Oracle company moves to New Jersey, we add a new record, as in Type 2 processing:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NJ	CA	01-Jan-2005	21-Dec-2009	N
2	ABC	Oracle	NJ	NJ	22-Dec-2009	31-Dec-9999	Y

We overwrite the Current_State information in the first record (Customer_Key = 1) with the new information, as in Type 1 processing. We create a new record to track the changes, as in Type 2 processing. And we store the history in a second State column (Historical_State), which incorporates Type 3 processing.

For example if the Customer were to relocate again, we would add another record to the Customer dimension, and we would overwrite the contents of the Current_State column:

Customer_Key	Customer_Code	Customer_Name	Current_State	Historical_State	Start_Date	End_Date	Current_Flag
1	ABC	Oracle	NY	CA	01-Jan-2005	21-Dec-2009	N
2	ABC	Oracle	NY	NJ	22-Dec-2009	03-Feb-2013	N
3	ABC	Oracle	NY	NY	04-Feb-2013	31-Dec-9999	Y

Note that, for the current record (Current_Flag = 'Y'), the Current_State and the Historical_State are always the same

SCD Part I     SCD Part 2

SCD Types.. Part II

Type III

This method tracks changes using separate columns and preserves limited history. The Type II preserves unlimited history as it's limited to the number of columns designated for storing historical data. The original table structure in Type I and Type II is the same but Type III adds additional columns. In the following example, an additional column has been added to the to record the Customer's original state - only the previous history is stored.

Customer_Key	Customer_Code	Customer_Name	Original_Customer_State	Effective_Date	Current_Customer_State
1	ABC	Oracle	CA	22-Dec-2009	NJ

Cons: This record contains a column for the original state and current state—cannot track the changes if the Customer relocates a second time. One variation of this is to create the field Previous_Customer_State instead of Original_Customer_State which would track only the most recent historical change.

Type IV

The Type 4 method is usually referred to as using "history tables", where one table keeps the current data, and an additional table is used to keep a record of some or all changes.

For the above example the original table name is Customer and the history table is Customer_History.

Customer
Customer_key	Customer_Code	Customer_Name	Customer_State
1	ABC	Oracle	NJ

Customer_History
Customer_key	Customer_Code	Customer_Name	Customer_State	Create_Date
1	ABC	Oracle	CA	22-Dec-2009

Part III here ...

Part I here...

SCD Types... Part 1

Type 0

The Type 0 method is passive. It manages dimensional changes and no action is performed. Values remain as they were at the time the dimension record was first inserted

The most common types are I, II, and III.

Type I

This methodology overwrites old with new data, and therefore does not track historical data. Its common uses are for misspelled names

Example of a Customer table:

Customer_Key	Customer_Code	Customer_Name	Customer_State
1	ABC	Oracle	CA

In the above example, Customer_Code is the natural key and Customer_Key is a surrogate key.

If the Customer relocates the headquarters to New Jersey. The updated table would overwrite this record:

Customer_Key	Customer_Code	Customer_Name	Customer_State
1	ABC	Oracle	NJ

Cons:

·         No history in the data warehouse.

·         If you have calculated an aggregate table summarizing facts by state, it will need to be recalculated when the Customer_State is changed.

Pros: It's easy to maintain.

Type II

This method tracks historical data by creating multiple records for a given natural key in the dimensional tables with separate surrogate keys and/or different version numbers. Unlimited history is preserved for each insert.

For example, if the Customer relocates to New Jersey the version numbers will be incremented sequentially:

Customer_Key	Customer_Code	Customer_Name	Customer_State	Version.
1	ABC	Oracle	CA	0
2	ABC	Oracle	NJ	1

Another method is to add 'effective date' columns.

Customer_Key	Customer_Code	Customer_Name	Customer_State	Start_Date	End_Date
1	ABC	Oracle	CA	01-Jan-2005	21-Dec-2009
2	ABC	Oracle	NJ	22-Dec-2009

The null End_Date in row two indicates the current record version.

Pros: An aggregate table summarizing facts by state continues to reflect the historical state, i.e. the state the Customer was in at the time of the transaction; no update is needed.

Cons: If there are retrospective changes made to the contents of the dimension, or if new attributes are added to the dimension (for example a Customer_Rep column) which have different effective dates from those already defined, then this can result in the existing transactions needing to be updated to reflect the new situation. This can be an expensive database operation, so Type 2 SCDs are not a good choice if the dimensional model is subject to change.

Part 2 here .....

Part 3 here.....