Saturday, March 1, 2014

OBIEE Interview Questions and Answers


Define pipeline. Did you use it in your projects?
o Yes, pipelines are the stages in a particular transaction. Assessment,  finance etc.

How do you create filter on repository?
o Where condition on content tab.

How do you work in a multi user environment? What are the steps?
o Create a shared directory on the network for Multi-user Development (MUD).
o Open the rpd to use in MUD. From Tools->Options, setup the MUD directory to point to the above directory.
o Define projects within the rpd to allow multiple users to develop within their subject area or Facts.
o Save and move the rpd to the shared directory setup in point 1.
o When users work in the MUD mode, they open the admin tool and start with
o MUD ->Checkout to checkout the project they need to work on (not use the File open as you would usually do).
o After completely the development, user checkin the changes back to the network and merge the changes.

Where are passwords for userid? Ldap,external table authentication stored respectively?
o passwords for userid are in siebel analytics server repository Ldap authentication in Ldap server external database in a table in external database

Can you bypass Siebel analytics server security ?if so how?
o yes you can by-pass by setting authententication type in NQSCONFIG file in the security section as:authentication_type=bypass_nqs.instanceconfig.xml and nqsconfig.ini are the 2 places
Where can you add new groups and set permissions?
o you can add groups by going to manage>security>add new groups> You can give permissions to a group for query limitation and filter conditions.

what are the things you can do in the BMM layer?
o Aggregation navigation, level base metrics, time series wizard, create new logical column, complex join.

what is Ragged hierarchy? and how do u manage it
o Ragged Hierarchy is one of the different kinds of hierarchy.
o A hierarchy in which each level has a consistent meaning, but the branches have inconsistent depths because at least one member attribute in a branch level is unpopulated. A ragged hierarchy can represent a geographic hierarchy in which the meaning of each level such as city or country is used consistently, but the depth of the hierarchy varies.
o For example, a geographic hierarchy that has Continent, Country, Province/State, and City levels defined. One branch has North America as the Continent, United States as the Country, California as the Province or State, and San Francisco as the City. However, the hierarchy becomes ragged when one member does not have an entry at all of the levels. For example, another branch has Europe as the Continent, Greece as the Country, and Athens as the City, but has no entry for the Province or State level because this level is not applicable to Greece for the business model in this example. In this example, the Greece and United States branches descend to different depths, creating a ragged hierarchy.

What is the difference between Single Logical Table Source and Multiple Logical Table Sources?
o If a logical table in BMM layer has only one Table as the source table then it is Single LTS.
o If the logical table in BMM layer has more than one table as the sources to it then it is called Multiple LTS.
o Ex: Usually Fact table has Multiple LTS’, for which sources will be coming from different Physical tables.
Can you let me know how many aggregate tables you have in your project? On what basis have you created them?
o As per resume justification document

How do you bring/relate the aggregate tables into the Siebel analytics Logical layer?
o One way of bringing the Aggregate Tables into the BMM layer is by bringing them as Logical Table sources for the corresponding Fact table.
o This is done by dragging and dropping the aggregate table into the corresponding fact table. After doing that establish the column mappings and the set the aggregation levels.

How do you know which report is hitting which table, either the fact table or the aggregate table?
o After running the report, go to Administration tab and go to click on Manage Sessions. There you can find the queries that are run and in the View Log option in the Session Management you can find which report is hitting which table.

Suppose I have report which is running for about 3 minutes typically. What is the first step you take to improve the performance of the query?
o Find the sql query of the report in Admin->manage Session-> run the sql query on toad ->read the explain plan output ->modify the SQL based on the explain plan output

Suppose you have a report which has the option of running on aggregate table. How does the tool know to hit the Aggregate table and for that what the steps you follow to configure them?
o Explain the process of Aggregate navigation
Have you heard of Implicit Facts? If, so what are they?
o An implicit fact column is a column that will be added to a query when it contains columns from two or more dimension tables and no measures. You will not see the column in the results. It is used to specify a default join path between dimension tables when there are several possible alternatives.
o For example, there might be many star schemas in the database that have the Campaign dimension and the Customer dimension, such as the following stars:
Campaign History star. Stores customers targeted in campaign.
Campaign Response star. Stores customer responses to a campaign.
Order star. Stores customers who placed orders as a result of a campaign.
In this example, because Campaign and Customer information might appear in many segmentation catalogs, users selecting to count customers from the targeted campaigns catalog would be expecting to count customers that have been targeted in specific campaigns.
To make sure that the join relationship between Customers and Campaigns is through the campaign history fact table, a campaign history implicit fact needs to be specified in Campaign History segmentation catalog. The following guidelines should be followed in creating
segmentation catalogs:
Each segmentation catalog should be created so that all columns come from only one physical star.
Because the Marketing module user interface has special features that allow users to specify their aggregations, level-based measures typically should not be exposed to segmentation users in a segmentation catalog.

