All About OBIEE and DAC

Informatica integration service issue with DAC

If you are getting below error and/or DAC is not able to test integration services please read on ..

ERROR:

The command: [pingserver] is deprecated. Please use the command [pingservice] in the future.
WARNING: Lost server connection.

ERROR: Cannot connect to Integration Service [Integration_Service:4001].

I have see that May times DAC acts so weirdly.
DAC is able to test all the physical data source and repository services but only thing he has porblem with is Informatica Integration services.So how its not able to test that.

DAC and informatica services runs file and even able to connect from command prompt.

 

This may have happen because of multiple reasons

like

1> New Oracle client installation or ORACL_HOME path variable changes

2> Informatica related changes like hot fix etc

3> Any changes in the DAC java libraries and or Oracle client java libraries

If you saw above error try to copy the ojdbc6.jarfrom your <Oracle Client Home\jdbc\lib> to <DAC Home> \lib and

Edit the batch or shell file which start the server  (startserver.bat or startserver.sh) and change the max memory to 1024 if it not already set.
start %JAVAW% -server -Xms256m -Xmx1024m -cp %DACCLASSPATH% -Duser.dir=%DAC_HOME% com.siebel.etl.net.QServer

Once done bounce the DAC server.

By doing these two things most probably this will fix your issue.

Thanks for reading..

OBIEE interview questions

Below are list of questions you may face during interview. This is part of overall effort to put the interview questions for OBIEE, DAC, Informatica , Oracle Pl\sql and DW Concepts

Thing to remember, it also depends on weather interview you are facing is for consulting or full time ? in requirement OBIEE is good to have skill or mandatory ? As question and depth of it vary based on that.

One suggestion Always be prepared. Its better to be over prepared. I have seen that people with lots of experience sometimes does not perform well. And only reason for that is lack of preparation. Working day to day job and attending interview are two different things.

So lets get started…
I will keep updated this blog to add answers and more questions as well. So stop by often

Below are obiee questions
OBIEE
– Explain the architecture of OBIEE including client components.
– What is the difference between Siebel and OBIEE over all and architecture wise? ( If your resume says Siebel experience that you are likely to encounter this question.
– What are level based measures. Explain it with practical example.
– What is connection pool ? What kind of connection pool you were using ?
– What was the max connection in you connection pool ? How did you come up with that number ?
– Some report is performing very poorly and takes about 15 mins to show results. What would you do to make it faster ? from where you would start ?
–Let say you want to create a new column in BMM layer. That can be derived from an existing column in BMM/physical layer. How would you create that column ?
– How would the aggregation looks if in above – you base new column on BMM column vs physical columns.
– What are the various utilities provided by OBIEE ? Which one did you use most in the past ?
– Time series related questions
– How was the security set up in your last project ?
– In case of ldap authentication, how user session was getting populated ?
– What is umdl ? How can you generate udml file from Repository ?
– Explain : How can you change the connection pool using udml command line utility.
– If you have aggregate tables – Then how/where you specify that report should go to Aggregate table and not detail table.
– Have you implement the data level security ? What are various ways you can implement that ?
– What is system session variable ? When does it get initialized ?
– What is static and dynamic variables ? When does these variable get refresh ?
– If moved catalog from one environment to another. Which service(s) you need to bounce ?
– Can you change the connection pool in online mode to be effective ?
– If you change the column names in presentation layer. Would existing reports referring these columns work or break ? why ?
– Can you implement snowflake schema in OBIEE ? If yes then how ? if not then why ?
– Which configuration file you have changed in OBIEE ? What have you change in them and why ?

If you are attending interview at big brand names then I will also encourage you to visit glassdoor.com as they have very specific questions based on company

unmatched RepositoryStamp issue in DAC

Sometime dac throws below error when accessing dac from command line.

ERROR:
com.siebel.etl.net.DACCommandLine main
INFO: Illegal message received, with unmatched RepositoryStamp! Ignoring it.

Here is what you can do to resolve this error
1- DAC server and client repository stamp are not in same. ( This happens when we move dac repository from different client ) .
2- Login to the client from where repository migration happened.
3- Check Help–> login Details- and copy Repository Stamp

4- go to server and locate the dac.properties file and edit this file.
change RepositoryStampVal=
5- Bource the dac server.

OBIEE Certification

Since winter of 2008 Oracle Certification team was considering coming up with OBIEE certification.They took survey as well on this aspect.

Click here to go through Oracle Blog to know the details where they were considering to put an exam for OBIEE.

Please see below details about the exam needed for OBIEE certification. It surely helps to get interview calls as.Plus you will have a point in resume to establish the customer confidence.

Certification surely helps if you are in consulting field I am not saying that it does not help to  take full time employment, it surely does. But benefits of obtaining the certification for consultants is more- at least that is how I am seeing it…

Exam Number: 1Z0-526
Associated Certifications: Oracle Business Intelligence Foundation 10 Certified Implementation Specialist
Exam Price: US$ 125
Duration: 105 minutes
Number of Questions: 70
Passing Score: 61%

Note: Oracle says that passing Score can change at any time. Please Click Here to know details on OBIEE certification

OBIEE Best Practices / Design Principles for 3 layers of Repository

1) Physical Layer Best Practices

2) Business Model & Mapping Layer Best Practices ( BMM layer Best Practices)

3) Presentation Layer Best Practices

OBIEE Presentation Layer Design Principles/Best Practices

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about

Today we will discuss the design principles for Presentation Layer.

Principle 1.Subject Areas

Do not include all presentation layer objects in a single subject area (Presentation Catalog). While it is possible but still not recommended approach.

 

Principle 2. Development with End Users in Mind

Always develop the presentation layer so that end users are able to understand and use it.

Principle 3. Role Based Subject Area.

