Define
repository in terms of OBIEE?
Repository stores the Meta data information.
OBIEE repository is a file system and the extension of the repository file is
.RPD (There is no specific abbreviation for RPD)
All the rules needed for security, data modeling,
aggregate navigation, caching, and connectivity is stored in metadata
repositories.
Each metadata repository can store multiple
business models. BI Server cannot access multiple repositories.
What
is the end to end life cycle of OBIEE?
OBIEE life cycle:
1.
Gather Business
Requirements
2.
Identify source systems
3.
Design ETL to load to a DW
if source data doesn’t exist.
4.
Build a repository
5.
Build dashboard or use
answers for reporting.
6.
Define security (LDAP or
External table…)
7.
Based on performance,
decide on aggregations and/or caching mechanism.
8.
Testing and QA.
How
does OBIEE Architecture works?
There are five parts of OBIEE Architecture.
1.
OBIEE Clients
2.
OBIEE Web Server
3.
OBIEE server
4.
OBIEE scheduler
5. Data sources
Explain
the three layers of OBIEE Admin tool?
Metadata that represents the analytical Model
Is created using the OBIEE Administration tool.
Repository divided into three layers
1. Physical – Represents the data Sources
2. Business – models the Data sources into
Facts and Dimension
3. Presentation – Specifies the users view of
the model; rendered in BI answers
How
do you import sources into Physical Layer?
Using ODBC or OCI connections. In the Oracle BI
Administration Tool, click File > Import > from Database.
If
you have 3 facts and 4 dimension and you need to join them, would you recommend
joining fact with fact? If no, than what is the option? Why you won’t join fact to fact?
In the BMM layer, create one logical table
(fact) and add the 3 fact table as logical table source.
What
is connection pool and how many connection pools did you have in your last
project?
Connection pool is needed for every physical
database.
It contains information about the connection
to the database, not the database itself.
Can use either shared user accounts or can
use pass-through accounts -Use: USER and PASSWORD for pass through.
We can have multiple connection pools for
each group to avoid waiting.
Purpose
of Alias Tables
An Alias table (Alias) is a physical table
with the type of Alias. It is a reference to a logical table source, and
inherits all its column definitions and some properties from the logical table
source. A logical table source shows how the logical objects are mapped to the
physical layer and can be mapped to physical tables, stored procedures, and
select statements. An alias table can be a reference to any of these logical
table source types.
Alias Tables can be an important part of
designing a physical layer. The following is a list of the main reasons to
create an alias table:
1.
To reuse an existing table
more than once in your physical layer (without having to import it several
times)
2.
To set up multiple alias
tables, each with different keys, names, or joins
3.
To help you design
sophisticated star or snowflake structures in the business model layer. Alias
tables are critical in the process of converting ER Schemas to Dimensional
Schemas.
How do you define the relationship between
facts and dimensions in BMM layer?
Using
complex join, we can define relationship between facts and dimensions in BMM layer.
What
is time series wizard? When and how do you use it?
We can do comparison for certain measures
(revenue, sales etc.) for current year vs. previous year, we can do for month
or week and day also
Identify the time periods need to be compared
and then period table keys to the previous time period.
The period table needs to contain a column
that will contain “Year Ago” information.
The fact tables needs to have year ago
totals.
To use the “Time series wizard”. After
creating your business model right click the business model and click on “Time
Series Wizard”.
The Time Series Wizard prompts you to create
names for the comparison measures that it adds to the business model.
The Time Series Wizard prompts you to select
the period table used for the comparison measures
Select the column in the period table that
provides the key to the comparison period. This column would be the column
containing “Year Ago” information in the period table.
Select the measures you want to compare and
then select the calculations you want to generate. For ex: Measure: Total
Dollars and calculations are Change and Percent change.
Once the Time series wizard is run the output
will be:
a) Aliases for the fact tables (in the
physical layer)
b) Joins between period table and alias fact
tables
c) Comparison measures
d) Logical table sources
In the General tab of the Logical table
source etc. you can find “Generated by Time Series Wizard” in the description
section
Then you can add these comparison measures to
the presentation layer for your reports.
Ex:
Total sales of current qtr vs. previous qtr vs. same qtr year ago
Did
you create any new logical column in BMM layer, how?
Yes. We can create new logical column in BMM
layer.
Example: Right click on fact table -new
logical column-give name for new logical column like Total cost.
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?
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?
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?
1.
Indexes
2.
Join algorithm
3.
Mat/view query rewrite
4.
Web proper report design it’s
optimal by making sure that it is not getting any addition column or rows
What
is level-based metrics?
Level-based metrics 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
A LBM is a metric that is defined for a
specific level or intersection of levels.
Monthly Total Sales or Quarterly Sales are
the examples.
You can compare monthly sales with quarterly
sales. You can compare customer orders this quarter to orders this year
What
is logging level? Where can you set logging levels?
You can enable logging level for individual
users; you cannot configure a logging level for a group.
Set the logging level based on the amount of
logging you want to do. In normal operations, logging is generally disabled
(the logging level is set to 0). If you decide to enable logging, choose a
logging
Level of 1 or 2. These two levels are
designed for use by OBIEE Server administrators.
Set Logging Level
1.
In the Administration Tool, select Manage > Security.
2.
The Security Manager dialog box appears.
3.
Double-click the user’s user ID.
4.
The User dialog box appears.
5.
Set the logging level by clicking the up or down arrows next to the Logging
Level field
What
is variable in OBIEE?
You can use variables in a repository to
streamline administrative tasks and modify metadata content dynamically to
adjust to a changing data environment. The Administration Tool includes a
Variable Manager for defining variables
What
is system variable and non-system variable?
System
variables
System variables are session variables that
the OBIEE Server and OBIEE Web use for specific purposes. System variables have
reserved names, which cannot be used for other kinds of variables (such as
static or dynamic repository variables, or for non-system session variables).
When using these variables in the Web,
preface their names with NQ_SESSION. For example, to filter a column on the
value of the variable LOGLEVEL set the filter to the Variable
NQ_SESSION.LOGLEVEL.
Non-system
variables
A common use for non-system session variables
is setting user filters. For example, you could define a non-system variable
called Sales Region that would be initialized to the name of the user’s sales
region. You could then set a security filter for all members of a group that
would allow them to see only data pertinent to their region.
When using these variables in the Web,
preface their names with NQ_SESSION. For example, to filter a column on the
value of the variable Sales Region set the filter to the Variable
NQ_SESSION.SalesRegion.
What
are different types of variables? Explain each.
There are two classes of variables:
1.
Repository variables
2.
Session variables.
Repository
variables.
A repository variable has a single value at
any point in time. There are two types of repository variables:
Static: This value persists, and does not change until a OBIEE Server
administrator decides to change it.
Dynamic: The values are refreshed by data returned from queries. When defining
a dynamic repository variable, you will create an initialization block or use a
preexisting one that contains a SQL query. You will also set up a schedule that
the OBIEE Server will follow to execute the query and periodically refresh the
value of the variable.
Session
Variables
Session variables are created and assigned a
value when each user logs on. There are two types of session variables:
1.
System
2.
Non-system.
What
are the cache management? Name all of them and their uses. For Event polling
table do u need the table in your physical layer?
Monitoring and managing the cache is cache
management. There are three ways to do that.
Disable caching for the system. (INI NQ
config file), Cache persistence time for specified physical tables and Setting
event polling table.
1. Disable caching for the
system.(INI NQ config file :
You
can disable caching for the whole system by setting the ENABLE parameter to NO
in the NQSConfig.INI file and restarting the OBIEE Server. Disabling caching
stops all new cache entries and stops any new queries from using the existing
cache. Disabling caching allows you to enable it at a later time without losing
any entries already stored in the cache.
2. Cache persistence time for
specified physical tables:
You
can specify a cacheable attribute for each physical table; that is, if queries
involving the specified table can be added to the cache to answer future
queries. To enable caching for a particular physical table, select the table in
the Physical layer of the Administration Tool and select the option Make table
cacheable in the General tab of the Physical Table properties dialog box. You
can also use the Cache Persistence Time settings to specify how long the
entries for this table should persist in the query cache. This is useful for
OLTP data sources and other data sources that are updated frequently,
potentially down to every few seconds.
3. Setting event polling table:
OBIEE
Server event polling tables store information about updates in the underlying
databases. An application (such as an application that loads data into a data
mart) could be configured to add rows to an event polling table each time a
database table is updated. The Analytics server polls this table at set
intervals and invalidates any cache entries corresponding to the updated
tables.
For
event polling table, it is a standalone table and doesn’t require to be joined
with other tables in the physical layer
What
is Authentication? How many types of authentication?
Authentication is the process, by which a
system verifies, through the use of a user ID and password, that a user has the
necessary permissions and authorizations to log in and access data. The OBIEE
Server authenticates each connection request it receives.
1.
OBI authentication
2.
External table
authentication
3.
Database authentication
4. LDAP authentication
What
is object level security?
There are two types of object level security:
Repository level and Web level.
Repository
level: In presentation layer we can set Repository
level security by giving permission or deny permission to users/groups to see
particular table or column.
Web
level: this provides security for objects stored in
the OBIEE web catalog, such as dashboards, dashboards pages, and folder and
reports you can only view the objects for which you are authorized. For
example, a mid-level manager may not be granted access to a dashboard
containing summary information for an entire department.
What
is data level security?
This controls the type an amount of data that
you can see in a report. When multiple users run the same report the results
that are returned to each depend on their access rights and roles in the
organization. For example a sales vice president sees results for all regions,
while a sales representative for a particular region sees only data for that
region.
What
is the difference between Data Level Security and Object Level Security?
Data level security controls the type and
amount of data that you can see in reports. Object level security provides
security for objects stored in the OBIEE web catalog, like dashboards,
dashboards pages, folder and reports.
How
do you implement security using External Tables and LDAP?
Instead of storing user IDs and passwords in
a OBIEE Server repository, you can maintain lists of users and their passwords
in an external database table and use this table for authentication purposes.
The external database table contains user IDs and passwords, and could contain
other information, including group membership and display names used for OBIEE
Web users. The table could also contain the names of specific database catalogs
or schemas to use for each user when querying data
Instead of storing user IDs and passwords in
a OBIEE Server repository, you can have the OBIEE Server pass the user ID and
password entered by the user to an LDAP(Lightweight Directory Access Protocol )
server for authentication. The server uses clear text passwords in LDAP
authentication. Make sure your LDAP servers are set up to allow this.
If
you have 2 fact and you want to do report on one with quarter level and the
other with month level how do you do that with just one time dimension?
Using level base metrics.
Did
you work on a standalone Siebel system or was it integrated to other platforms?
Deploying the OBIEE platform without other
Siebel applications is called OBIEE Stand -Alone .If your deployment includes
other OBIEE Application it called integrated analytics -You can say Stand-Alone
OBIEE
How
to sort columns in RPD and web?
Sorting on web column, sort in the RPD its
sort order column
If
you want to create new logical column where will you create (in repository or
dashboard) why?
I will create new logical column in repository.
Because if it is in repository, you can use for any report. If you create new
logical column in dashboard then it is going to affect on those reports, which
are on that dashboard. You cannot use that new logical column for other
dashboard (or request)
What
is the difference between foreign key join & complex join?
Answer1:
A
physical join is at the physical layer and defines the join between two
physical tables.
Logical joins live at the BMM (logical) layer and define a join between two logical tables.
Logical joins live at the BMM (logical) layer and define a join between two logical tables.
The
important differentiation is that at the BMM layer you do not tell the OBIEE
server how to do the join, you just tell it that there is a relationship
between these two logical entities. When the server comes to this logical join
it will use the information in the physical joins and decides how the two
logical tables are joined together.
Answer2:
The Join in BMM layer explains how the join
has to be interpreted. For example in physical layer when you define a join b/w
two tables, you are not explaining the type of join (.e. inner or outer). What
you have is the expression stating the condition of join. In BMM layer between
these two respective tables you explain the type of join.
If
you want to limit the users by the certain region to access only certain data,
what would you do?
Using data level security.
OBIEE Administrator: go to Manage ->
Security in left hand pane u will find the user, groups, LDAP server, Hierarchy
What you can do is select the user and right
click and go to properties, you will find two tabs named as users and logon, go
to user tab and click at permission button in front of user name you have
selected as soon as u click at permission you will get a new window with user
group permission having three tabs named as general ,query limits and filter
and you can specify your condition at filter tab, in which you can select
presentation table ,presentation columns ,logical table and logical columns
where you can apply the condition according to your requirement for the
selected user or groups.
If
there are 100 users accessing data, and you want to know the logging details of
all the users, where can you find that?
To set a user’s logging level
1. In the Administration Tool, select Manage
> Security.
The Security Manager Dialog box appears.
2. Double-click the user’s user ID. The User
dialog box appears.
3. Set the logging level by clicking the up
or down arrows next to the Logging Level field
How
do implement event polling table?
OBIEE Server event polling tables store
information about updates in the underlying databases. An application (such as
an application that loads data into a data mart) could be configured to add
rows to an event polling table each time a database table is updated. The
Analytics server polls this table at set intervals and invalidates any cache
entries corresponding to the updated tables.
Can
you migrate the presentation layer only to different server?
No we can’t do only presentation layer. And
ask him for more information and use one of the above answers
Create ODBC connection in the different serve and
access the layer.
Copy the RPD and migrate it to other server
Define
pipeline. Did you use it in your projects?
Yes, pipelines are the stages in a particular
transaction. Assessment, finance etc.
How
do you create filter on repository?
Where condition on content tab.
How
do you work in a multi user environment? What are the steps?
1.
Create a shared directory
on the network for Multi-user Development (MUD).
2.
Open the RPD to use in MUD.
From Tools->Options, setup the MUD directory to point to the above
directory.
3.
Define projects within the
RPD to allow multiple users to develop within their subject area or Facts.
4.
Save and move the RPD to
the shared directory setup in point 1.
5.
When users work in the MUD
mode, they open the admin tool and start with
6.
MUD ->Checkout to check
out the project they need to work on (not use the File open as you would
usually do).
7.
After completely the
development, user check-in the changes back to the network and merge the
changes.
Where
are passwords for userid? LDAP, external table authentication stored
respectively?
Passwords for userid are in OBIEE server
repository LDAP authentication in LDAP server external database in a table in
external database
Can
you bypass OBIEE server security? If so how?
Yes you can by-pass by setting authentication
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?
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?
Aggregation navigation, level base metrics,
time series wizard, create new logical column, complex join.
What
is ragged hierarchy and how do you manage it?
Ragged Hierarchy is one of the different
kinds of hierarchy.
A hierarchy in which each level has a
consistent meaning, but the branches have inconsistent depths because at least
one member attributes 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.
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?
If a logical table in BMM layer has only one
Table as the source table then it is Single LTS.
If the logical table in BMM layer has more
than one table as the sources to it then it is called Multiple LTS.
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?
As per resume justification document
How
do you bring/relate the aggregate tables into the OBIEE Logical layer?
One way of bringing the Aggregate Tables into
the BMM layer is by bringing them as Logical Table sources for the
corresponding Fact table.
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?
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?
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?
Explain the process of Aggregate navigation
Have
you heard of Implicit Facts? If, so what are they?
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.
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.
1.
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
2.
Segmentation catalogs:
3.
Each segmentation catalog
should be created so that all columns come from only one physical star.
4.
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 OBIEE?
Aggregate tables store pre-computed 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.
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 OBIEE 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 OBIEE 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, and then in such a case is it mandatory to create hierarchies
for all the dimension tables?
No, it’s not mandatory to define hierarchies
to other Dimension tables.
Can
you have multiple data sources in OBIEE?
Yes.
How
do you deal with case statement and expressions in OBIEE?
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?
Init blocks are used for instantiating a
session when a user logs in.
To create dynamic variable you have to create
IB to write SQL statement.
What is query repository tool?
It is utility of Seibel/OBIEE Admin tool
Allows you to examine the repository metadata tool
For example: search for objects based on name, type.
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?
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?
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?
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?
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?
To improve the performance and query response time.
What is the full form of RPD?
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?
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.
We need to make an alias table in the physical layer.
What type of protocol did you
use in SAS?
TCP/IP
How is security set up in
OBIEE? How many methods of security can be set up in OBIEE…?
Let’s start with Authentication and Authorization.
Authentication is process of confirming whether the user is a valid user
or not. Is he part of this company? Is he an employee of our external
suppliers?
Authorization is process of giving access to different pieces of
the OBIEE. One user “UserA” can access dashboards, can create iBots, can
schedule reports and can do administrative tasks. Another user “UserB” can see
only part of what “UserA” has access to and “UserB” has access to some other
tabs of the dashboard that “UserA” does not.
This layer of separating who can access what is done as part of
Authorization.
How does OBIEE handle Authentication?
It’s very flexible and can be integrated to one of your existing
technologies like LDAP, Oracle EBS, AD, and Oracle Database. So, do the same
username have to exist in OBIEE security layer as part of RPD development? Yes,
by default, OBIEE stores list of usernames and passwords and checks incoming
credentials against it.
Heard about External Table
Authentication? Where does this come into play?
Instead of storing usernames and passwords directly in the RPD, these
are stored in the database for better management purposes. This also helps in
RPD migration and deployment across multiple environments.
How is Authorization
handled in OBIEE?
Authorization is done as part of security in Presentation Services.
Does OBIEE have two layers of security?
Yes, first at the RPD level and second at the presentation services
level.
Do you have to have the same
username established at both RPD and presentation services levels for this to
work?
Not necessarily based on my knowledge. I will let others comment on this
What kind of privileges can be
granted from presentation services level?
Access to iBots, certain tabs in the dashboard, delivers, alerts,
schedule reports etc.
Why OBIEE security is different compared to other BI tools?
Because it’s very flexible and can integrate into any existing security
architecture an organization has built and reduces the need for one more layer
of administration.
Did Oracle OBIEE do a good job
of communicating and convincing the user community regarding the security
architecture?
Probably not... I understand the complexity behind this and the mere
flexibility of the tool makes this even difficult.
Nerds of OBIEE
What is the default location
of a repository file?
[InstalledDirectory]\OracleBI\server\Repository\
How many repository files can
be loaded to a BI Server simultaneously? Assume it’s a single server with
single instance of BI Server running just to keep things easy
I’m not sure about the limit of number of repositories loaded at same
time but to make one rpd online we need one saw server so for multiple rpds to
be online we need as many saw servers.
If you have more than 3
repository files mentioned in your NQSConfig.ini file as default, which one
gets loaded to the memory when the BI Server is started?
Ex:
Star = SamplerRepository1.RPD, DEFAULT;
Star = SamplerRepository2.RPD, DEFAULT;
Star = SamplerRepository3.RPD, DEFAULT;
If we don’t specify comment the last repository will be loaded online,
so in this case SamplerRepository3.rpd will be loaded.
How do you import Essbase Cubes into your repository?
What’s XMLA and where is it
used for in OBIEE context?
The Provider Services tool that comes with Essbase is used to provide
the interface, with Oracle BI Server talking to Essbase through its XMLA
interface. (Need to find the answer still)
XML for Analysis (abbreviated as XMLA) is an industry standard for data
access in analytical systems, such as OLAP and Data Mining. XMLA is based on
other industry standards such as XML, SOAP and HTTP.
Can you change the location of
your RPD file in your OBIEE Configuration? If Yes, Where would you mention the
new location of this RPD file for Bi Server?
Read the NQSConfig file “Repository Section”. You will easily find the
answer. The answer for this is “It cant be changed”.
The repository location can be changed, this must be done when
clustering the BI Server. The parameters in the NQSCONFIG.INI file are
REPOSITORY_PUBLISHING_DIRECTORY =
REQUIRE_PUBLISHING_DIRECTORY = YES;
REPOSITORY_PUBLISHING_DIRECTORY =
REQUIRE_PUBLISHING_DIRECTORY = YES;
What kind of joins would you
perform in the physical layer of the repository file when opened with
Administration tool?
We cannot have outer joins in Physicla layer. We can outer joins in BMM
layer. In BMM layer - complex join can
be full inner join or full outer join or whatever your criteria was,but in
physical layer - physical join is always an inner join.
What are the minimum services
needed to load a repository file onto memory and view a dashboard which has
reports that have been refreshed on a scheduled basis?
Oracle BI Java Host, Oracle BI Presentation Server, and Oracle BI Server
Can you use an OLTP backend
database for creating a dashboard?
Technically you can use OBIEE with OLTP as well. However real world
scenarios pertaining to OLTP makes it difficult for OBIEE to create Star schema
from OLTP. Also OBIEE is an engine to perform analytics queries, while in most
cases queries generated against OLTP schemas are operational in nature. Better
option is to use BI publisher and do away with OBIEE when querying against
OLTP.
Bottom line, you can use OLTP with OBIEE but performance will be an issue (not advisable).
Bottom line, you can use OLTP with OBIEE but performance will be an issue (not advisable).
OBIEE works with any database if you force to do. If you want to follow
best practices, it’s recommended to use with OLAP systems. After all OBIEE is
analytics reporting tool and OLAP databases were designed for reporting
requirements. So it’s always advised use the OLAP to get better performance and
better analytics from OBIEE.
How many tables are needed in
minimum to pass through the Consistency Checking in Logical Layer (Business
Mapping and Modeling Layer) of the repository?
TWO TABLES…
Can you create new views in
the database using OBIEE Administration tool? Can you explain the procedure for
doing this?
The view SQL Syntax is standard ANSI SQL. From what I understand, you
have a table named "Table 1" which has four columns,
"Attr1", "Attr2", "Measure 1", "Measure
2". You want to create a new measure named "Attribute 3" (I'd
recommend not having spaces in your column and table names FYI).
If that's correct, then try this
1. In the physical layer, change the object type to view.
2. put the following SQL in the box
Select "Table 1"."Attr 1","Table 1"."Measure 1", 'Y' "Attribute 3"
From "Table 1"
3. Define your columns in the object appropriately.
If that's correct, then try this
1. In the physical layer, change the object type to view.
2. put the following SQL in the box
Select "Table 1"."Attr 1","Table 1"."Measure 1", 'Y' "Attribute 3"
From "Table 1"
3. Define your columns in the object appropriately.
*
You are right, it is called a
SELECT type.
About your second question I'm not sure what you mean. You use a SQL Select statement in this area. The Alias of the columns need to match the columns you setup in the RPD exactly. But in this process you don't create a physical DB view object, hence there's no need to name it. You just create the select statement that you would use to create a view and OBIEE references the statement.
About your second question I'm not sure what you mean. You use a SQL Select statement in this area. The Alias of the columns need to match the columns you setup in the RPD exactly. But in this process you don't create a physical DB view object, hence there's no need to name it. You just create the select statement that you would use to create a view and OBIEE references the statement.
What is a complex join in
OBIEE and why, where is it used?
A complex join is a metadata of sql join statement in the repository.
The complex join in the BM is it will dynamically select
which Logical table sources to join together from the Logical tables.
If you use a FK business model join, you are forcing which fields OBI can
use and therefore which LTSs it can use. The joining is not physical table
to physical table, but really LTS to LTS. Whatever physical joins are
underneath it will use (as long as they are there).
But the concept is LTS-to-LTS, not the individual tables inside.
The problem with the FK join is that you are tying
OBI's hands and limiting its choices. If you made a FK join on MONTH_KEY
for example, if you selected a Day field on your report, OBI would try to
find a LTS souce that
1) has day on it and
2) has a field that maps to MONTH_KEY
which your day table might not have mapped. Thus, you would be asking
for something that doesn't exist, which results in throwing an error.
which Logical table sources to join together from the Logical tables.
If you use a FK business model join, you are forcing which fields OBI can
use and therefore which LTSs it can use. The joining is not physical table
to physical table, but really LTS to LTS. Whatever physical joins are
underneath it will use (as long as they are there).
But the concept is LTS-to-LTS, not the individual tables inside.
The problem with the FK join is that you are tying
OBI's hands and limiting its choices. If you made a FK join on MONTH_KEY
for example, if you selected a Day field on your report, OBI would try to
find a LTS souce that
1) has day on it and
2) has a field that maps to MONTH_KEY
which your day table might not have mapped. Thus, you would be asking
for something that doesn't exist, which results in throwing an error.
When Complex joins are used in the BMM (Business Model) layer then they
are really acting as placeholders. They allow the OBI Server to decide on which
are the best joins to use in the Physical Layer to satisfy the request.
In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will decide on the best physical joins to use to meet the requirements if a Complex join is used in the BMM Layer.
Hope this help.
Be careful, you can also use a "complex join" in the physical layer but it's to be able to set a complex join condition and you must do the difference with the complex join in the BMM as describe above.
In a Dimension, you may have multiple Logical Table sources that all join to one fact table. The OBI Server will decide on the best physical joins to use to meet the requirements if a Complex join is used in the BMM Layer.
Hope this help.
Be careful, you can also use a "complex join" in the physical layer but it's to be able to set a complex join condition and you must do the difference with the complex join in the BMM as describe above.
Lets take one example.
We have a fact (F) joined with DIM (D) having 2 LTS DAY & WEEK using a complex join in BMM. And Physical/PF joins on DAY_ID & WK_ID respectively in physical layer.
Now if I take a column from one of the LTS suppose from day on a report with other few columns/measures from the fact.
On what basis the BI server will decide from the complex join in the BMM that which physical join will be selected in the PHYSICAL layer to issue a SQL.
We have a fact (F) joined with DIM (D) having 2 LTS DAY & WEEK using a complex join in BMM. And Physical/PF joins on DAY_ID & WK_ID respectively in physical layer.
Now if I take a column from one of the LTS suppose from day on a report with other few columns/measures from the fact.
On what basis the BI server will decide from the complex join in the BMM that which physical join will be selected in the PHYSICAL layer to issue a SQL.
Is it mandatory to have
hierarchies defined in your repository? If yes, where does it help? If No, what
happens in the reports?
not mandatory, if u have u can drill the data, like if u click on year
you will get quarters in the other column or summary values to detailed values.
If No, you cannot drilldown.
How do you create outer joins
in physical layer?
We cannot
What does Consistency Checking
perform; what are the minimum criteria to pass consistency checking for a given
repository?
Consistency check is a utility in the Administration Tool that checks if
a repository has met certain requirements. Repositories and the business models
within them must pass the consistency check before you can make business models
available for queries. When a repository or business model is inconsistent, a
detailed message alerts you to the nature of the inconsistency.
The Consistency Check Manager displays three types of messages:
Error messages indicate errors that need to be fixed to make the
repository consistent.
Warning messages indicate conditions that may or may not be errors,
depending upon the intent of the Oracle BI Server administrator. For example,
if the Administrator user has an empty password this should be addressed, but
is not a requirement for a consistent repository.
Best Practices messages provide information about conditions but do not
indicate an inconsistency. For example, if there are physical tables with no
keys defined, a best practice message is displayed. Defining keys for physical
tables is best practice, but is not a requirement for a consistent repository.
For each message the Consistency Check Manager identifies the message
type, the object type, the object, and provides a detailed description of the
message. There are options to display only selected message types, display
results using qualified names, check all objects in the repository, and copy
the results to another file.
Minimum criteria
Checking Global Consistency checks for errors in the entire repository.
Some of the more common checks are done in the Business Model and Mapping layer
and Presentation layer. Since these layers are not defined yet, bypass this
check until the other layers in the repository are built. You learn more about
consistency check later in this tutorial.
Does OBIEE store physical sql
? How is physical sql generated in OBIEE environments?
YES.
HOW: The physical SQL is the SQL that send the BI Server to the data sources in order to retrieve data. This SQL is generated by
the query compiler during the query processing of the logical sql created by a client tool generally through an answer (Advanced Tab)
Are there any occasions
where physical sql is not generated when running against a backend database
like Oracle, SQL Server or any other relational database?
Depends on Logging level. (Elaborate it later)
What is the best default
logging level for production users?
The login level is a parameter which control the number of information
that you will retrieve in the log file from nothing (level 0 - OBIEE - No Log
Found) to a lot of information (level 5).
You can enable logging level for individual users; you cannot configure
a logging level for a group.
The session variable LOGLEVEL overrides a user's logging level. For
example, if the Oracle BI Administrator has a logging level defined as 4 and
LOGLEVEL is defined as default 0 (zero) in the repository, the Oracle BI
Administrator's logging level will be 0.
In normal operations:
Users have a logging level
set to 0
Administrators have a
logging level set to 2
What is the difference between
logging level 1 and 2?
|
Logging Levels
|
Logging Level
Information That Is Logged
|
|
Level 0
|
No logging
|
|
Level 1
|
Logs the SQL statement issued from the client application
Logs elapsed times for query compilation, query execution, query cache processing, and back-end database processing Logs the query status (success, failure, termination, or timeout). Logs the user ID, session ID, and request ID for each query |
|
Level 2
|
Logs everything logged in Level 1
Additionally, for each query, logs the repository name, business model name, presentation catalog (called Subject Area in Answers) name, SQL for the queries issued against physical databases, queries issued against the cache, number of rows returned from each query against a physical database and from queries issued against the cache, and the number of rows returned to the client application |
|
Level 3
|
Logs everything logged in Level 2
Additionally, adds a log entry for the logical query plan, when a query that was supposed to seed the cache was not inserted into the cache, when existing cache entries are purged to make room for the current query, and when the attempt to update the exact match hit detector fails |
|
Level 4
|
Logs everything logged in Level 3
Additionally, logs the query execution plan. Do not select this level without the assistance of Technical Support |
|
Level 5
|
Logs everything logged in Level 4
Additionally, logs intermediate row counts at various points in the execution plan. Do not select this level without the assistance of Technical Support |
What are the different places
(files) to view the physical sql generated by an Answers report?
A quick way to see the content of the Nqquery.log to grab the SQL information is to follow
these quick instructions.
Click Settings >
Administration > Manage Sessions > View Log to view the query log and
examine the Physical SQL generated by the request.
To be able to see the log, you must set the log
level at least with the level 2.
Where the BI Server
does logs its start, stop and restart times in the file system?
NQServer.log can be located from the path
ORACLEBI/server/log/NQServer.log.This log consists of BI Server Start up issues
and Subject areas loaded and the data sources connected from within the RPD. If
the NQSConfig.ini has errors or if RPD has version issues or if proper RPD is
not loaded, the BIServer fails to start and this will be recorded in the
BIServer.
You have two tables Table 1
and Table 2 joined by a foreign key in the database? They are imported together
from the database into your physical layer. Is this relationship still
preserved in the OBIEE physical layer?
NO. Need to join them again with their foreign keys (Elaborate it more
later)
Same as question 22 but what
happens if you import each table separately?
Same. Need to Join them in Physical Layer (Elaborate it more later)
If Table 1 and Table 2 are
dragged from physical layer to BMM layer, which table becomes a Fact Table and
which table becomes a Dimension Table?
Depends on the relation between table 1 and table 2. The table that N
relation will be fact and the other will be dimension.
More info:
In Physical Layer joins, it matters which table you click first. The
join is creating a one-to-many (1:N) relationship that joins the key column in
the first table to a foreign key column in the second table. The Administration
Tool makes a best "guess" and automatically determines which columns
should be included in the join.
There are two main categories of logical tables: fact and dimension.
Logical fact tables contain the measures by which an organization gauges its
business operations and performance. Logical dimension tables contain the data
used to qualify the facts.
What if the tables (Table 1
and Table 2) are not joined, and then what happens in BMM layer?
We will get warning that, logical table is joined with any other table
What is the difference between
logical table and logical table source?
A logical table contains
one or more logical table source. The mapping between physical columns and
logical columns are done in this element.
How many LTS (Logical Table Sources) can a logical
table have? What’s the minimum and maximum?
Info:
How many server instances can
coexist in an OBIEE cluster?
Maximum 16
What’s a dimension only query in OBIEE?
Aggregation rules are set on
top of …………… columns (Physical Columns or Logical Columns or Both)
Logical columns
What is alternative drill path? How do you set
alternative drill path in OBIEE?
What are the pre-requisites for using aggregate
persistence wizard? What does aggregate persistence wizard create and what does
it help with?
Can you import data in
multiple sheets from an Excel file? How do you import data from an Excel sheet?
(Forget about csv files for now)
Yes.
What are the uses of “Execute
Direct SQL” feature in Answers? Is it a good practice to allow this feature for
production users?
Check it later….NO
How do you disable “Execute
Direct SQL” feature for all the users of your repository?
Presentation services Admin
I want to store the value of
the last time the repository was updated and show it in the dashboard? What
type of variable can be used for this purpose?
Dynamic Repository variable
Is there any way to
see a list of all the repository variables defined in your
repository using Answers tool?
What are Chronological Keys in
OBIEE? How are they different from Logical Keys?
The chronological keys are used in the hierarchies where the dimension
attributes referred in the same keep on increasing in a particular fashion. The
best example of the same is the date dimension. The chronological key is
referred to as the attribute which keeps on increasing. This helps in using the
time series functions and the YTD and MTD measures calculation.
You can also declare the other hierarchy attributes as chronological key if their value keep on increasing in a certain fashion.
You can also declare the other hierarchy attributes as chronological key if their value keep on increasing in a certain fashion.
The Chronological Key should
be the date itself. The unique identifier is the date/time itself, there can be
many dates in a year, month, etc. If the key is structured as you have said
yyyymmdd you can use it as the key. The chronological key has to increase
sequentially, so yyyyddmm wouldn't work. The reason is that the query generated
by date specific functions in OBI like AGO use the chronological order in
building the query, as mentioned in an earlier response.
You want to use a database
built-in function bypassing the functions defined in OBIEE? What OBIEE function
helps in achieving this? What’s the syntax for this function?
Evaluate function
No comments:
Post a Comment