OBIEE On Cloud
View more presentations from guestb9ab838.
Best practices: Have warehouse statistics as report
Today I would like to share one good practice for OBIEE administrators on warehouse statistics why they are good to collect.
It’s better to have a dashboard page with all statistics/parameters of objects which are used in building your warehouse. By this administrator can judge/analyze/enhance few data issues very quickly on broader way.
1) User complains about not able to see latest data.
2) User complains about missing data which was exists previous day or in back days, but now missing.
3) Enables what the percentage of change on particular dimension or fact or stage tables.
4) Quickly find out if dimension or fact rows got reduced.
5) list goes on :)(please feel free to add)
To do this we need to create a table in database and store statistics, update them as and when require i.e update the history tables after every ETL load or create a procedure and schedule it every day.
It’s better to have a dashboard page with all statistics/parameters of objects which are used in building your warehouse. By this administrator can judge/analyze/enhance few data issues very quickly on broader way.
1) User complains about not able to see latest data.
2) User complains about missing data which was exists previous day or in back days, but now missing.
3) Enables what the percentage of change on particular dimension or fact or stage tables.
4) Quickly find out if dimension or fact rows got reduced.
5) list goes on :)(please feel free to add)
To achieve this, I will illustrate you with one simple way and one advanced way.
Here is simple way, by make use of database dictionary objects, create a direct data base request with all required fields, for all objects used in building warehouse.
Here is simple way, by make use of database dictionary objects, create a direct data base request with all required fields, for all objects used in building warehouse.
Example: Table name, no of rows, last modified time.
SQL for above request:
select t.TABLE_NAME,t.NUM_ROWS,o.LAST_DDL_TIME
from user_objects o,user_tables t
where t.TABLE_NAME = o.OBJECT_NAME
where t.TABLE_NAME = o.OBJECT_NAME
Report look like as follows
By this administrator can judge particular table/dimension has not loaded today because of that latest data missed, if dimension rows are 0 then corresponding ETL process went for task,these kind of decision will be done easily.
 |
| From O.B.I.E.E |
By this administrator can judge particular table/dimension has not loaded today because of that latest data missed, if dimension rows are 0 then corresponding ETL process went for task,these kind of decision will be done easily.
Now little complex approach:
Here we maintain history of volume and will display this history table by direct database call, by doing this we can quickly identify if volume got reduced or volume did increased proportionately what we expect per ETL load/day/week etc.
Here we maintain history of volume and will display this history table by direct database call, by doing this we can quickly identify if volume got reduced or volume did increased proportionately what we expect per ETL load/day/week etc.
 |