It is always good idea to create presentation catalog to suit the needs of individual users or user types. For ex. create a separate presentation catalog for sales managers because they need to see only an overview of an organization.

Principle 4. Presentation Tables

Presentation Tables should consistent across presentation catalog.

• List Dimension tables first.
• Do not mix dimension and fact columns in the same table.
• Apply consistent ordering and naming conventions for tables and columns across catalogs.
• Include the words “measure” or “fact” in the names of the fact presentation tables.

Principle 5. Rule of Seven

Keep presentation catalogs small and easy to understand by limiting the number of tables to seven.

Keep Presentation catalogs small and easy to understand by limiting the number of columns to seven.

Principle 6. Fact Tables

Try to limit the presentation catalogs so that there are no more than a couple of fact presentation tables in each catalog. It is important to avoid situation in which there are multiple join paths existing within one presentation catalog.

Principle 7. Implicit Fact Columns

If you have multiple fact presentation tables, it is always best practice to assign an implicit fact column. Implicit facts come into play when an Answers report contains columns from more than one logical dimension table and no explicit facts.

Principle 8. Canonical Time

Canonical time is a useful way for allowing users to report for a specific period of time across multiple star schema. If you use canonical time ,make sure that the corresponding time presentation table is given a very generic  name and that name is consistent across all the presentation catalogs.

It should be the first table in a catalog.

Principle 9. Secondary Time Dimension

This function enables users to build time reports for specific star schema.

Secondary time dimensions can be given their own presentation tables further down the list.We can place all secondary time dimension objects into a single presentation table.

Principle 10. Nesting Presentation Table

Prefix presentation table names with a hyphen to group common objects together into sub folders.

Principle 11. Presentation Object Names & Description

• Use the Alisa tab to keep track of prior names.

• Use the default option that synchronize the presentation column name with the underlying logical column name.

• Use only logical, business -oriented names ( rather than physical object names) in the presentation Layer.

OBIEE BMM Layer Design Principles/Best Practices

OBIEE BMM Layer Design Principles / Best Practices

There are certain design principles or best practices oracle suggest in designing the OBIEE repository. If you open any standard/sample repository provided by oracle you will understand what exactly I am talking about

Today we will discuss the design principles for BMM Layer.

Principle 1. Use Multi User Development Environment

Use the Multi- User Development  facility if there are multiple developers. Multiple developers to connect “online” to the same repository file and making changes is not recommended.

Multi User Development allows user to define a series of projects within the repository file ,where each project is a subset of the entire repository .If developers want to make changes , they can check out a project to a local machine make and test the changes,and then check the modifications back into the master repository file.

Principle 2.  Always run Global Consistency Check before releasing a repository.

Whenever we make changes to a repository ,always be sure to run Global consistency check. It is bad practice to release a repository that still contains consistency check errors. In some cases, consistency errors prevent Oracle BI Server from loading the repository. Use the Consistency check manager to identify and debug check messages.

Principle 3.  Separate Business Model

Even if you have only a single data source or schema in the physical layer, or you have only one physical data source for the repository, it is still good practice to break out the physical objects into multiple business models in the BMM layer to represent the independent areas of functionality.

Principle 4. Logical Tables

When building logical tables, do not merge multiple dimension tables into a single logical dimension table,and do not merge multiple fact tables into a single logical fact table.

Having multiple logical fact tables also makes it easier to create well defined projects for Multi User development.

It is also a good practice to prefix logical table names with either Dim-, Fact- ,or Fact Compound -.

This allows you to easily see how the tables are being used. It also groups the tables in the business model, so that facts are groups with facts, dimensions with dimensions  and so on.

Principle 5. Time Dimension

There are few things to keep in mind in time dimension factor.

• Always must ensure that time Dimension hierarchy is built correctly and the logical level of each time- logical table source is set correctly
• If there are multiple time dimensions within the business model, for consistency, make sure that all time dimension logical table contains the same columns and general structure. This is good for reporting purpose.

Principle 6. Logical Fact & Dimension table columns

• Always assign a primary key for logical dimension tables. All logical dimension columns should be renamed in a way that is meaningful to users.
• Bring only required columns in to the BMM layer for reporting.
• Do not assign logical primary key for logical fact tables.
• Create meaningful name for measures
• Set aggregation rule for every logical fact columns.
• Create “dummy” measures to group facts.

Principle 7. Logical Joins

Use only logical(complex ) joins in BMM layer. And always accept default properties when creating joins.

Principle 8. Calculated Measure

Be very careful when building calculated measures.

• Use logical columns for calculations that require an aggregation rule that is applied before the calculation.
• Use physical columns for calculations that require an aggregation rule to be applied after the calculation.
  

Principle 9. Aggregates

Few important things to keep in mind about Aggregates.

• Try to ensure that each aggregate table has an effective summary ration with underlying detail.
• Ensure that the logical level of every aggregate logical table source is set correctly.
  
• Always test to ensure that aggregates tables are being used as expected.
• If an aggregates is not used ,try changing the number of elements on one of the related logical dimension levels.

Principle 10. Dimension Hierarchies

In Dimension Hierarchies few things  are very important to keep in mind

• It is best practice to create dimensional hierarchy for every logical dimension table in BMM layer.
• All Dimension must have at least two levels : the total level and detail level.
• If you are creating Dimensional Hierarchy manually, be sure to check Grand total level for the Total Level.
• Use Update Row Counts or Estimate Levels to set the number of elements for every level of every Dimension Hierarchy.
• Think about the experience of user when enabling drill down.
  

Principle 11. Avoid Snowflake schema

When there is Snowflaking in physical model,We should try to avoid Snowflaking in BMM layer and build models that use only star schema .

Use WHERE clause filters to help avoid using opaque views or complex joins in the physical layer.