What is Business
Intelligence?
Business
intelligence is the process of gathering information and knowledge through the
process of analyzing business data. The major challenges for Business
intelligence is to gather the necessary data about the business and enable
end-users to access that knowledge easily and efficiently and in effect
maximize the success of an organization
Some
of the most popular business tools are Cognos,OBIEE,SAS etc
What is OBIEE?
OBIEE
stands for Oracle Business Intelligence Enterprise Edition.Oracle Business
Intelligence is a complete, open, and architecturally unified business intelligence system for the
enterprise that delivers abilities for reporting, ad hoc query and
analysis, online analytical processing (OLAP),
dashboards
Difference
b/w Star Schema and Snowflake Schema?
The
star schema is the simplest schema. A single Fact table (center of the star)
surrounded by multiple dimensional tables , It is called a star schema because
the diagram resembles a star
Whereas,
A Snowflake schema is a schema which consists of a single Fact table and
multiple dimensional tables. A variant of the star schema where each dimension
can have its own dimensions.These Dimensional tables are normalized.
What
are the different layers of repository?
Physical
Layer, Business Model and Mapping Layer(BMM) , Presentation Layer.
What are the key
configuration files in OBIEE?
NQSConfig.ini
,NQSCluster.ini, odbc.ini, instanceconfig.xml
What are different log
files in OBIEE?
NQServer.log,
NQQuery.log, NQSAdminTool.log
How can you filter/limit the rows returned from database in RPD?
with the help of where clause in content tab in logical table source we can filter the data(limit the rows)
In
the BMM layer,double click on logical table sources ,on the content tab we can
put a where condition in where pane.
How can you disable the
cache for only particular tables?
In
physical layer, double click on the table, on the general tab we have an option
to make the particular table non cacheable. Uncheck the cacheable box, by
default table is cacheable.
What
are different ways to view the physical sql generated by an Answers report?
You
can see the SQL at C:\OracleBI\server\Log\NQQuery.log
And
using administrator , Settings ->Administration ->Manage
session->View log
What is column level
security?
Column
level security means restricting acces to OBIEE objects between different
users/groups.
What is data level
security?
Data
level security means controls the type and amount of data users can see in a
report. When multiple users run the same report results may vary, depending on
the access rights user have.
What is complex join?
What is loglevel set for
production users?
Log
level will be 0.
What is Alias ,usage and
how to create?
An
Alias table is a physical table with the type of Alias. It is a reference to a
physical table, and inherits all its column definitions and properties from the
physical table.
What is Opaque view? How
to create ? Why we should avoid them?
An
opaque view is a Physical layer table that consists of a SELECT statement..
In
Physical layer, Right click on table->properties->Select table type as
“Select” ->then write query in Default Initialization String.
It
should be used only if there is no other solution to your modeling
problem. Ideally, a
physical table should be created, or alternatively
a materialized view. Opaque
views prevent the Oracle BI Server from generating its own optimized
SQL,
because they contain fixed SQL statements that are sent to the underlying data
source.
Can we use Complex join
in physical layer?
Yes,we
can use in below scenarios
1) when
we join key column of one table to non key column of other table.
2) when
the operator is other than equal to operator.
What is the default
location of a repository file?
[InstalledDirectory]\OracleBI\server\Repository\
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.
You have to create new
logical column where will you create (in repository or dashboard) why?
We
should create new logical column in repository, Because if it’s in repository,
it can be used for any report. whereas, If we create new logical column in
dashboard then it is going to affect only those reports, which are on that
dashboard. We cannot use that new logical column for other dashboard (or
request).
What are the things can
be done in the BMM layer?
Hierrarchy
creation, Aggregation navigation, level base metrics, time series wizard,
create new logical column, complex join.
What is level-based metrics?
Level-based
metrics means, having a measure pinned at a certain level of the dimension. A
level base measure as name state are always calculated at a certain level
of aggregation.for example if we have a measure called Amount
Sold, we can create a Level Based Measure called CategoryAmoundSold which is
Amount Sold for a Category . This measure will always return the Amount sold
for the category level even if we drill down to a lower level like subcategory
etc.
http://startobiee.blogspot.in/2013/08/level-based-measures-in-obiee_5.html
What are different types
of variables?
In
OBIEE we have two types 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 Repository Variables: This value persists, and does not change until
OBIEE Server administrator decides to change it.
Dynamic Repository Variable: 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. So, every login has its own
session variable.When a user begins a session, the Oracle BI Server creates a
new instance of a session variable and initializes it. There are two types of
session variables:
System Session Variables: System variables are session
variables that the OBIEE Server and OBIEE Web use for specific purposes.
Example, USER, LOGLEVEL, GROUP etc.
Non-system Session Variables: A system variable that
the administrator creates. Example, A non-system variable called Sales Region
that would be initialized to the name of the user’s sales region.
How to create any new
logical column in BMM layer?
Right
click on fact table ->new logical column->give name for new logical column
like
Total sales.
How to create outer join
in BMM layer?
While
creating logical join between tables you can select outer join type.
What is logging level?
Where can you set logging levels?
It is
a parameter which control the number of information that you will retrieve
in the log file from nothing (level 0 – 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.
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.
Description of Logging
Levels
Log
Level 0
|
No Logging
|
Log
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
|
Log
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
|
Log
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
|
Log
Level 4
|
Logs everything logged in Level
3
Additionally, logs the query execution plan.
|
Log
Level 5
|
Logs everything logged in Level
4
Additionally, logs intermediate row counts at various points in the execution
plan.
|
How many dimension can
we create on a table?
One Dimension
What is Authentication?
How many types of authentication ?
In
the OBIEE context, Authentication means validating the user while logging
in the OBIEE application. When a user logs in the OBIEE application a
request is sent to the BI Server asking that whether this user is a valid user
or not. When BI Server validates the user, then only the user is able to login
in the application.
OBIEE Support four types
of authentication
External
table authentication,
Database authentication,
LDAP authentication,
Oracle
BI Server User Authentication.
What is Authorization?
Authorization
means a user is authorized to view what all objects. Example, User A might be
authorized to view only particular set of reports and dashboards based on the
security applied.
How many layouts/view
are available in OBIEE?
Majorly we have four
layouts
Compound Layout,
Table Layout,
Graph Layout,
Pivot layout.
What are AGO and
TODATE function?
Where dashboards get
stored?
Under _portal folder
at Shared folder.
Difference in Navigation
and Drill down and Drill through?
Drill down means drill
down from one level to lower level of the same hierarchy
Drill through means
form one level of a hierarchy to another level from another hierarchy. (For
instance, from a Date Dimension level date to the Time Dimension level hour)
Navigation means move
from one report to another report.
What is Logical table
sources (LTS) ?
Logical table source
define the mappings from a single logical table to one
or more physical tables. A logical table may contains one or more logical table
source.
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, whereas, 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.
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?
BI server,
Presentation server; Java Host for charts; Scheduler for iBots.
How
many minimum tables are needed to pass through the Consistency Check of the
repository?
Two,One
Dim and One Fact
How to give admin
privileges in dashboard?
Settings ->
Administrator -> Manage privileges -> Manage dashboards.
What is Initialization
Blocks?
Initialization
blocks are used to initialize dynamic repository variables, system session
variables, and non-system session variables. For example, the NQ_SYSTEM
initialization block is used to refresh system session variables. Init blocks
are used for instantiating a session when a user logs in.
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.
You
can see the SQL in the C:\OracleBI\server\Log\NQQuery.log.
What is Cache? How does
it works?
When
data is accessed, a copy is stored in separate memory called cache , Next time
CPU looks for information, it first checks the cache. If data is there called a
hit , retrieval is faster from the cache memory .If it is not a hit, then
it accesses system memory, puts a copy of the new data in the cache, and
processes the information. Disk caching and memory caching significantly
improves the overall speed of the computer.
What is Cache
management? What are possible cache methods in OBIEE?
Monitoring
and managing the cache is cache management.
1) No Cache: 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.
2) Manual Cache
Management(Using Cache Manager) : Go to Administration Tool-> Manage->cache
3) Automatically(Using
Event 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.
4)
Programmatically(Purging and Maintaining Cache Using ODBC Procedures) :
Call
SAPurgeAllCache(), Call SAPurgeCacheByTable(), Call
SAPurgeCacheByQuery()
Please share your feedback and keep me posted.
have a good day!!
Thanks
