Q. What
is Event Polling Table
A:Event
Polling Table is serves as a message bus. You put the names of the tables for
which the data has changed. The OBIEE server polls this table for update
events and purges cache entries that reference those tables. It also
removes those entries from the polling table – so avoid purging them at the
next polling interval.
Oracle
BI Event Tables
Oracle
BI Event tables Utility allows us to Identify a table as an Oracle BI Event
Polling table. An Event pulling table is a way to notify the Oracle BI Server
that one or more physical tables have been updated.
To
mark the table object as an Event Polling Table
1 Click on the Tools > Utilities menu item.
2 Select the option Oracle BI Event Tables from the list of options.
3 Click Execute.
4 Select the table to register as an Event Table and click the >> button.
5 Specify the polling frequency in minutes, and click OK.
The default value is 60 minutes.
1 Click on the Tools > Utilities menu item.
2 Select the option Oracle BI Event Tables from the list of options.
3 Click Execute.
4 Select the table to register as an Event Table and click the >> button.
5 Specify the polling frequency in minutes, and click OK.
The default value is 60 minutes.
Q.How would you come to know about the updations
in tables present in physical layer of
repository, after ETL has been executed?
A:we have to write the triggers so that we
can get the name of the objects gets stored in event polling table.
Q. With
LDAP authentication implemented, how admin password can be reset? (In
repository or in LDAP server?)
A:It can be reset IN LDAP server
Q.Is it
possible to have presentation server pointing to more than one BI servers?
A:yes
Q:What are the specific tasks we can perform on repository only in
online mode?
A:You can make changes in the RPD file and push in
changes which will be immediately visible to the users who are already connected.
This feature we may use in production environment
--- cache
--- set the log level
--- any minor work
Q:What are the spesific tasks we can perform on repository only in
offline mode?
A:development of repository
creating users
Q:what are Database Hints?
A:Database hints are instructions placed
within a SQL statement that tell the database query optimizer the most
efficient way to execute the statement. Hints override the optimizer's
execution plan, so you can use hints to improve performance by forcing the
optimizer to use a more efficient plan.
Hints are database specific. The Oracle BI Server supports hints only for Oracle 8i, 9i, and 10g servers.
Hints are database specific. The Oracle BI Server supports hints only for Oracle 8i, 9i, and 10g servers.
Q:Different ways to purge the cache.
A:Purging cache is the process of deleting the entries
from the query cache. In Siebel Analytics you can purge the cache entries in
the following ways:
a)
Manually, using the Administration tool cache manager facility (in
online mode)
b) Automatically, by setting the Cache persistence time field in the physical table dialog box for a particular table.
c) Automatically, by setting the BI server event polling table.
d) Automatically, as the cache storage space fills up.
Q:Use of aliases tab (Presentation table)
A:OBIEE uses aliases in presentation layer in order to
avoid report failure whenever a source column or source table name changes in
the repository.
2.
Create an alias
Automatically
-
For a Table
A table alias is created automatically when the table’s name is changed only and exclusively by taking the following actions: right click -> rename; if a table’s name changes by taking a different action, the alias is not created automatically.
A table alias is created automatically when the table’s name is changed only and exclusively by taking the following actions: right click -> rename; if a table’s name changes by taking a different action, the alias is not created automatically.
For
a column
A column alias is created automatically when the column’s name is changed (double click -> deselect “Use Logical Column name” -> specify a new name):
A column alias is created automatically when the column’s name is changed (double click -> deselect “Use Logical Column name” -> specify a new name):
Manually
An
alias can be created manually through the “Aliases” tab in the
table/column properties (double click on the item or right click
-> Properties).
Q:what are the ways to avoid circular joins
A:aliases are required to workaround the circular join
condition....OBIEE dosent allow the circular join ...so we use the alias tables
to break the join by placing a new alias table in place
Q:What is connection pool and how many
connection pools did you have in your last project?
A:connection pool is needed for every physical database.
o It contains information about the connection to the database, not the database itself.
o Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass
through .
o We can have multiple connection pools to incraese query performance
o It contains information about the connection to the database, not the database itself.
o Can use either shared user accounts or can use pass-through accounts -Use: USER and PASSWORD for pass
through .
o We can have multiple connection pools to incraese query performance
Q:Did you create any new logical column in
BMM layer, how?
A:Yes. We can create new logical column in BMM layer.
o Example: Right click on fact table -new lgical 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.
o Example: Right click on fact table -new lgical 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.
Q:What is Object and Data Level Security?
A:Object level security
o
There are two types of object level security: Repository level and Web level
o Repository level : In presention layar we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
o web level:thisprovides security for objects stored in the siebel anlytics web catlog,such as dashboards,dashboards pages,folder,and reportsyou 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.
o Repository level : In presention layar we can set Repository level security by giving permission or deny permission to users/groups to see particular table or column.
o web level:thisprovides security for objects stored in the siebel anlytics web catlog,such as dashboards,dashboards pages,folder,and reportsyou 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.
Data
level security
o
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 alll regions, while a sales representative for a
particular region sees onlu datafor that
Q:Did you work on a stand alone Siebel system
or was it integrated to other platforms?
A:Stand alone
Q:If you want to limit the users by the
certain region to access only certain data, what would you do?
A:using session variable
Q:Can you have multiple data sources in
OBIEE?
A:yes
Q:How do you deal with case statement and
expressions in OBIEE?
A:use expression builder to create case when…then.. end
statement
Q:Do you know about Initialization Blocks?
Can you give me an example where you used them?
A: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.
To create dynamic variable you have to create IB to write sql statement.
Q:what is the full form of rpd?
A:There is no full form for rpd as such, it is just a
repository file (Rapidfile Database)
Q:if you navigate to a particular request
then how do you navigate back?
A:using guided navigation link
Q:How do you manage the scheduling the cache
using job manager?
seeding
of cache
Seeding
is the process of prepopulating the cache with queries that are known to
generate cache hits.
•Helps
to improve query performance
–Use
queries that heavily consume database processing and are likely to be reused.
•Is
performed by running prebuilt queries during off hours or immediately after
purging
–Manually
in Answers
–Automatically
using Oracle BI Delivers to schedule queries to run at a specified time
Seeding
the cache is the process of prepopulating the cache with queries that are known
to generate cache hits. One of the main advantages of seeding the cache is the
improvement of query performance. A good strategy, therefore, is to seed the
cache during off hours by running queries and caching their results. A good
seeding strategy requires knowing when cache hits occur so that you can seed
the cache with the appropriate queries. The best queries to seed the cache with
are queries that heavily consume database processing resources or that are
likely to be reused. For example, seed the cache with queries that have many
joins or a great deal of sorting, or with queries that is used frequently
throughout the business day. Be careful not to seed the cache with simple
queries that return many rows and require very little database processing.
Q:What is the difference between materialized
view and view?
Materialized
View:
A
materialized view is like a query with a result that is materialized and stored
in a table. When a user query is found compatible with the query associated
with a materialized view the user query can be rewritten in terms of the
materialized view. This technique improves the execution of the user query
because most of the query result has been precomputed. The query transformer
looks for any materialized views that are compatible with the user query and
selects one or more materialized views to rewrite the user query.
A
materialized view is a database object that contains the results of a query.
They are local copies of data located remotely, or are used to create summary
tables based on aggregations of a table's data.
A
materialized view is a stored summary containing precomputes results
(originating from an SQL select statement).
As
the data is precomputed, materialized views allow for (seemingly) faster
dataware query answers
View:
In
the repository, opaque views appear as tables in the physical
layer but the view does not actually exist.
Q:Is it possible to drill from one dimension
heirarchy to other? if Yes then dimension must have any relation or not?
A:Yes it is possible.For this there is no need to have
any relation between dimensions.
Q:How many logs files You used? What is name
of the log files? What is use of NQQUERY. Log and NQSERVER.Log?
A:There are
NQQuery,NQServer,MQSchedular,NQCluster,NQSAdminTool log files.
--NQQuery.log file shows
Physical SQL Query, logical execution plan where Usage tracking
does not have this information.
--NQServer File contains when
server is start or stop,which subject area is loaded,
how many % of cache is been used.
Q:we have 2 measures in request can we show 1
at X axis & other at Y axis?
A:yes..
Q:What do you mean by chronological key?
A:chronological key is used in time dimension function to
uniquely identify data at particular level to compare previous data
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 dimenion. 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
INTERMEDIATE QUESTIONS
Q: How we are going to decide which schema we are going to
implement in the data warehouse?
A: One way is what is mentioned in Question above.If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organization
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organization would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.
A: One way is what is mentioned in Question above.If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organization
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organization would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.
Q: Major Challenges You Faced While Creating the RPD??????
A:Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. The type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lots of problems with the date formats also.
Q: What is Guided Navigation?
A: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.
A: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.
Q: How the users Created Differs From
RPD/Answers/Dashboards Level?
A:RPD users can do administrator tasks like adding new data source, create hierarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyze the dashboard or can edit dashboard by adding/removing charts objects.
A:RPD users can do administrator tasks like adding new data source, create hierarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyze the dashboard or can edit dashboard by adding/removing charts objects.
Q:Where are passwords for userid? Ldap,external
table authentication stored respectively?
A:ldap-- ldap server,external table --external table
columns
Q:what is Ragged hierarchy? and how do u
manage it
A: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.
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.
Q:What is the difference between Single
Logical Table Source and Multiple Logical Table Sources?
A: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.
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.
Q:If we have 20 pages in one dashboard then all pages are shown in one line can we make 2 line which contain 10 pages each?
A:---yes.
---Go
in portal.css file and
a)copy tabdimcell and paste
it.rename it to tabdimcell2
b)copy tabhicell and paste
it.rename it to tabhicell2
---then
on each dashboard page add html script.
Q:Can you let me know how many aggregate
tables you have in your project? On what basis have you created them?
A: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.
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.
Q:How do you know which report is hitting
which table, either the fact table or the aggregate table?
A: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.
Q: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?
A:in ui--manage sessions--view log
Q:Have you heard of Implicit Facts? If, so
what are they?
A: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.
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.
Q:If you have 3 facts and 4 dimension and you
need to join would you recommend joining fact with fact? If no than what is the
option?
A:In the BMM layer, create one logical
table (fact) and add the 3 fact table as logical table source
If you have dimension table like customer, item, time and fact table like sale and if you want to find out how often a
customer comes to store and buys a particular item, what will you do?
o write a query as SELECT customer_name, item_name, sale_date, sum(qty) FROM customer_dim a, item_dim b,
time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key = b.item_key AND d.time_key = c.time_key
GROUP BY customer_name, item_name, sale_date
If you have dimension table like customer, item, time and fact table like sale and if you want to find out how often a
customer comes to store and buys a particular item, what will you do?
o write a query as SELECT customer_name, item_name, sale_date, sum(qty) FROM customer_dim a, item_dim b,
time_dim c, sale_fact d WHERE d.cust_key = a.cust_key AND d.item_key = b.item_key AND d.time_key = c.time_key
GROUP BY customer_name, item_name, sale_date
Q: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?
A:Using levelbase matrics.
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
Q:What is time series wizard? When and how do you use it?
Q:What is time series wizard? When and how do you use it?
A: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
o Identify the time periods need to be compared and then period table keys to the previous time period.
o The period table needs to contain a column that will contain Year Ago information.
o The fact tables needs to have year ago totals.
o To use the Time series wizard. After creating your business model right click the business model and click on Time
Series Wizard.
o The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
o The Time Series Wizard prompts you to select the period table used for the comparison measures
o 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.
o 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.
o 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
o In the General tab of the Logical table source etc you can find Generated by Time Series Wizard in the description
section
o Then you can add these comparision measures to the presentation layer for your reports.
o Ex: Total sales of current qtr vs previous qtr vs same qtr year ago
month or week and day also
o Identify the time periods need to be compared and then period table keys to the previous time period.
o The period table needs to contain a column that will contain Year Ago information.
o The fact tables needs to have year ago totals.
o To use the Time series wizard. After creating your business model right click the business model and click on Time
Series Wizard.
o The Time Series Wizard prompts you to create names for the comparison measures that it adds to the business model.
o The Time Series Wizard prompts you to select the period table used for the comparison measures
o 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.
o 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.
o 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
o In the General tab of the Logical table source etc you can find Generated by Time Series Wizard in the description
section
o Then you can add these comparision measures to the presentation layer for your reports.
o Ex: Total sales of current qtr vs previous qtr vs same qtr year ago
Q:What happens when a Foreign Key Join is defined in
BMM Layer?
A:If a
physical join (foreign key join) is used in the BMM layer, then BI Server will
always select this physical join to create the SQL even when it is not the most
efficient one.
Q.What happens when a Complex Key Join is defined in Physical
Layer?
A:If you
have to join two tables in physical layer and the data type of the columns on
which you have to join is not same then what we would complex join
e.g cast(A.column_name as NUMBER) =
B.column_name
Q.What
will happen if we removed all the Logical complex joins?
A:[NQSERROR:15001]could
not load navigation space for subject area.
Logical table
doesnot join to anyother logical table
Q.What
is difference between column filter prompt and image filter prompt?
A:An image
prompt provides an image that users click to select values for an analysis or
dashboard.
For example, in a sales
organization,users can click their territories from an image of a map
to see sales information, or click a
product image to see sales information about
that product.
If you know how to use the HTML
<map> tag,then you can create an image map definition. For more
information about creating an image prompt, see "Creating or Editing an
Image Prompt".
Column Filter
Prompt is a prompt,it is used provide a general filtering of a column in a
request.we can set all choice or constrained choice for a column.it can be
combined with other column filter prompt. A
column filter prompt provides general filtering of a column in a request. A
column filter prompt can present all choices for a column or it can present
constrained choices.
Q.Can we
use the column of two different Subject area in a single request?
A:Yes,using
combine with similar request.
Q.How
will you merge two catalog folders?
A:From
catalog manager
Merge
Development with production
using EBA Catalog
Q.How many
folders get created when you install OBIEE?
A:OracleBI,OracleBIData
Q.How to move repository from development to production environment?
A:copy the rpd file rom development to production.
Q.You
have a request on the dashboard which contains two columns say country and
dollars and you want to see dollar related to particular country only, how will
u implement this scenario?
A:we
will make a dashboard prompt of country and by selecting a country from that
prompt we will be able to the dollars related to that particular country.
Q.On which port
schedular is configured
A:9705
Q.How
database authentication is performed.
A:In NQSconfuig file # is placed in front of
set Authentication_type='Database',removed that # and give the database name
also.
Q.Which databases
have you worked
A:Oracle,sqlserver
Q. Can you write
PL/SQL
A:Yes
Q. Do you know sub
queries? What are the correlated sub queries?
A:Sub
Query
A sub query is a query that
lives inside a query. By query of course we mean a SELECT statement.In sub
query the inner query is first executed, and the result
is then fed into the outer query.
Correlated Sub Query:
A query is called correlated
subquery when both the inner query and the outer query are interdependent. For
every row processed by the inner query,
the outer query is processed as well. The inner query depends on the outer
query before it can be processed.
Q.What is Indexing?
A:When
there are thousands of records in a table, retrieving information will take a
long time. Therefore indexes are created on columns which are accessed
frequently, so that the information can be retrieved quickly. Indexes can be
created on a single column or a group of columns. When a index is created, it
first sorts the data and then it assigns a ROWID for each row.
Syntax
to create Index:
CREATE
INDEX index_name
ON table_name (column_name1,column_name2...);
ON table_name (column_name1,column_name2...);
Q. Tell me how
exactly does indexing works.
A:Without
an index, the database system reads through the entire table (this process is
called a 'table scan') to locate the desired information. With the proper index
in place, the database system can then first go through the index to find out
where to retrieve the data, and then go to these locations directly to get the
needed data. This is much faster.
Q.What is the
difference between Union and Union All?
A:union is used to select distinct values from two tables
where as union all is used to select all values including duplicates from the
tables
Q.What do you mean by surrogate
key?
A:When
creating a dimension table in a data warehouse we generally create the tables
witha system generated key to unqiuely identify a row in the dimension. This
key is also known as a surrogate key. The surrogate key is used as the primary
key in the dimension table. The surrogate key will also be placed in the fact
table and a foreign key will be defined between the two tables.
Surrogate
keys are system generated keys. They are integers.Surrogate keys are extremely
useful when having type 2 data ( i.e. storing historical information) For ex:
Consider one has a table in which a person and his location are stored. Now
when his location is changed and we want to keep a historical record of the
same it is stored with a surrogate key that will help us to uniquely identify
the record. This is also a reason that OLTP keys are not used in the warehouse
and a seperate dimension or surrogate key is maintained.
Q.What is the
difference between Primary Key and surrogate key?
A:Primary Key: A
column in a table whose values uniquely identify the rows in the table. A
primary key value cannot be NULL.
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.
Unique Key: Unique Keys are used to uniquely identify each row in an Oracle table. There can be one and only one row for each unique key value.
Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.
Q.Assume a scenario where I want to purge the cache as soon as the
ETL completes. How could this be achieved in
OBIEE
A:Using
event polling table
Q.What is Row Wise Initialization block?
A:The
row-wise initialization allows us to create and set session variables
dynamically. It is similar to external table authentication
except here we have fixed number of columns (name, value) and each row for a
particular user represents different name (variable name) and associated value
(column value).
It allow to create session variable dynamically and set their
values when session starts .
Name and value of session variable reside in external table that access through connection pool
Name and value of session variable reside in external table that access through connection pool
Q:What all objects are get created after
running time series wizard.
A:1)Ago Function--->Calculates aggregated value as of
some time period shifted from current time.
ago(<measure>,<time
level>,<number to shift>)
2)Todate--->Aggregates a measure attribute from the beginning
of a specified time period ti the currently displayed time.
ToDate(<measure>,<time
level>)
EXPERT QUESTIONS
Q: Where to Configure the Scheduler?
A: I am not sure if I am correct but we configure the OBIEE scheduler in database.
A: I am not sure if I am correct but we configure the OBIEE scheduler in database.
Q.How
will you restrict the number of records displaying in your BI Answers?
A:Manage
security-users-permissions-query limits
Q:What
is the concept of Bridge table?
A:M:m is not allowed in bmm then to
solve it we use bridge table in bmm.
In that we craete a new column and in that we add the
primary keys of both the tables in new
column and give the join as 1:m
Q.What
are uses of Content Tab of LTS?
A:In
case of aggregation navigation and fragmentation it tells the oracle bi server
which lts to use.in content tab we set the logical level for a dimensional
level
Q.What is
the use of BINS?
A:Bins are
used if one wants to see data comparatively for a particular month itself
For eg. For february month we can create bin as first half
and second half and get compared.
Q.If you are using 4 prompts in a
report and values of two prompts are entered same then can you have a pop up
window which shows an error message? Is it possible, how?
A:Oracle
bi data//app//res//mozzila//js file modify
Q.I want
to hide request on a dashboard page,how can I do this?
A:We can
hide the request on a dashboard page,by adding javascript....
There
are two ways of adding JS into the OBIEE dashboard:
If
the script is for one page only - Enter the dashboard editor and
edit your page. Add a new Text object from the bar on the left. It doesn't
matter where you add it in the page. Click on its Properties button and write
your script in the text box. Don't forget to start it with <script> and
end it with </script>. Check the "Contains HTML Markup" box.
There is no need to hide this object because it doesn't contain anything
visual. Rename the text object and give it a descriptive name.
If
the script is common to more than one page or may be in
the future - Create a new request which contains only one column.
It can be any column from any table, but I recommend you to use a column which
doesn't have many rows (you can use a small dimension if you have one). Click
on the Results tab and enter the Narrative view. Write your script in the
Narrative text box. Like before, don't forget to start it with <script>
and end it with </script>. Check the "Contains HTML Markup" box
and enter zero in the "Rows to display" text box. You can save your
request now. Every dashboard page that contains this request will run the JS
you just written.
How
to develop correctly with JS
in OBIEE
Don't enter bunch of code in the request. It'll only get you messy once the length of the code will grow. Your code inside the dashboard should only call functions. For example, it can look like this:
<script>
if (myFunction)
{
myFunction();
}
if (mySecondFunction)
{
mySecondFunction();
}
</script>
Don't enter bunch of code in the request. It'll only get you messy once the length of the code will grow. Your code inside the dashboard should only call functions. For example, it can look like this:
<script>
if (myFunction)
{
myFunction();
}
if (mySecondFunction)
{
mySecondFunction();
}
</script>
You will write
and maintain these functions in external JS file. Take the common.js file
(search it under the OBIEE dir in the server) which is called in
every page in OBIEE dashboards (don't forget to backup first!!)
and add your code in the bottom of the file. Now you have a convenient place
where you can edit your JS code. There is one last thing you need to know
before proceeding - the browser caches the JS files so you'll need to clear the
browser's cache every time you change your code. In Internet Explorer, you can
do it in Tools -> Internet Options -> Delete Files -> Delete all
offline content. You'll do it a lot while developing, so having a button that
does that will be great. You can use Microsoft's IE developer toolbar or another developer toolbar.
Q.There
are 400 values to be displayed in dashboard propmt,but if I select dashboard
prompt type to be drop-down then at the max I can see 256 values,so I want to
know what is the solution for this.
A:OracleBIdatawebconfiginstanceconfig.xml
·
insert the following piece of code
<prompts><maxdropdownvalues>1000</maxdropdownvalues></prompts>.
The value ’1000′ can be any value as per your need.
·
Save
the file and restart your presentation server.
Q:Oracle doesn’t recommend Opaque Views
because of performance considerations, so why/when do we use them?
A: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
Q:How do you implement security using
External Tables and LDAP?
A:External Tables
Instead
of storing user IDs and passwords in a
Server repository, you can maintain lists of users and their passwords
in an external database table and use this table for purposes. The external database table
contains user IDs and passwords, and could contain other information, including
group membership and display names used for Siebel Analytics Web users. The
table could also contain the names of specific database catalogs or schemas to
use for each user when querying data
LDAP
o Instead of storing user IDs and passwords in a Server repository, you can have the
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.
o Instead of storing user IDs and passwords in a Server repository, you can have the
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.
Q:How can i disable cache for only 2
particular tables?
A:Click on table and disable cacheable option in physical
layer.
Q:what are slowly changing dimensions?
A: --The Type
1 methodology overwrites old data with new data, and therefore does not track historical data at all.
--The Type 2
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.
--The Type 3
method tracks changes using separate columns. Whereas Type 2 had unlimited history preservation,
Type 3 has limited history preservation.
--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.
--The Type 6 method
combines the approaches of types 1, 2 and 3 (1 + 2 + 3 = 6).
Q: How you generally Approach to your Analytics Project?
A: Any project should start from defining the scope of the project and the approach
should be not to deviate from the scope. Then the project should be functionally divided into smaller modules generally done by project managers along with technical and functional leads.
The functional leads then decide on majorly three things:
1. According to the defined scope of the project they start gathering requirements while
interacting with the clients.
2. They had a discussion with the technical leads and try to reach a solution.
3. Technical leads will decide what schemas to create and what requirements are going to
fulfill by that schema. Technical leads discuss all this with the developers and try to close requirements. Simultaneously testing and deployment is planned in a phased manner.
Q: How we are going to decide which schema we are going to implement in the data warehouse?
A: One way is what is mentioned in Question above.
If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organization
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organization would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.
Q: What are the Challenges you faced while making of Reports?
A: Making of a report has never been a difficult task. But problem comes when users
are reluctant to adopt a new system. I have experienced that if you are not able to create
the report in exactly the way they used to see, they will keep asking for the changes. Your
approach should be to first show them what they want to see and then add more information
in the report.
Q: What you will do when your Report is not Fetching Right Data?
A: this is the biggest problem in report creation and verification. There could be two
reasons for report not fetching the right data.
1. Mostly clients do not have correct data in their database and on top of that to correct
the results they make some changes at the report level to bring the desired result which
you may not e aware of while creating the reports. Clients try to match the data with their
existing reports and you never get the correct results. You try to discover the things and
at later stage come to know of all these problems and you are held responsible for this
delay. Hence always consult the SPOC (Single Point of Contact) and try to understand the
logic they have used to generate their reports.
2. If the database values are correct, there could be a problem with the joins and
relations in the schema. You need to discover that analyzing and digging deep into the
matter.
Q: How analytics Process Your Request When you create your Requests?
A:If the Question means how does Oracle BI Analytics Server processes the user requests, the answer is- Oracle BI server converts the logical SQL submitted by the client into optimized physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc.
Q: From where u Get the Logical Query of your Request?
A: The logical SQL generated by the server can be viewed in BI Answers. If I have not understood the question, Please raise your voice.
Q: Major Challenges You Faced While Creating the RPD??????
A:Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. The type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lots of problems with the date formats also.
Q: What is Global Filter and how are they different from Column Filter?
A: Column filter- simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- Not sure. I understand this filter will have impact on across the application but I really don’t understand where and how it can be user. I heard of global variables but not global filters.
Q: How to make the Delivery Profilers Work?
Q: When we are Use SA System how Does SA Server understand that it needs to use it For Getting the User Profile information?
Q: Where to Configure the Scheduler?
A: I am not sure if I am correct but we configure the OBIEE scheduler in database.
A: Any project should start from defining the scope of the project and the approach
should be not to deviate from the scope. Then the project should be functionally divided into smaller modules generally done by project managers along with technical and functional leads.
The functional leads then decide on majorly three things:
1. According to the defined scope of the project they start gathering requirements while
interacting with the clients.
2. They had a discussion with the technical leads and try to reach a solution.
3. Technical leads will decide what schemas to create and what requirements are going to
fulfill by that schema. Technical leads discuss all this with the developers and try to close requirements. Simultaneously testing and deployment is planned in a phased manner.
Q: How we are going to decide which schema we are going to implement in the data warehouse?
A: One way is what is mentioned in Question above.
If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organization
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organization would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.
Q: What are the Challenges you faced while making of Reports?
A: Making of a report has never been a difficult task. But problem comes when users
are reluctant to adopt a new system. I have experienced that if you are not able to create
the report in exactly the way they used to see, they will keep asking for the changes. Your
approach should be to first show them what they want to see and then add more information
in the report.
Q: What you will do when your Report is not Fetching Right Data?
A: this is the biggest problem in report creation and verification. There could be two
reasons for report not fetching the right data.
1. Mostly clients do not have correct data in their database and on top of that to correct
the results they make some changes at the report level to bring the desired result which
you may not e aware of while creating the reports. Clients try to match the data with their
existing reports and you never get the correct results. You try to discover the things and
at later stage come to know of all these problems and you are held responsible for this
delay. Hence always consult the SPOC (Single Point of Contact) and try to understand the
logic they have used to generate their reports.
2. If the database values are correct, there could be a problem with the joins and
relations in the schema. You need to discover that analyzing and digging deep into the
matter.
Q: How analytics Process Your Request When you create your Requests?
A:If the Question means how does Oracle BI Analytics Server processes the user requests, the answer is- Oracle BI server converts the logical SQL submitted by the client into optimized physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc.
Q: From where u Get the Logical Query of your Request?
A: The logical SQL generated by the server can be viewed in BI Answers. If I have not understood the question, Please raise your voice.
Q: Major Challenges You Faced While Creating the RPD??????
A:Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. The type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lots of problems with the date formats also.
Q: What is Global Filter and how are they different from Column Filter?
A: Column filter- simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- Not sure. I understand this filter will have impact on across the application but I really don’t understand where and how it can be user. I heard of global variables but not global filters.
Q: How to make the Delivery Profilers Work?
Q: When we are Use SA System how Does SA Server understand that it needs to use it For Getting the User Profile information?
Q: Where to Configure the Scheduler?
A: I am not sure if I am correct but we configure the OBIEE scheduler in database.
Q: How to hide Certain Columns from a User?
A: Application access level security- Do not add the column in the report, do not add the column in the presentation layer.
Q: How can we Enable Drills in a Given
Column Data?
A: To enable Drill down for a column, it should be included in the hierarchy in OBIEE. Hyperion IR has a drill anywhere feature where don’t have to define and can drill to any available column.
Q: Is Drill down Possible without the attribute being a Part of a Hierarchical Dimension?
A: No
A: To enable Drill down for a column, it should be included in the hierarchy in OBIEE. Hyperion IR has a drill anywhere feature where don’t have to define and can drill to any available column.
Q: Is Drill down Possible without the attribute being a Part of a Hierarchical Dimension?
A: No
Q: How do u Conditional Format.?
A: while creating a chat in BI Answers, you can define the conditions and can apply color formatting.
Q: What is Guided Navigation?
A: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.
Q: How is Webcat File Deployed across Environment?
Q: How the users Created Differs From RPD/Answers/Dashboards Level?
A:RPD users can do administrator tasks like adding new data source, create hierarchies, change column names whereas Answers users may create new charts, edit those charts and Dashboard users may only view and analyze the dashboard or can edit dashboard by adding/removing charts objects.
Q: Online/Offline Mode how it Impact in Dev and Deployment?
A: Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment.
Offline mode- can be useful in test or development environment.
Q: Explain me the Schema in Your Last Project?
A: while creating a chat in BI Answers, you can define the conditions and can apply color formatting.
Q: What is Guided Navigation?
A: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.
Q: How is Webcat File Deployed across Environment?
Q: How the users Created Differs From RPD/Answers/Dashboards Level?
A:RPD users can do administrator tasks like adding new data source, create hierarchies, change column names whereas Answers users may create new charts, edit those charts and Dashboard users may only view and analyze the dashboard or can edit dashboard by adding/removing charts objects.
Q: Online/Offline Mode how it Impact in Dev and Deployment?
A: Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment.
Offline mode- can be useful in test or development environment.
Q: Explain me the Schema in Your Last Project?
No comments:
Post a Comment