| From O.B.I.E.E |
To do this we need to create a table in database and store statistics, update them as and when require i.e update the history tables after every ETL load or create a procedure and schedule it every day.
My recommendation would be maintain statistics in history table, for current day stats, amount of previous day to current day change, amount of last 7 days change, and last 30 days. So that if some tables are growing exponentially, we can fine tune them in well advance.
Here are sample scripts which I have used in above example.
CREATE TABLE SH.DBSTATS as
CREATE TABLE SH.DBSTATS as
select t1.TABLE_NAME ,
t1.NUM_ROWS as YESTERDAY_NO_ROWS,
t1.NUM_ROWS as TODAY_NO_ROWS
from user_tables t1
Here update/insert statement.
Here update/insert statement.
MERGE INTO SH.DBSTATS D
USING user_tables today
ON (today.TABLE_NAME = D.TABLE_NAME )
WHEN MATCHED THEN
UPDATE
SET
D.YESTERDAY_NO_ROWS = d.TODAYS_NO_ROWS,
d.TODAYS_NO_ROWS=today.NUM_ROWS
WHEN NOT MATCHED THEN
INSERT
USING user_tables today
ON (today.TABLE_NAME = D.TABLE_NAME )
WHEN MATCHED THEN
UPDATE
SET
D.YESTERDAY_NO_ROWS = d.TODAYS_NO_ROWS,
d.TODAYS_NO_ROWS=today.NUM_ROWS
WHEN NOT MATCHED THEN
INSERT
(D.Table_name,d.YESTERDAY_NO_ROWS,d.TODAYS_NO_ROWS)
VALUES
VALUES
(today.Table_name,today.NUM_ROWS,today.NUM_ROWS);
commit;
Note: By pass presentation cache.
commit;
Note: By pass presentation cache.
Explained every thing with Oracle technology.
Hope it helped you
Hope it helped you
Happy weekend :)
Srinivas Malyala
Unexpected query string(obiee url) behavior
I have observed one strange/unexpected behavior long back, thought of sharing with you guys.
To observe please login to any OBIEE Dashboard now go to browser URL, replace all characters after saw.dll? With ReloadDashboard (url looks like: http://yourobieeserverurl/analytics/saw.dll?ReloadDashboard) and click on go.
What you guys expect?
What I have expected here, dashboard page reloads.
What actually happening is, dashboard shows error message “errorInvalid item name () -- not allowed to be empty”.
Now logoff from Dashboard, go to browser URL replace all characters after saw.dll? With ReloadDashboard and click on go.
Here what you guys expect?
What I have expected here, nothing happens.
What actually happening is, page keeps on reloading for infinite times, sometimes browser could go into hangup state.
I thinking oracle obiee developers not handled this query string parameter ( ’saw.dll? ReloadDashboard’) properly or testers missed this one or I am confused! What do you guys say about it? Did they did with some purposely?
RSS option in OBIEE
With RSS feed we can see/track changes within catalog folder that appears on dashboard page by enabling RSS feed option.
To enable RSS feed option, follow these steps:
1) Go to Dashboard Objects (You can see dashboard objects when you create or edit dashboard pages)
2) Drag and Drop Folder object on to construction page.
3) Click on folder properties, Pops up with web panel.
4) In above panel browse the folder which you want to display on Dashboard.
5) Check enable option ‘Show RSS Link'.
6) Click on save
On Interactive dashboard page ‘xml’ icon should appears, which confirms RSS option enabled.
When we click on 'xml' link pops up with atom xml url(as shown below pic)..which we need to use this link to access RSS feed from any application.
How to see/track changes?
In the market there are so many RSS are available as online (NewsGator, Bloglines Beta,Google Reader,FeedShow, Rojo) and client applications .
Many BI dashboards are internal to organization’s network we cannot use any online RSS feed services like Google reader or FeedBurner etc; we need to go with client applications.
In this post I would like to introduce you two client applications which are very widely used by many people.
1) FeedReader
This tool is open source tool. Below pictures shows steps to configure and view rss feeds.
Above pic: Configuring the atom link within FeedReader and provide UserId/Password to authenticate.
Above pic: First time when FeedReader fetches RSS.
Above pic: After changes made to catalog folder, which were tracked by RSS in FeedReader(shown as Today).
2) RSS option in Outlook - Microsoft Office
We can use outlook also as client application to track changes. Following pictures will show how to configure and access RSS feeds.
Above pic: Configuring RSS feed in Outlook
Note: We need to enable RSS Feed Access through administration manage to see changes.To enable access need to give access as following.
Settings—Administration—Manage Privileges -- RSS Feeds—and give access to whom you want.
For more information:
Adding an RSS Feed Option to a Dashboard Page’s Catalog Folder Page no 209
How to Get UserID and Password
On OTN this question asked. Question is how to get current UserID and Password on a Report, to address this here I'm posting this blog so that it will be help full to other people as well.
In Reporting we can get userid by using function 'USER()' but there is no flexibility on how to get password
To get password we need to do little trick as shown below.
Create new session initialization block with sql query as
select ':USER',':PASSWORD' from dual
And assign these values to session variables, example GetUser, GetPassword
access these session variables on report by VALUEOF(NQ_SESSION.GetUser) or VALUEOF(NQ_SESSION.GetPassword) respectively.
How to find current value of each Server Variables?
If you want to know current assigned values of server variables with in your BI Server then follow these steps.
1. Open RPD
2. Goto Manage menu tab
3. From manage menu select “Sessions …”
 |
| From O.B.I.E.E |
4. Pops up Session Manager
5. From available sessions choose a session
6. Now click on variables tab, it will show all server and session variables and respective values at that particular time.
 |
| From O.B.I.E.E |
In general following type of variables you can see here
Session System: all these variables have specific purpose (reserved key words) with in BI system
Exp: USER, DISPLAYNAME, EMAIL, LOGLEVEL, GROUP, LAST_SYNCH_TIME, PORTALPATH, REQUESTKEY, SKIN, WEBGROUPS, PASSWORD
Session Non-System: These variables are created by admin, for various reporting purposes.
Exp: USERROLE, USERGEO, USERCURRENCY etc
Session System and Session Non-System variables are specific to user logged on.
Dynamic Repository Variables:
These variables can be used by any users with in BI system created by admin,
Exp: LASTWEEK, LASTMONTH, YESTARDAY, CURRENTYEAR etc
Static Repository Variables:
These are initialized when BI Server start remain same as long as server runs.
Exp: PATH_TO_RESOURCE_FILES, WELCOME_MESSAGE, etc.
For more information on variables and how to create variables look at here
Best practice: Optimally define dynamic repository variables.
Subscribe to:
Posts (Atom)