Read and monitor Oracle related blogs and news sources, all in one place.
In part 2 I showed you the Columns structure, in this article I want to show you the filter structure
To create filters OBIEE uses sawx epression engine. First it define the type:
Then the right side:
and left side:
For filters based on an other report it pretty much works the same:
Till Next Time
For any large OBIEE project the normal set-up would involve having a development machine, a test machine, Production machines and some form of disaster recovery. These normally need to be on separate physical machines, mainly for access rights reason and of course the DR machines need to be in a separate building (hopefully in a separate city!)
Did you know that you can have multiple OBIEE environments, but without buying new boxes?
I am not suggesting that development and Production are on the same box, but there are situations that demand environments, such as:
Multiple Projects - The success of many a good OBIEE project will often lead to other departments in the organisation wanting in on the action. Rather than build a whole new development, UAT and Production box you can just add more services to the existing one.
Clustering - although you often cluster for performance, you should also cluster for availability. The normal process is cluster over two or more boxes, but you can also cluster on the same box across users.
Development Cycles – Sometimes you need to create and test a version of your config (rpd /webcat) for a particular release, but carry on developing for the next release. This calls for multiple development environments.
Integration Testing - You can create your repository, but will it work in UAT and production, particularly if they have SSO and your dev environment does not? I prefer to have a pre-UAT environment in place for Developer testing, or internal testing teams to use.
Sandbox - The last thing you want is for all developers to use the same master development repository to implement new models or methods. Get the developer to work on their own copy, or new one, in a separate area to prove that it works.
Worldwide Development – I am not a big fan of MUD. It will work in certain situations, but you normally find that someone hogs the lock on the master repository. The main issue is new business models. One way around this is to have a master repository and have developers around the world use a copy to build their sections, then merge in their changes when they are tested.
Production Support – In a controlled environment the developers do not have access to production (I normally set the production rpd to read only just in case!)
Demo Site – To help your users understand what is possible you can install the sample sites and give all your potential users access.
Training Site – To support training you often need to build a separate environment. You may not want the hassle of whole box to do this on.
Linux
On a Linux box you can install multiple environments into individul user accounts. There is no need to use vitualisation.
When you install a new OBIEE into a separate user you need to make sure that OC4J is not running. Other than that it is a normal simple installation.
After install you have to update all the ports in the system, making sure that you have the correct settings for BI Server, Presentation server, Javahost and graph server. The most important thing is to make sure you do not create spaghetti! Keep a central document on the environments and all their ports.
You can then update the cluster settings as normal, and put the webcat into a shared area for clustering.
We have created a script that does a silent installation and updates all the ports numbers.
Windows
Now there’s another story! My advice is stick to Unix or Linux for now. In theory you can create multiple services to run but I havn’t tried it yet. The simple solution would be to use virtualisation.
If you give it a go let me know how you get on.
No Clutter
Don’t clutter your dashboard with unnecessary visual elements and data. If it looks too busy, then it will fail to communicate the data effectively. Don’t include every bit of data in a dashboard. Seperate them into separate dashboard Tabs.
Single Page – No Horizontal scrolling
Do not put objects on the far right so that the user has to scroll horizontally. It is easy to scroll only one direction which is vertical as that is the default website browsing behavior.
Do not print your Dashboard
Encourage users to export dashboard to PDF and stay on the “Green” side
Add interactivity
Adding extra interactivity to your dashboard, such as being able to select a certain region or being able to drill down can make a huge difference in the perceived usability.
Keep it simple
Show trend by periods
Show pie charts by categories or dimensions
Show tabular data with exception formatting
At the end, ask yourself, “Does this provide any value?” If so, what is it?
In little over a month I will be in Las Vegas speaking at Collaborate 10. There is a lot of BI / DW talks this year and for the first time with BIWA Training Days branding. Rittman Mead will be there at the conference giving talks on each of the conference days. If you are at the conference (or even just on vacation there) then come and say ‘Hi’ to Stewart, Venkat, Mark and myself.
My talk will be about Realtime Data Warehousing – it is an overview of reasons, techniques and pitfalls, but I do cover a lot of material in that hour. Of course, Change Data Capture (CDC) will be a major part of the talk; Oracle has so many options here including their recently acquired GoldenGate product set. As always, the slides will be here on the Rittman Mead site soon after I speak.
My colleague, Stewart Bryson has also had some recent thoughts about change data capture over on the TDWI group at LinkedIn.com (group membership needed); he was quite preceptive (and on the money, in my opinion) with his comment “I would hesitate to let technical limitations dictate user requirements. In today’s BI/DW market, there are very few technical limitations that cannot be solved one way or another.”
One of points I will make in my Realtime DW talk, and perhaps I need a few more slides to do it justice, is the need to profile the change you capture on the source system. Often there is a lot of “noise” that looks like change but you have no real interest in it at the data warehouse. Not all systems are “well behaved”; I have seen systems that always update a record even if nothing has changed and even systems that update each column as separate statement with its own commit. Of course, even systems that don’t have those vices can still have columns that have no DW significance being updated and see those changes being filtered out on the data warehouse after we had already done a lot of work (processing, network bandwidth and the like) to get the data there.
The more I do this kind of work I feel there is a need to switch CDC on on the live source for a while and see the typical patten of change that occurs in a day, week, period whatever and then make decisions on how to handle this defensively downstream. Do we need to exclude certain columns that are just “noise”? What will be the impact of multiple, rapidly-occurring commits on how we handle SCD-2 dimensions? Of course we can predict what will see and come up with a proposed solution but the real source often has a few surprises up its sleeve – once a customer gave me a sequence of order statuses that an order passed through in its life-cycle except that on the actual source system the order sequence was not the same as their documentation and that would impact our reporting.
In part I showed you the base structure. In this part I want to show you the Columns Structure:
The first inner tag is {saw:column} . The base tag contains the column formula, columnId and aggregation rule
This tag is divided into {saw:tableHeading}, {saw:columnHeading}, {saw:displayFormat}
This allows you to set an alternative caption as table heading.
This allows you to set an alternative caption as column heading. Also the interaction on a column level (Ai when you click on the column heading) is set here.
This control the display format of the data cel:
This also control the dataformat:
Till Next Time
As the awareness of Oracle Data Miner increases so does the interest in trying out and playing with Oracle Data Miner (ODM). The following set of instructions steps you through what is required to download and install the database, configure a data mining user in the database, populate the data mining user with some data. The instructions finish with details of how to download and configure the Oracle Data Mining tool. The following instructions cover 11g R1 of the database and tool.
Download and install the 11g R1 Database
NB. Make sure that you complete all the installation steps correctly and that you did not receive any errors. If you did you may need to start again.
HINT : You may need to do the installation as Administrator on your PC.
Set up the Sample Data for the Data Mining User
Create and Configure the Oracle Data Mining User
To create the a database user for data mining, log into the database as the
SYSTEM user (and the password you gave during the installation process) and type a command like the following:
ALTER USER SH IDENTIFIED BY SH;
Download the files DMSHGRANTS.SQL and DMSH.SQL
Logon into the database as SYSTEM and run the DMSHGRANTS.SQL file
@<File Location>dmshgrants sh dmuser
Logon into the database as your DMUSER (password = DMUSER) and run the DMSH.SQL file
@<File Location>dmsh.sql
If everything has worked (no errors) then you should have Oracle Data Mining setup in the Database
Download, Setup and Login to Oracle Data Miner
Two useful books in the documentation are, ODM Administrators Guide 11g and ODM Concepts Guide 11g
If you don’t want to go to the trouble of downloading and installing the enterprise edition of the database then you can use it on the Amazon cloud. The ODM team have provided a set of instructions for you to follow on how to setup yourself up on the Amazo cloud and how you can connect the Oracle Data Mining tool to the database on th Amazon cloud. Link to instructions.
The 4th puzzle in this series was a reasonably simple one which basically demonstrates a capability that allows end users to bypass the security applied in the BI Server layer. There are 3 possible solutions for this
Solution 1: Using Evaluate
EVALUATE was introduced in the 10.1.3.3.1 version of BI EE, that allows end users to call database functions directly. This feature has a lot of uses and actually is widely used. One such use case (others might term this as a bug) is its ability to call table columns that are not in the repository, by using native database functions. For example, the report shown below is a very simple one where only dimensional attributes CHANNEL_CLASS and CHANNEL_TOTAL exist in the report
The SQL for this report is give below
select distinct T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2
from
CHANNELS T4167
order by c1, c2
In our case CHANNEL_DESC column is completely not available for reporting as that is blocked by Security in the presentation layer as shown below
To bypass this, in one of the columns of the above report, use an EVALUATE function shown below
EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS)
This will basically bypass the security of the BI Server and will provide a means of looking at the CHANNEL_DESC column directly. The report and the SQL are given below
select distinct T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3
from
CHANNELS T4167
order by c1, c2, c3
So far so good. Now, lets try adding a measure to this report. You will notice that this will start producing an OCI error.
The reason is since CHANNEL_DESC was not part of the report directly, BI Server did not include that as part of the Select or the Group By clause. The wrong SQL is given below
select T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC) as c3,
sum(1) as c4
from
CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2, c3
Now, to make this work even when a fact attribute is included, the only option is to somehow push this column inside an Aggregate function like SUM, MAX etc since we cannot make the BI Server to generate the Group By (for the secured column). This requires a non-secure dimensional attribute at the same grain as the secured column. In our case, both CHANNEL_CLASS and CHANNEL_DESC have one to one relation and hence i will use that itself to demonstrate. The idea is to encapsulate the EVALUATE expression inside a string aggregate function like MAX etc as shown below
MAX(EVALUATE(’DECODE(%1,”Dummy”,NULL,CHANNEL_DESC)’ AS CHAR(10),Dim.CHANNEL_CLASS))
select T4167.CHANNEL_TOTAL as c1,
T4167.CHANNEL_CLASS as c2,
max(DECODE(T4167.CHANNEL_CLASS,'Dummy',NULL,CHANNEL_DESC)) as c3,
sum(1) as c4
from
CHANNELS T4167
group by T4167.CHANNEL_CLASS, T4167.CHANNEL_TOTAL
order by c1, c2
How do we disable this ability completely as this might be considered as a security bug? Ideally i would like to have a privilege in Answers, that can basically stop the use of EVALUATE functions thereby providing us with the capability of controlling who has access to this. But since this is not currently available, the easiest approach is to make sure that all your query columns are pushed into a sub-query. There are a multiple ways we can push all the columns to a sub-query. I will list them below
1. Using Logical Columns
2. Using Level Based Measures
3. Using SELECT based tables
There are other techniques as well. But for the sake of keeping this brief, i will show how all columns can be pushed into Sub-Queries using SELECT tables (instead of the normal tables obtained through Import). The idea is to use a simple table based on SELECT as shown below
instead of the normal imported table. Then we cannot use EVALUATE as the EVALUATE function can be pushed only to the sub-query. For example, the SQL for the same report above, using SELECT table instead of normal table is given below
select distinct T5419.CHANNEL_TOTAL as c1,
T5419.CHANNEL_CLASS as c2
from
(SELECT
CHANNEL_CLASS,
CHANNEL_CLASS_ID,
CHANNEL_ID,
CHANNEL_TOTAL,
CHANNEL_TOTAL_ID
FROM
CHANNELS) T5419
order by c1, c2
As you see, whatever function we apply in the front-end will always be pushed only to the outer query (inner sub-query will always remain the same which is for the SELECT table). Hence other than the columns in the Select sub-Query, EVALUATE cannot get external columns residing in the actual table (CHANNEL_DESC for example). If we try using the same EVALUATE function we will get an error. But this approach has potential performance issues since for every query, depending on the database, predicates might not get pushed from outer query to inner sub-query(within the optimizer) thereby causing performance issues.
People who answered this correctly: Craig, Anu
Solution 2: Using BYPASS_NQS authentication
This is not something that is normally used. But this is another important example where when the authentication model gets changed, the entire BI EE security can be bypassed. This security model allows any user to login to BI EE. But only database users will be allowed to report out of the databases(login to BI EE with the same username/password as the database). When this is done, any security that is applied at the column level will be bypassed and hence everyone can see the secured column data as well
Solution 3: Impersonation & Proxy Authentication
For this solution to work, one needs to have the privilege to do proxy authentication. Also, one cannot call this exactly as a security bypass since the user requires the capability to proxy in as another user. But there are cases when logged in as a single user, to look at the actual report values (during report development) we might want to login as Administrator which will essentially bypass all the security that is applied at the column level in BI Administrator. For more details on Proxy Authentication refer my blog post here.
The 5th Puzzle in this series to follow later this week.
On the OTN forums (http://forums.oracle.com/forums/thread.jspa?threadID=1039411&tstart=0) an example for this:
was sought.
Let’s start with the basic report:
Calculate the delta:
Add two conditional formats:
Add the correct images:
Set it to image only:
There you go:
Till Next Time
Previous – Excel Dashboard Tutorial – 11
How do you share your Excel Dashboard with users?
Now that you have built the dashboard, you can share the dashboard in multiple ways.
” Create a PDF output of the dashboard and send as email attachment.
Click on the PDF icon on the toolbar as shown below and it will instantly create a PDF output
PDF output
” Create Static HTML output. Click on the HTML icon on the toolbar. This option creates a static html page that you can put in on the web. Users cannot interact with the dashboard. It is just a snapshot image of the dashboard at the time the output was generated. It does not refresh automatically. You will need to regerate the output again when the data is refreshed.
” Desktop Sharing. You can put the dashboard definition file (icv) on a file share directory and other users can view the dashboard through the Dashboard viewer. You may need separate license for dashboard viewers for desktop.
Go to Menu Create ‘ Publish to web and it will generate a file that you can put it where the dashboard web server is and it will render the output. The look and feel is different with this option
The web version is using a flash charting engine to the desktop Java chart engine.
Details of converting the desktop to web version are not covered in the document. Please refer online at any of our websites for more details
You can download the complete tutorial as PDF
” Web Sharing: In this option, you can publish the dashboard to web and the dashboard is available real time with all the dashboard parameters and refresh actions
Previous – Excel Dashboard Tutorial
Summary
We built the above dashboard completely from scratch.
Next – Share Excel Dashboard with other Users
Previous – Excel Dashboard Tutorial – 9
Cleanup – Resize and Organize
Give appropriate names to each portlet.
During the chart creation if we did not provide names, now is the time to provide them.
Right click on each title and click on “Edit”
Scale down the Size
Note, you can drag the corners of each portlet to resize the frames and drag the title name to place them on the canvas
Screen real-estate is very important when designing any dashboard or web application. For better space utilization, we will convert the standard pie to a 3D view
Also, while arranging the objects, keep the most important charts on the top and left area. Put all the detailed portlets at the bottom.
Avoid horizontal scrolling
Remember, do not put any objects on the right side of the scroll bar. Always, keep the scrolling to vertical as it is convienient for viewing.
The above layout will cause for horizontal scrolling and is not a good practice.
Once you do the basic layout of placing the portlets so that they are distinctly visible, it becomes easy to use the resizing options within the tool.
Navigate to Edit ‘ Resize Mode
Once in the resize mode, you can select each object and they are enabled for group resize and placement
Select all the parameters on the top and we will align the top edges with respect to the first one.
As you see, each plet is shown in red with a bar on top indicating it is selected for alignment
One more, thing, you can also turn the Grid mode to see a guide for your alignment and placement
Goto Edit ‘ Show Grid
Note: Once you are done with resize and align with one set of objects, make sure to deselect them before working on the next set of objects
It is best practice to first resize all the objects. All objects on the same horizontal line should have the same height. Select the optimum height of any object on that particular horizontal line and then right click on the title and resize the height of all the selected ones. Then apply the alignment -> Top , deselect the objects and start with the objects in the next horizontal line.
This completes our basic dashboard which is dynamic and extremely informative.
If you need to further customize the look and feel please refer to our online tutorial and reference section
http://www.infocaptor.com/user_help/bi-dashboard-help.htm
Next – Excel Dashboard – Complete Order Management Dashboard
Previous – Excel Dashboard Tutorial – 8
Finish the Dashboard Design and Development
By now, your dashboard is quite chaotic. You have objects all around your dashboard canvas with different sizes. We will start the process of finishing our design.
Before proceeding, we save a copy of the dashboard.
Navigate to menu File ‘ Save As
Give a name to the file so that you can identify the raw dashboard definition file.
Click on Save.
Next, go to menu File => Close All
Note that there are no dashboards to view.
If you need more backup copies to be created or create different versions of the same dashboard definition, then go to the File Explorer, select the file and type – Ctrl-C, and then Ctrl-V
Now, Click on the File ‘ Open
Select the file “Order Management Analytics.icv”
Note that we are not touching the – Initial Copy file.
Click on Open
Get rid of objects you don’t need
We don’t need the initial table portlet, so we will delete that.
Select the “Name” portlet and then click on the “Scissor’ icon on the toolbar
Click “Yes” to confirm.
Note, we created the backup copy of the dashboard so don’t worry if you mess up the dashboard. You can always go back to the original and start again.
Similarly remove all objects that you don’t need it.
Next – Excel Dashboard Tutorial – Arrange objects in sizing and alignment
One of the best ways to drive performance of your projects in your organization is to publish the latest reports, trends, analysis and results on an internal website for your stakeholders, PMO and team members.
One of the best ways that we have seen to do this since our acquisition by Oracle in 2008 is to publish your project portfolio data through Oracle BI (OBIEE) so that you do not require folks to log into a P6 client in order to gain visibility into resource allocation, resource availability, ROI, project budgets, etc.
By leveraging the Primavera P6 Reporting Database from Oracle, you can persist and offline large data sets of all of your company's project history and then report on it with super fast access and analytics from OBIEE (Oracle BI Enterprise Edition) without any negative effects on the live production project system where P6 transactions are occuring.
I have a screen capture of an example of what these dashboards can look like when you build metadata around P6 data:
Previous – Excel Dashboard Tutorial – 7
Create Pie Chart
We will now create a pie chart by product name
It is nice to know that the revenue distribution is not dominated by a single product.
We can now change the “Category name parameter” to see the product distribution for other category
By Category = Beverages
Summary of steps so far
” Create a foundation table portlet (Qlet)
” Add Calculations and any derived columns
” Add Date hierarchy columns as needed
” Create Date parameters if needed
” Create drop down list of values as parameters
” Create charts using the base Qlet
” Delete the foundation portlet when not needed and you are done with the dashboard design
” Arrange the remaining portlets in desired format.
Once you are done with all the Qlets (query/table portlet) and Glets (graphic portlets), it is advisable to save the dashboard file, copy the file and work on the new file for finishing and polishing the dashboard.
Next – Excel Dashboard Tutorial – 9
RSS
Email