What is aggregate navigation? How do you configure the Aggregate tables in Siebel Analytics?
o Aggregate tables store precomputed results, which are measures that have been aggregated (typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular technique for speeding up query response times in decision support systems.
o If you are writing SQL queries or using a tool that only understands what physical tables exist (and not their meaning), taking advantage of aggregate tables and putting them to good use becomes more difficult as the number of aggregate tables increases. The aggregate navigation capability of the Siebel Analytics Server, however, allows queries to use the information stored in aggregate tables automatically, without query authors or query tools having to specify aggregate tables in their queries. The Siebel Analytics Server allows you to concentrate on asking the right business question; the server decides which tables provide the fastest answers.

(Assume you are in BMM layer) We have 4 dimension tables, in that, 2 tables need to have hierarchy, then in such a case is it mandatory to create hierarchies for all the dimension tables?
o No, its not mandatory to define hierarchies to other Dimension tables.

Can you have multiple data sources in Siebel Analytics?
o Yes.

How do you deal with case statement and expressions in siebel analytics?
o use expression builder to create case when…then.. end statement

Do you know about Initialization Blocks? Can you give me an example where you used them?
o Init blocks are used for instantiating a session when a user logs in.
o To create dynamic variable you have to create IB to write sql statement.

what is query repository tool?
o It is utility of Seibel/OBIEE Admin tool
o allows you to examine the repository metadata tool
o for example: search for objects based on name,type.
o Examine relationship between metadata objects like which column in the presentation layer maps to which table in physical layer

what is JDK and why do we need it?
o Java Development Kit (JDK), A software package that contains the minimal set of tools needed to write, compile, debug, and run Java applets.

Oracle doesn’t recommend Opaque Views because of performance considerations, so why/when do we use them?
o an opaque view is a physical layer table that consists of select statement. an opaque view should be used only if there is no other solution.

Can you migrate the presentation layer to a different server.
o No we have to migrate the whole web & rpd files

How do you identify what are the dimension tables and how do you decide them during the Business/Data modeling?
o Dimension tables contain descriptions that data analysts use as they query the database. For example, the Store table contains store names and addresses; the Product table contains product packaging information; and the Period table contains month, quarter, and year values. Every table contains a primary key that consists of one or more columns; each row in a table is uniquely identified by its primary-key value or values

Why do we have multiple LTS in BMM layer?What is the purpose?
o to improve the performance and query response time.

what is the full form of rpd?
o there is no full form for rpd as such, it is just a repository file (Rapidfile Database)

how do i disable cache for only 2 particular tables?
o in the physical layer, right click on the table there we will have the option which says cacheable

How do you split a table in the rpd given the condition? (The condition given was Broker and customer in the same table) Split Broker and customer.
o we need to make an alias table in the physical layer.

What type of protocol did you use in SAS?
o TCP/IP

Did you create any new logical column in BMM layer, how?
o Yes. We can create new logical column in BMM layer.
o Example: Right click on fact table -new logical column-give name for new logical column like Total cost.
o Now in fact table source, we have one option column mapping, in that we can do all calculation for that new column.

Can you use physical join in BMM layer?
o yes we can use physical join in BMM layer. when there is SCD type 2 we need complex join in BMM layer.

Can you use outer join in BMM layer?
o yes we can. When we are doing complex join in BMM layer ,there is one option type, outer join is there.

What are other ways of improving summary query reports other than Aggregate Navigation and Cache Management
Indexes
Join algorithm
Mat/view query rewrite
Web proper report design its optimal by making sure that it is not getting any addition column or rows

What is level-base matrics?
o Leval-base matrics means, having a measure pinned at a certain level of the dimension. For Example, if you have a measure called Dollars, you can create a Level Based Measure called Yearly Dollars which (you guessed it) is Dollars for a Year. This measure will always return the value for the year even if you drill down to a lower level like quarter, month… etc. To create a level based measure, create a new logical column based on the original measure (like Dollars in the example above). Drag and drop the new logical column to the appropriate level in the Dimension hierarchy (in the above example you will drag and drop it to Year in Time Dim
o A LBM is a metric that is defined for a specific level or intersection of levels.
o Monthly Total Sales or Quarterly Sales are the examples.
o You can compare monthly sales with quarterly sales. You can compare customer orders this quarter to orders this year

1 comment:

Craig Shallahamer said...

I just wanted to let you know that your link to the Oracle blog, ORAPUB, is not the correct link. OraPub is not a Chinese company. orapub.cn is a bogus blog. Please use the correct link of www.orapub.com. Thank you.

Post a Comment

Best Blogger TipsGet Flower Effect