To Retrieve duplicate records
select * from src_emp where rowid not in (select max(rowid) from src_emp group by emp_no);
For concatenation names for same dept.
select dept_no,listagg(name,';') within group (order by dept_no)
from src_emp
group by dept_no;
2nd highest salary using dense_rank() and Row_number()
select * from (select e.*, dense_rank() over (order by sal desc) as D from table name e) where D=2;
select * from (select e.*, row_number() over (order by sal desc) as R from table name e) where R=2;
What is Cache Management?
Monitoring and managing the cache is called cache management.
How many types of Caches we have in OBIEE?
In OBIEE we have 2 types of Cache:
1.Oracle BI Presentation Server Cache
2.Oracle BI Server Cache
Could you please tell me what is the report order of execution and where report result will be stored actually?
The report order of execution is
1. OBIPS cache
2. OBIS cache
3. DB cache
And the report result will be stored in OBIPS cache and OBIS cache
What is advantage of Cache Management?
We are able to improve the Performance by reducing time to execute it and reducing time to create the physical sequel.
Let me know one thing actually, what are the differences did you observed running a report twice at the time of cache?
Running a Report First Time:
1. Logical SQL based on RPD BMM layer
2. Physical SQL based on Physical layer joins
3. This Physical SQL executing Connection Pool corresponding DB
4. Result showing to user and also it stores result in OBIPS Cache and OBIS
Cache
Running a Report Second Time:
1. Logical SQL based on RPD BMM layer
2. The logical SQL corresponding cache will be shown to the user
How to skip OBIPS cache for particular report?
Advance Tab: By pass OBIPS cache enable
How do I disable cache for only two particular tables?
In physical layer, right click on the table there we will have the option
Which says cacheable and here you have to deselect the check box
What is cache management? How do you implement auto cache management?
Cache Management:
?Process to maintain cache for performance and update at certain
Duration
?Auto cache management can be attained by setting Event Pooling Table
Could you please tell me how do you purge cache by subject area or cache by query?
Admin Tool: Manage?Cache?Purge
You can purge all the cache
Let us assume we have two reports on that one report is cacheable and other is not cacheable, how the request will happened?
? The request will not be cached.
What is advantages of cache?
1. Saves the result of queries in cache files
2. Enable OBIS to satisfy subsequent query request without having to
Access back-end databases
3. Less network Traffic
4. Improves Performance
What is Disadvantages of cache?
1. There is chance to get stale data.
2. Memory wasted
What is the use of Enable BI server cache?
Enabling the server cache can greatly improve performance by enabling
Users who share data visibility to retrieve row sets from queries that
Have already been run at the loss of the Possibility of seeing stale data.
If the cache is shareable among all the users are not?
? Yes
Tell me some of the parameters did you observed in cache?
User, Use count, last used, Repository, Business Model etc.
Tell me one thing I have two users X1 and X2 and I want to purge a U1
Related cache how it is?
?Cache manager window?Select required caches and purge
I have run a three reports i.e. Customers, Products, calendar with orders Fact and then I want to delete customer table cache, how it is?
Cache manger window?Physical tab?Navigate to customer table
?Edit?Purge
Note: Notice that the customer cache will be deleted.
What is purging?
The process of removing query cache entries is called purging.
What are the purging methods?
1. Manual.
2. Persistency
3. Event Pooling Table
4. ODBC functions
What is manual Purging?
For this manual Purging human interaction is required
What is Persistency time purging?
It is useful to purge cache based on fixed time. To provide this setting we
need to know accurately the tables refresh frequency.
What is query caching?
It is used to save the result of query in cache file and reuse for similar Queries.
What are the advantages of query caching?
1. Improve query performance
2. Less network traffic
3. Reduce DB processing
4. Reduce OBI server processing
What is cache Hints?
A cache hints means that the server was able to use cache to answer the
query and did not got to the database at all.
How you are doing the cache purging in your Project?
In my project all the ETL jobs are running every day mid night, so that in
my project we set the persistency time for all physical tables one day.
When will do the cache purging?
If new data comes into Data warehousing then we need to cache purging
Otherwise the report don’t shoe the new data. If the report hitting the
Cache.
How to disable cache?
1. Login to EM
2. Click on Core-Application under BI
3. Click on capacity management tab
4. Click on Performance sub tab
5. Click on Lock and edit configuration
6. Deselect the cache enable check box
7. Click on apply button
8. Click on activate changes button
9. Restart the services
What is the use of LOG-LEVEL?
The amount of log information to be specifies for a user, generally we
can use level-2
How many ways to do cache purging?
Manually: In Admin tool by using the cache manage to purge the cache
entries.
Automatically: We have four types of ODBC functions, those are useful
to purge cache programmatically & those functions are
1. SA purge all cache.
2. SA purge cache by db.
3. SA purge cache by table.
4. SA purge cache by query.
It is an accurate method to purge the cache. Normally these
functions will be executed by ETL-team using their post load option.
What is Presentation server cache?
1. The presentation server caches the result of the queries OBIPS users the
request key and logical SQL string to determine if subsequent queries can
use cache result.
2. It will be unique for Particular Session
What is SEED-CACHE?
1. Inserting cache into cache folder is called seed cache.
2. Seeding is the process of pre calculating the cache with queries that are
Known to generate cache hits.
3. Helps improve query performance.
Cloud you please tell me, how many ways to purge the cache in Presentation services?
1. Refresh Icon Dashboard
2. Clear my selections
3. Manage session?session management ?close all cursors
What type of purging methods you are used in Project?
1. ODBC Functions (Programmatically)
2. Event Pooling Table
Note: Tell them to above two methods first priority goes to ODBC
What is Difference between Persistency time and EPT?
In EPT we have the new records then purging happened, whereas the above
Persistency is time fixed.
What is Difference between EPT and ODBC functions?
In EPT stale data is available to overcome this problem we will go for ODBC functions.
What is Manual Seeding?
Navigate to the report in the catalog ?click on the report ?then
automatically cache will be created.
Could you please tell me, how will you perform EPT in your project?
1. Import table S_NQ_EPT in physical layer
2. Defining table S_NQ_EPT as event Pooling table.
What are the parameters enabled while cache time?
1. Enable BI server cache enable, maximum cache entry size=20,
Maximum cache entries=1000
2. Global cache, global cache path, global cache size
I want to disable the cache entire environment?
If you want to disable OBI PS cache for entire environment,
We need to change tag in INSTANCECONFIG.XML file LOCATION:instance\instance1\config\oracle BIPS component\coreapplication_obips1\instanceconfig.xml file
<Server Instance>
___________________
<cursors>
<ForceRefresh>True/ForeceRefresh>
</cursors>
__________________
<server instance>
While generating a report I don’t want to read the OBIPS cache? How you will do this?
If we click report refresh button it will not read OBIPS cache, directly it
will go for OBIS cache
Could you please tell me what the difference between OBIPS and OBIS
cache?
OBIPS cache is temp cache and whereas OBIS server cache will not be
deleted with OBI server start or restart
select * from src_emp where rowid not in (select max(rowid) from src_emp group by emp_no);
For concatenation names for same dept.
select dept_no,listagg(name,';') within group (order by dept_no)
from src_emp
group by dept_no;
2nd highest salary using dense_rank() and Row_number()
select * from (select e.*, dense_rank() over (order by sal desc) as D from table name e) where D=2;
select * from (select e.*, row_number() over (order by sal desc) as R from table name e) where R=2;
What is Cache Management?
Monitoring and managing the cache is called cache management.
How many types of Caches we have in OBIEE?
In OBIEE we have 2 types of Cache:
1.Oracle BI Presentation Server Cache
2.Oracle BI Server Cache
Could you please tell me what is the report order of execution and where report result will be stored actually?
The report order of execution is
1. OBIPS cache
2. OBIS cache
3. DB cache
And the report result will be stored in OBIPS cache and OBIS cache
What is advantage of Cache Management?
We are able to improve the Performance by reducing time to execute it and reducing time to create the physical sequel.
Let me know one thing actually, what are the differences did you observed running a report twice at the time of cache?
Running a Report First Time:
1. Logical SQL based on RPD BMM layer
2. Physical SQL based on Physical layer joins
3. This Physical SQL executing Connection Pool corresponding DB
4. Result showing to user and also it stores result in OBIPS Cache and OBIS
Cache
Running a Report Second Time:
1. Logical SQL based on RPD BMM layer
2. The logical SQL corresponding cache will be shown to the user
How to skip OBIPS cache for particular report?
Advance Tab: By pass OBIPS cache enable
How do I disable cache for only two particular tables?
In physical layer, right click on the table there we will have the option
Which says cacheable and here you have to deselect the check box
What is cache management? How do you implement auto cache management?
Cache Management:
?Process to maintain cache for performance and update at certain
Duration
?Auto cache management can be attained by setting Event Pooling Table
Could you please tell me how do you purge cache by subject area or cache by query?
Admin Tool: Manage?Cache?Purge
You can purge all the cache
Let us assume we have two reports on that one report is cacheable and other is not cacheable, how the request will happened?
? The request will not be cached.
What is advantages of cache?
1. Saves the result of queries in cache files
2. Enable OBIS to satisfy subsequent query request without having to
Access back-end databases
3. Less network Traffic
4. Improves Performance
What is Disadvantages of cache?
1. There is chance to get stale data.
2. Memory wasted
What is the use of Enable BI server cache?
Enabling the server cache can greatly improve performance by enabling
Users who share data visibility to retrieve row sets from queries that
Have already been run at the loss of the Possibility of seeing stale data.
If the cache is shareable among all the users are not?
? Yes
Tell me some of the parameters did you observed in cache?
User, Use count, last used, Repository, Business Model etc.
Tell me one thing I have two users X1 and X2 and I want to purge a U1
Related cache how it is?
?Cache manager window?Select required caches and purge
I have run a three reports i.e. Customers, Products, calendar with orders Fact and then I want to delete customer table cache, how it is?
Cache manger window?Physical tab?Navigate to customer table
?Edit?Purge
Note: Notice that the customer cache will be deleted.
What is purging?
The process of removing query cache entries is called purging.
What are the purging methods?
1. Manual.
2. Persistency
3. Event Pooling Table
4. ODBC functions
What is manual Purging?
For this manual Purging human interaction is required
What is Persistency time purging?
It is useful to purge cache based on fixed time. To provide this setting we
need to know accurately the tables refresh frequency.
What is query caching?
It is used to save the result of query in cache file and reuse for similar Queries.
What are the advantages of query caching?
1. Improve query performance
2. Less network traffic
3. Reduce DB processing
4. Reduce OBI server processing
What is cache Hints?
A cache hints means that the server was able to use cache to answer the
query and did not got to the database at all.
How you are doing the cache purging in your Project?
In my project all the ETL jobs are running every day mid night, so that in
my project we set the persistency time for all physical tables one day.
When will do the cache purging?
If new data comes into Data warehousing then we need to cache purging
Otherwise the report don’t shoe the new data. If the report hitting the
Cache.
How to disable cache?
1. Login to EM
2. Click on Core-Application under BI
3. Click on capacity management tab
4. Click on Performance sub tab
5. Click on Lock and edit configuration
6. Deselect the cache enable check box
7. Click on apply button
8. Click on activate changes button
9. Restart the services
What is the use of LOG-LEVEL?
The amount of log information to be specifies for a user, generally we
can use level-2
How many ways to do cache purging?
Manually: In Admin tool by using the cache manage to purge the cache
entries.
Automatically: We have four types of ODBC functions, those are useful
to purge cache programmatically & those functions are
1. SA purge all cache.
2. SA purge cache by db.
3. SA purge cache by table.
4. SA purge cache by query.
It is an accurate method to purge the cache. Normally these
functions will be executed by ETL-team using their post load option.
What is Presentation server cache?
1. The presentation server caches the result of the queries OBIPS users the
request key and logical SQL string to determine if subsequent queries can
use cache result.
2. It will be unique for Particular Session
What is SEED-CACHE?
1. Inserting cache into cache folder is called seed cache.
2. Seeding is the process of pre calculating the cache with queries that are
Known to generate cache hits.
3. Helps improve query performance.
Cloud you please tell me, how many ways to purge the cache in Presentation services?
1. Refresh Icon Dashboard
2. Clear my selections
3. Manage session?session management ?close all cursors
What type of purging methods you are used in Project?
1. ODBC Functions (Programmatically)
2. Event Pooling Table
Note: Tell them to above two methods first priority goes to ODBC
What is Difference between Persistency time and EPT?
In EPT we have the new records then purging happened, whereas the above
Persistency is time fixed.
What is Difference between EPT and ODBC functions?
In EPT stale data is available to overcome this problem we will go for ODBC functions.
What is Manual Seeding?
Navigate to the report in the catalog ?click on the report ?then
automatically cache will be created.
Could you please tell me, how will you perform EPT in your project?
1. Import table S_NQ_EPT in physical layer
2. Defining table S_NQ_EPT as event Pooling table.
What are the parameters enabled while cache time?
1. Enable BI server cache enable, maximum cache entry size=20,
Maximum cache entries=1000
2. Global cache, global cache path, global cache size
I want to disable the cache entire environment?
If you want to disable OBI PS cache for entire environment,
We need to change tag in INSTANCECONFIG.XML file LOCATION:instance\instance1\config\oracle BIPS component\coreapplication_obips1\instanceconfig.xml file
<Server Instance>
___________________
<cursors>
<ForceRefresh>True/ForeceRefresh>
</cursors>
__________________
<server instance>
While generating a report I don’t want to read the OBIPS cache? How you will do this?
If we click report refresh button it will not read OBIPS cache, directly it
will go for OBIS cache
Could you please tell me what the difference between OBIPS and OBIS
cache?
OBIPS cache is temp cache and whereas OBIS server cache will not be
deleted with OBI server start or restart
No comments:
Post a Comment