tag:blogger.com,1999:blog-88415857331410379462024-03-13T03:18:28.154+00:00OBIEE (Oracle Business Intelligence) TipsThis is a blog detailing the interesting (and sometimes not so interesting) features of OBIEE which I come across in my day-to-day life.Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-8841585733141037946.post-5129904743382623902011-03-11T07:11:00.000+00:002011-03-11T07:11:15.437+00:00Removing the PDF/HTML print option<div style="font-family: Arial,Helvetica,sans-serif;">I saw a post in the OTN forums asking how to disable the pdf option in the print menu for dashboards, if you want to do this for a particular dashboard then you can do this through javascript, here is a simple example:</div><br />
<div style="font-family: "Courier New",Courier,monospace;">var dateHeaderDiv = document.getElementById("dateHeader");<br />
var dateHeaderPos = findPos(dateHeaderDiv);<br />
dateHeaderDivYOffset = dateHeaderPos[1];<br />
dateHeaderDivXOffset = dateHeaderPos[0];<br />
<br />
var menuLink = getElementsByClassName("NQWMenuItem");<br />
<br />
//Get the appropriate function for getting the HTML print<br />
//output of the current page<br />
for(i = 0; i < menuLink.length; i++)<br />
{<br />
if(menuLink[i].name == "html")<br />
{<br />
var onClickText = menuLink[i].onclick;<br />
}<br />
}<br />
<br />
//Find the print icon and make it display the print page instead of the<br />
//default menu for pdf or html print (because pdf print does not work<br />
//properly with javascript narrative views)<br />
var pdfParent = getElementsByClassName("DashboardFormatLinks");<br />
<br />
for(i = 0; i < pdfParent.length; i++)<br />
{<br />
for(j=0; j < pdfParent[i].childNodes.length; j++)<br />
{<br />
if(pdfParent[i].childNodes[j])<br />
{<br />
if(pdfParent[i].childNodes[j].title == "Printer Friendly")<br />
{<br />
pdfParent[i].childNodes[j].onclick = onClickText;<br />
}<br />
}<br />
}<br />
}</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;">That acutally removes the PDF option and makes the print icon kick off the HTML print page straight away without showing the menu, it works in 10.1.3.4.1. You can then add that as a static text item with the HTML option ticked and add that to the dashboard.</span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;">If you want to remove the option to print to pdf all across the application then first find the controlmessages.xml file which lives in OracleBI\web\msgdb\messages and copy it to </span></span><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">OracleBIData\web\msgdb\customMessages</span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;">, if it the customMessages folder is not there then create it. Now open the file, it looks like this:</span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"><?xml version="1.0" encoding="utf-8"?></span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><!--DO NOT MODIFY THIS FILE. THIS FILE IS AUTOMATICALLY GENERATED AND IS REPLACED UPON UPGRADE OR REINSTALL.--><!--Contents of this file are Copyright (C) 2001-2005 by Siebel Systems, inc.--><!--Consult your Siebel Analytics Web documentation for how to override messages. Note that some or all messages may not be overriden unless your license specifically allows for it.--><WebMessageTables xmlns:sawm="com.siebel.analytics.web/message/v1"><WebMessageTable lang="en-us" system="ControlMessagesSys" table="Messages"></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgAdminSysLogConcatenateNew"><HTML><nobr><sawm:param insert="1"/></nobr><br/><nobr><sawm:param insert="2"/></nobr></HTML></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgAnswersBannerHeight"/></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgAnswersBannerURL"/></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgCatalogTabURL"/></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgChangePasswordLink"><!-- <HTML><a insert="1"><sawm:messageRef name="kmsgUIChangePassword"/></a></HTML> --></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgCustomLink"/></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgDashboardAddToBriefbookLink"><HTML><a insert="1"><img align="absbottom" src="fmap:Portal/add2bb.gif" border="none"/></a></HTML><!-- <HTML><a insert="1"><img align="absbottom" src="fmap:Portal/add2bb.gif" border="none"/></a>&nbsp;<a insert="1"><sawm:messageRef name="kmsgPortalAddToBriefbook"/></a></HTML> --></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgDashboardAlternateFormats"><HTML><span class="DashboardFormatLinks"><sawm:param insert="1"/></span>&nbsp;<span class="DashboardFormatLinks"><sawm:param insert="2"/></span>&nbsp;<span class="DashboardFormatLinks"><sawm:param insert="3"/></span></HTML></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgDashboardPrinterFriendlyLink"><HTML><a href="javascript:void(null)" onclick="return NQWPopupMenu(event,'idDashboardPrintMenu', null, 'top')" title="@{title}"><img align="absbottom" src="fmap:Portal/PrinterFriendly.gif" border="none"/></a></span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><div id="idDashboardPrintMenu" class="NQWMenu" onmouseover="NQWMenuMouseOver(event)"><sawm:messageRef name="kuiMenuShadowBegin"/><a class="NQWMenuItem" name="html" href="javascript:void(null)" onclick="return PortalPrint('@{htmlURL}[javaScriptString]', true);"><sawm:messageRef name="kmsgDashboardPrintHTML"/></a><sawm:if name="enablePDF"><a class="NQWMenuItem" name="pdf" href="javascript:void(null)" onclick="return PortalPrint('@{pdfURL}[javaScriptString]',@{bNewWindow});"><sawm:messageRef name="kmsgDashboardPrintPDF"/></a></sawm:if> </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><sawm:messageRef name="kuiMenuShadowEnd"/></div></HTML></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgDashboardRefreshPageLink"><!-- title --><!-- portalPath --><!-- pageName --><HTML></span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><A href="javascript:void(null)" onclick="RefreshPage('@{sawCmd}[javaScriptString]&PortalPath=@{portalPath}[javaScriptString]&Page=@{pageName}[javaScriptString]');return false;" title="@{title}"><img align="absbottom" src="fmap:Portal/dash_refresh.gif" border="none"/></A></HTML></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgInOuterFrame"/></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgJoinGroupLink"><HTML><a insert="1"><sawm:messageRef name="kmsgUIJoinGroup"/></a></HTML></WebMessage></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kmsgStaticWebGroups"><TEXT>Analytics Users</TEXT></WebMessage></WebMessageTable><WebMessageTable system="ViewGeneration" table="IQY"></span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"><WebMessage name="kuiIQYContent"><!-- cmdPrefix = http://machine/path/saw.dll? --><!-- path = /path/to/some/report --><!-- noSSO = true | false --><TEXT>WEB<sawm:lineBreak/>1<sawm:lineBreak/><sawm:param name="cmdPrefix"/>Download&Format=excel&Extension=.xls&BypassCache=true&PathEncode=IQYEncode&Path=<sawm:param name="path"/><sawm:if name="noSSO">&NQUser=["Oracle BI User"]&NQPassword=["Oracle BI Password"]&SyncOperation=1</sawm:if><sawm:lineBreak/></TEXT></WebMessage></WebMessageTable></WebMessageTables> </span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;">The bit we care about in this case is the </span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;">kmsgDashboardPrinterFriendlyLink<span style="font-family: Arial,Helvetica,sans-serif;"> in here</span></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"></span> you can see the bit that says:</span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"></span><span style="font-family: "Courier New",Courier,monospace;"><sawm:if name="enablePDF"><a class="NQWMenuItem" name="pdf" href="javascript:void(null)" onclick="return PortalPrint('@{pdfURL}[javaScriptString]',@{bNewWindow});"><sawm:messageRef name="kmsgDashboardPrintPDF"/></a></sawm:if></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"> </span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">Removing all of that will disable the print to pdf option across the board as the option will no longer appear in the menu.</span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">Restart the OBIEE services and you should see the change applied. </span> </span></span></span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com2tag:blogger.com,1999:blog-8841585733141037946.post-49297880256823409792011-03-10T12:50:00.000+00:002011-03-10T12:50:44.912+00:00Using a row wise initialised session variable in a dashboard prompt<div style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">I came across a case today where I wanted to use the contents of a row wise intialised variable in a dashboard prompt. You can't just use the variable directly as the possible list of values for the prompt as you will get an error message like this:</span></div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: "Courier New",Courier,monospace; margin-top: 4pt;"> <div class="ErrorSubInfo"><span style="font-size: small;">State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)</span></div></div><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"> </span></span><div style="margin-top: 4pt;"> <div class="ErrorSubInfo" style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;"><span class="SQLTag">SQL Issued: </span><span class="SQLDisplay" dir="ltr">VALUEOF(NQ_SESSION.<variable>) </variable></span></span></div><div class="ErrorSubInfo" style="font-family: Arial,Helvetica,sans-serif;"><span class="SQLDisplay" dir="ltr"> </span></div><div class="ErrorSubInfo"><span class="SQLDisplay" dir="ltr" style="font-family: Arial,Helvetica,sans-serif;">So instead you can use the SQL Results option for the "Show" section of the dashboard prompt to bring in the column you are prompting and then add a where clauses referencing the variable:</span></div><div class="ErrorSubInfo"><span class="SQLDisplay" dir="ltr" style="font-family: Arial,Helvetica,sans-serif;"> </span></div><div class="ErrorSubInfo"><span class="SQLDisplay" dir="ltr" style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">SELECT Organisation.Organisation FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.<variable>) </variable></span></span> </span></div><div class="ErrorSubInfo"><br />
</div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">This will work with no errors and the list of possible results in the dashboard prompt will the same as those held in the session variable.</span></span></div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;"> </span></span></div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">If you then want one of these to be selected by default (if you use report defaults then a blank entry will be added to the dropdown and this will be selected by default) then you can use some SQL again:</span></span></div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;"> </span></span></div><div class="ErrorSubInfo" style="font-family: "Courier New",Courier,monospace;"><span style="font-size: small;">SELECT MIN(Organisation.Organisation) FROM Masterplan where Organisation.Organisation=VALUEOF(NQ_SESSION.ORGANIZATION_NAME)</span></div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;"> </span></span></div><div class="ErrorSubInfo"><span style="font-size: small;"><span style="font-family: Arial,Helvetica,sans-serif;">This will select the first one in the list of results when ordered alphabetically.</span></span></div></div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com1tag:blogger.com,1999:blog-8841585733141037946.post-72005776294843585842010-07-07T20:20:00.003+01:002010-07-08T10:18:45.281+01:00OBIEE11g Launch<div style="font-family: Arial,Helvetica,sans-serif;">So, just back from the launch day for OBIEE 11g, lots of good stuff there, although I have to say that after this long in development I was hoping for a bit more, especially for developers. When one of the people giving a session opened the Admin tool and I saw that it was pretty much exactly the same with new icons my heart did sink a little bit.</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Anyway, here are the notes I made, and a few blurry pictures from my iPhone, sorry still getting used to it ...</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Visualisation:<br />
<br />
- Geospatial support, ie results shown on map without any extra config, probably separately licensed says my sceptical self.<br />
- Scorecards, kpi watchlist , strategy trees etc; these are all new and look very powerful, again the implication was separate licensing.<br />
- Complete integration of bi publisher and <br />
- Complete control of the whole from enterprise manager<br />
- Pivot tables can be re- pivoted in place by the user; huge win in my mind<br />
- Comments against the strategy tree explaining problems etc, collaboration, not so sure about this one. </div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
From insight to action:<br />
<br />
- Action links have the traditional navigate etc options but also allow custom actions. These actions can be all sorts of navigation, call web service, java method, browser script or http method. Guided navigation still available on these links.<br />
<br />
- Can have saved actions which we then just pick out as action for link. So actions are now first class objects that are saved and shared.<br />
<br />
- Can add commentaries to dashboards etc for collaboration, maybe useful if going to sharepoint or something.<br />
<br />
Dashboard editor is much prettier.<br />
<br />
iBots now called Agents. Other than that not very different for delivers section except for actions which now allows the full list as above and use of the action library. Also have invoke per row so that action is triggered for each row in the report instead of for the whole result set.<br />
<br />
Systems management and deployment:<br />
<br />
- Centralised performance management.<br />
- Pull logs from all components on all servers to one log and trace an error or query all the way through it.<br />
- Usage tracking and delivers etc schemas are created at point of installation with repository creation utility. </div><div style="font-family: Arial,Helvetica,sans-serif;">- Oracle Process Management Notification now used for controlling non j2ee comps i.e. BI server etc. All j2ee comps now controlled via weblogic which is included in the installation. EM can be used to configure everything now for 11g.<br />
<br />
Security things: pluggable SSO and ID management. SSL everywhere can be configured in one place and then starts working between all components just like that.<br />
<br />
3 install options, simple, enterprise (pick ports network install etc) and software only for just lying out software and not doing the configuration.<br />
<br />
Very simple 10g to 11g upgrade experience multipass so that you can check what is going to happen etc before doing it. Upgrades data and schema and all pieces eg webcat.<br />
<br />
EM is very good now. No more searching for logs etc all in one place no more configuration files either.<br />
<br />
Can also see performance metrics for the environment e.g. Number of queries, new logins by time.<br />
<b><br />
6 weeks till GA of OBIEE11G</b><br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/TDTTwQhijGI/AAAAAAAAAGk/cu4k-bWemv4/s1600/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278505706.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/TDTTwQhijGI/AAAAAAAAAGk/cu4k-bWemv4/s320/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278505706.jpg" /></a><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/TDTTzTUOhvI/AAAAAAAAAGs/BwD_wI8NsyA/s1600/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278505776.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/TDTTzTUOhvI/AAAAAAAAAGs/BwD_wI8NsyA/s320/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278505776.jpg" /></a></div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/TDTToagks3I/AAAAAAAAAGc/WnJWUH_vjuw/s1600/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278510060.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/TDTToagks3I/AAAAAAAAAGc/WnJWUH_vjuw/s320/_var_mobile_Applications_A8BCAC7F-429D-4E06-8621-D11D92A0B67A_Documents_image_img_1278510060.jpg" /></a></div></div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com0tag:blogger.com,1999:blog-8841585733141037946.post-43735940295110626382010-06-01T09:47:00.005+01:002010-06-06T18:08:06.950+01:00DAC server as service in 10.1.3.4.1<span style="font-family: Arial,Helvetica,sans-serif;">Today I had to create the DAC server as a service again, this time in a later version of the DAC and found that the libraries have changed. To get the DAC server working in this version:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/TATLyEklKpI/AAAAAAAAAGE/daJfe8U36lU/s1600/untitled.PNG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/TATLyEklKpI/AAAAAAAAAGE/daJfe8U36lU/s320/untitled.PNG" /></a></div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">I had to use this command (you'd need to change this to point to your location for the jdk and the dac root directory):</span><br />
<br />
<span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">javaservice -install "Oracle BI: DAC Service" "E:\Java\jdk1.6.0_13\jre\bin\client\jvm.dll" -Xms256m -Xmx1024m "-Djava.class.path=E:\OracleBI\DAC\bifoundation\dac\lib\msbase.jar;E:\OracleBI\DAC\bifoundation\dac\lib\mssqlserver.jar;E:\OracleBI\DAC\bifoundation\dac\lib\msutil.jar;E:\OracleBI\DAC\bifoundation\dac\lib\sqljdbc.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc6.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc5.jar;E:\OracleBI\DAC\bifoundation\dac\lib\ojdbc14.jar;E:\OracleBI\DAC\bifoundation\dac\lib\db2java.zip;E:\OracleBI\DAC\bifoundation\dac\lib\terajdbc4.jar;E:\OracleBI\DAC\bifoundation\dac\lib\log4j.jar;E:\OracleBI\DAC\bifoundation\dac\lib\teradata.jar;E:\OracleBI\DAC\bifoundation\dac\lib\tdgssjava.jar;E:\OracleBI\DAC\bifoundation\dac\lib\tdgssconfig.jar;E:\OracleBI\DAC\bifoundation\dac\DAWSystem.jar;E:\OracleBI\DAC\bifoundation\dac" "-Duser.dir=E:\OracleBI\DAC\bifoundation\dac" -start com.siebel.etl.net.QServer -description "Oracle BI DAC Server Service" -current "</span></span><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">E:\OracleBI\DAC\bifoundation\dac"</span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">As you can see the libraries are completely different compared to my earlier post.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">You must include the -current parameter, as otherwise the relative references to files will not work and so features such as integration with Informatica will not work correctly. </span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com3tag:blogger.com,1999:blog-8841585733141037946.post-18168358536307439642010-02-02T12:06:00.000+00:002010-02-02T12:06:23.347+00:00SA System Subject Area<div style="font-family: Arial,Helvetica,sans-serif;">I have seen a few posts on various blogs which have info on the SA System area, but none of them seemed complete so here's my attempt :-)</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">The SA System area allows you to set up delivery devices for users of Siebel Delivers automatically, so that it isn't necessary for each user to fill in My Account with appropriate delivery devices and profiles etc.<br />
<br />
The core of the SA System area is a presentation catalog/subject area containing <b>exactly</b> the following tables and columns: <br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gI-puj7hI/AAAAAAAAAEs/gb6Eo7zj4ZY/s1600-h/PresentationRPDSA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gI-puj7hI/AAAAAAAAAEs/gb6Eo7zj4ZY/s320/PresentationRPDSA.png" /></a> </div><div class="separator" style="clear: both; text-align: left;">As far as I can work out the BMM layer can look pretty much as you want as long as the presentation layer looks like this. For example, in other examples on blogs I have seen the Group Name on the same logical table as the user name etc; in my BMM I took a leaf out of the OBIA and how the SA System is configured to work with Siebel:</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"></div><div class="separator" style="clear: both; text-align: left;"></div><div style="text-align: center;"><a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/S2gJdHIiYgI/AAAAAAAAAE0/aD3FWkimq_I/s1600-h/BMMRPDSA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/S2gJdHIiYgI/AAAAAAAAAE0/aD3FWkimq_I/s320/BMMRPDSA.png" /></a> </div><div style="text-align: center;"></div><div style="text-align: left;">So I created a seperate logical table for groups, and then an intersection table to hold the many to many relationship between users and groups, this also mapped to the physical layer:<br />
<br />
</div><div style="text-align: left;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/S2gJtUY5yhI/AAAAAAAAAE8/d27dp_69z5Q/s1600-h/PhysicalRPDSA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/S2gJtUY5yhI/AAAAAAAAAE8/d27dp_69z5Q/s320/PhysicalRPDSA.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;">I did this because I wanted each user to be able to have multiple groups, more on this later. </div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div style="text-align: left;"></div>So, once I had created this rest rpd, I set up my OBIEE server to use it, filled in some data in the DB tables and then started it up. This is a query showing what I added to the tables:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gPem-TAnI/AAAAAAAAAF8/rFlriLgFpj4/s1600-h/ResultOfQueryInSA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gPem-TAnI/AAAAAAAAAF8/rFlriLgFpj4/s320/ResultOfQueryInSA.png" /></a></div><br />
<br />
I then logged in as my test user, went to My Account and got this:<br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gKXxtGwfI/AAAAAAAAAFE/e9tAiftPdPk/s1600-h/ErrorInMyAccount.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gKXxtGwfI/AAAAAAAAAFE/e9tAiftPdPk/s320/ErrorInMyAccount.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;">Hmm, strange. So as instructed I looked at the BI presentation server log:</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">Authentication Failure.</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Odbc driver returned an error (SQLDriverConnectW).</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">---------------------------------------</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Type: Error</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Severity: 40</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Time: Fri Jan 29 14:20:21 2010 </span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">File: project/websubsystems/sasystemsubjectarea.cpp Line: 248</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Properties: ThreadID-7256;HttpCommand-UserPreferences;RemoteIP-127.0.0.1;User-Matt</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Location:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.httpserver.request</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.rpc.server.responder</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.rpc.server</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.rpc.server.handleConnection</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.rpc.server.dispatch</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.threadPool</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> saw.threads</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Error finding System SA. Authentication Failure.</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Error Codes: IHVF6OM7:OPR4ONWY:U9IM8TAC</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">Odbc driver returned an error (SQLDriverConnectW).</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused.</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">[nQSError: 43001] Authentication failed for Administrator in repository Star: invalid user/password. (08004)</span></span></div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif; font-size: small;">I had to scratch my head over this one for a while until I wondered if it was something to do with the credential store that Delivers uses to authenticate to the presentation services.</span> So I decided to try adding Administrator to the store:</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">C:\OracleBI\web>cd bin</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBI\web\config\credentialstore.xml</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Credential Store File:</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBIData\web\config\credentialstore.xml</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Credential Store File:</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">C:\OracleBI\web\bin>cryptotools credstore -add infile c:\OracleBIData\web\config\credentialstore.xml</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Credential Store File: c:\OracleBIData\web\config\credentialstore.xml</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Credential Alias: admin</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Username: Administrator</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Password: ******</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>Do you want to encrypt the password? y/n (y): n</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">>File "c:\OracleBIData\web\config\credentialstore.xml" exists. Do you want to overwrite it? y/n (y): y</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">C:\OracleBI\web\bin></span></span></div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">After that I restarted the services and tried again</span></span>, to my surprise this had worked:</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div style="text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gLYeZY1UI/AAAAAAAAAFM/4jaC_Tk1rbs/s1600-h/MyAccountWorking.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gLYeZY1UI/AAAAAAAAAFM/4jaC_Tk1rbs/s320/MyAccountWorking.png" /></a> </div><div style="text-align: center;"><br />
</div><div style="text-align: left;">Here we can see the devices I added for myself in the database table (the cell phone and pager aren't real examples, they have to be addresses to which texts can be delivered rather than actual phone numbers):</div><div style="text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gL_VpeDSI/AAAAAAAAAFU/x-b9Wqp8v1Y/s1600-h/MyAccountSystemEmail.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gL_VpeDSI/AAAAAAAAAFU/x-b9Wqp8v1Y/s320/MyAccountSystemEmail.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"> <a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gMCihXJZI/AAAAAAAAAFc/OMAyTfO1PRE/s1600-h/MyAccountSystemPhone.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/S2gMCihXJZI/AAAAAAAAAFc/OMAyTfO1PRE/s320/MyAccountSystemPhone.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"> <a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gMF2jo4TI/AAAAAAAAAFk/PEnHkftVOzY/s1600-h/MyAccountSystemPager.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gMF2jo4TI/AAAAAAAAAFk/PEnHkftVOzY/s320/MyAccountSystemPager.png" /></a> </div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;">And here is the profile I added:</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"></div><div class="separator" style="clear: both; text-align: left;"></div><div style="text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gMKVKeXrI/AAAAAAAAAFs/L3vqMKURMl0/s1600-h/MyAccountSystemProfile.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/S2gMKVKeXrI/AAAAAAAAAFs/L3vqMKURMl0/s320/MyAccountSystemProfile.png" /></a></div><div style="text-align: left;"><br />
</div>On playing around with this there appear to be some of the columns which don't do anything:<br />
<ul><li>Language</li>
<li>Locale</li>
<li>Time Zone</li>
<li>Group Name</li>
</ul>As the membership to web groups is handled through the GROUPS session variable, I'm not sure what the Group Name in SA System is supposed to do. I tested by adding myself to multiple groups but this didn't appear to have any effect anywhere within OBIEE. <br />
<br />
Another thing to consider is that a user can still add their own delivery devices and profiles and thus override the SA System ones. However this can be disabled through the instanceconfig.xml, the tag <b><IgnoreWebcatDeliveryProfiles></b> needs to be added inside the Alerts tags and set to true, for example:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;"><ServerInstance><br />
...<br />
<Alerts><br />
...<br />
<IgnoreWebcatDeliveryProfiles>true</IgnoreWebcatDeliveryProfiles><br />
...<br />
</Alerts><br />
...<br />
</ServerInstance></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">This will mean that users cannot now add their own delivery devices or profiles, the My Account screen looks like this (notice that the links to add new devices and profiles are missing):</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"></div><div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/S2gOOEZcu0I/AAAAAAAAAF0/4tjUl3GcM2Y/s1600-h/MyAccountWithUserProfilesDisabled.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/S2gOOEZcu0I/AAAAAAAAAF0/4tjUl3GcM2Y/s320/MyAccountWithUserProfilesDisabled.png" /></a></div><br />
By default the user name you log in with and the user name in the database table must exactly match for the SA System area to work. You can get around this by using the tag <b><UpperCaseRecipientNames></b> in the instanceconfig.xml as below:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;"><ServerInstance></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"><Alerts></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"><UpperCaseRecipientNames>true</UpperCaseRecipientNames></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"></Alerts></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"></ServerInstance></span><br />
<br />
If you then ensure that the user names on the database table are in upper case then the user logging in can be in upper, lower or a mix and it will still work. For example, the username on the database table is 'MATT' but I log in using 'Matt', this will still pick up my delivery devices and profiles if the tag above is set to true.<br />
<br />
Finally, it is possible to disable the SA System area completely from the instanceconfig.xml using the tag <b><SystemSubjectArea></b> (note that this tag does not go inside Alerts):<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;"><ServerInstance></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"><SystemSubjectArea>true</SystemSubjectArea></span><br />
<span style="font-family: "Courier New",Courier,monospace;">...</span><br />
<span style="font-family: "Courier New",Courier,monospace;"></ServerInstance></span><br />
<span style="font-family: "Courier New",Courier,monospace;"></span><span style="font-family: "Courier New",Courier,monospace;"></span></div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com8tag:blogger.com,1999:blog-8841585733141037946.post-2711855744956858702009-11-26T11:13:00.000+00:002009-11-26T11:13:40.521+00:00ISAPI Forwarding<div style="font-family: Arial,Helvetica,sans-serif;">At a recent client we were using two web servers, both running IIS and the OBIEE presentation services, over the top of this a load balancer routed connections to one of the two servers based on load. We used the Oracle provided replication services to make sure that the contents of the two web catalogs running on each presentation server were kept in synch.<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">However we noticed over time that the replication services weren't working 100% and that some items were not present in both web catalogs.<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">To get around this we decided instead to use the ISAPI forwarding functionality; this allows the IIS server on one web server to forward it's presentation service connections to another server. This means that we can have two servers running IIS and accepting incoming connections, but only one running the presentation services, so only one web catalog is required and no replication has to take place.<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">In versions before the name change to OBIEE (i.e. Siebel Analytics 7.8 etc) this was handled via a registry key entry as below:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Path:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
<div style="font-family: "Courier New",Courier,monospace;">HKEY_LOCAL_MACHINE\Software\Siebel Systems, Inc.\Siebel Analytics\Web\7.8\ISAPI\ <br />
</div></div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">String value:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Name = <span style="font-family: "Courier New",Courier,monospace;">ServerConnectString</span><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Value = <span style="font-family: "Courier New",Courier,monospace;">sawtcp://[server name]:9710 </span><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">In OBIEE this has been replaced with a setting in the configuration file:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">[Oracle BI Directory]\web\config\isapiconfig.xml<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Like this:<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;"><?xml version="1.0" encoding="utf-8"?></span><br />
<span style="font-family: "Courier New",Courier,monospace;"><WebConfig></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <ServerInstance></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <ServerConnectInfo address="localhost" port="9710"/></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </ServerInstance></span><br />
<span style="font-family: "Courier New",Courier,monospace;"></WebConfig></span><br />
<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Just change localhost to the name of the server running the presentation services that you want the IIS connections to be forwarded to.<br />
</div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com4tag:blogger.com,1999:blog-8841585733141037946.post-69660091140021512472009-10-28T08:36:00.000+00:002009-10-28T08:36:56.708+00:00RPD Groups and Siebel Responsibilities<span style="font-family: Arial,Helvetica,sans-serif;">This post explains how responsibilities map to RPD groups in OBIA/Analytics Apps with Siebel as a source of the apps.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">First of all, you must either create responsibilities in Siebel with exactly the same name as groups in RPD, or vice versa. The important point is that there must be groups in the RPD and responsibilities in Siebel with exactly the same name:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/Suf-zlJ2EJI/AAAAAAAAAEU/iMC5fv5iJ64/s1600-h/groups1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/Suf-zlJ2EJI/AAAAAAAAAEU/iMC5fv5iJ64/s320/groups1.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: center;"><b> <span style="font-size: x-small;"><span style="font-family: Arial,Helvetica,sans-serif;">Groups in RPD</span></span></b><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"> <a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/Suf-2RH2IWI/AAAAAAAAAEc/792lZtO_rkk/s1600-h/groups2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/Suf-2RH2IWI/AAAAAAAAAEc/792lZtO_rkk/s320/groups2.png" /></a><br />
</div><div style="text-align: center;"><span style="font-family: Arial,Helvetica,sans-serif;"> <b><span style="font-size: x-small;">Responsibilities in Siebel</span></b></span><br />
<br />
</div><div style="text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">When a user logs into OBIEE an initialisation block named "Authorization" runs some SQL against the Siebel database and gets the responsibilities for the current user. It sets the session variable GROUP to the set of responsibilities returned.</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SugAbENfhEI/AAAAAAAAAEk/12oti5TWQLU/s1600-h/groups3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SugAbENfhEI/AAAAAAAAAEk/12oti5TWQLU/s320/groups3.png" /></a><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">If there are groups in the RPD which have identical names to any of the responsibilities held in the GROUP variable after the initialisation block has run then the user will be added to those groups, and will then inherit all the security (object and data level) for that group.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">This example is based on Siebel as a source, but there is no reason why this can't work for any source. The important point is the session initialisation block which runs and gets the groups to add the user to. In Siebel this is controlled through responsibilities, in another source system it could be through another mechanism; as long as it is possible to retrieve through SQL then the initialisation block can be changed to use this. <br />
</span><br />
</div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com0tag:blogger.com,1999:blog-8841585733141037946.post-85412408135295419162009-10-20T10:15:00.000+01:002009-10-20T10:15:16.204+01:00Logical Levels<div style="font-family: Arial,Helvetica,sans-serif;">One of the issues that developers regularly run into with OBIEE (especially when they are new to the application) is the setting of levels against logical fact sources. The most common error which should instantly have you thinking about levels if you see it is:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Logical dimension table <x> has a source <y> that does not join to any fact source</y></x><br />
<br />
Beyond the obvious check that all sources do actually join to a fact you should then think about levels. Logical Levels are set against the logical source of fact tables in the BMM layer:<br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/St1-E5Q0tUI/AAAAAAAAAEM/B4wIAYMmJe0/s1600-h/Levels1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/St1-E5Q0tUI/AAAAAAAAAEM/B4wIAYMmJe0/s320/Levels1.png" /></a><br />
</div><br />
<div style="font-family: Arial,Helvetica,sans-serif;">Levels control how the fact table connects to the dimension table and are used to create level based measures and control the use of aggregation tables. What many people don't realise is that to make OBIEE work correctly you should always set the levels on fact tables, so that OBIEE knows that it can connect the dimension and the fact at the most detailed level.<br />
<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">The general rules for levels are:<br />
</div><ol style="font-family: Arial,Helvetica,sans-serif;"><li>If you create a new dimension table, build a dimension hierarchy over it<br />
</li>
<li>Make sure the dimension hierarchy has a detail level at the lowest level of granularity for the dimension table (i.e. integration id, ROW_WID etc)<br />
</li>
<li>If you are adding a join from this new dimension to any fact table then make sure that you set the level for the connected dimension hierarchy against the correct logical source of the fact (the one that physically joins to the dimension), the default level to set is the lowest level (the detail level).</li>
<li>If you create a new fact then you need to make sure you set the levels as in 3 for all the dimension hierarchies built over dimensions which this fact joins to.</li>
</ol><span style="font-family: Arial,Helvetica,sans-serif;">Sometimes you can get away with not setting levels, but more often than not this will come back and bite you in the long run, the better option is to ensure that you always set them.</span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com2tag:blogger.com,1999:blog-8841585733141037946.post-68719161622517030142009-09-28T11:50:00.017+01:002009-09-28T12:00:00.782+01:00Row-wise variables<span style="font-family: Arial,Helvetica,sans-serif;">This is a topic I've seen a few times on the OTN forums, and I'm not surprised because the way it has been built is not exactly intuitive. With that in mind here are some notes on Row-wise intialisation of varaibles in OBIEE, how it works, how to use them and what they are useful for.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">So first of all I am going to set up a table for testing purposes:</span><br />
<blockquote><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: x-small;"><span style="font-family: "Courier New",Courier,monospace;">CREATE TABLE TEST_ROWWISE(</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> ROW_WID NUMBER(10) NOT NULL,</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> USER_NAME VARCHAR2(100),</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"> VAR_VALUE VARCHAR2(100)</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">);</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO TEST_ROWWISE VALUES(1,'matt','Value 1');</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO TEST_ROWWISE VALUES(2,'matt','Value 2');</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO TEST_ROWWISE VALUES(3,'matt','Value 3');</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO TEST_ROWWISE VALUES(4,'sue','Value 1');</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">INSERT INTO TEST_ROWWISE VALUES(5,'sue','Value 2');</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">commit;</span></span></span><br />
</blockquote><span style="font-family: Arial,Helvetica,sans-serif;">Then in my repository I created a user matt and a user sue for testing purposes:</span><br />
<div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCJskGLvgI/AAAAAAAAACc/ZEznhSFwYXQ/s1600-h/variable1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCJskGLvgI/AAAAAAAAACc/ZEznhSFwYXQ/s320/variable1.png" /></a><br />
</div><br />
<div style="font-family: Arial,Helvetica,sans-serif;">Next I created a new session intitialisation block:<br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/SsCJ-r8A8lI/AAAAAAAAACk/63YR5Gxvn9U/s1600-h/variable2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/SsCJ-r8A8lI/AAAAAAAAACk/63YR5Gxvn9U/s320/variable2.png" /></a><br />
</div><br />
<div style="font-family: Arial,Helvetica,sans-serif;">With a data source of this:<br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCKLk3RLpI/AAAAAAAAACs/_Lv9c5wvn6Y/s1600-h/variable3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCKLk3RLpI/AAAAAAAAACs/_Lv9c5wvn6Y/s320/variable3.png" /></a><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
Then in the data target:<br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCUSnolhcI/AAAAAAAAADc/gSeGrN1Q4n0/s1600-h/variable4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCUSnolhcI/AAAAAAAAADc/gSeGrN1Q4n0/s320/variable4.png" /></a><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">The end result of this is that when a user logs in the Oracle BI server will run the SQL in the initialization block against the database and return the rows from the table TEST_ROWWISE for that user (the parameter :user always passes the username of the currently logged in user). After it has done that the currently logged in user now has a variable called 'VAR' containing all the values for that user.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">I tested it by exposing the TEST_ROWWISE table in an rpd and creating a simple request against that table:</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"> </span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCQQdJIkII/AAAAAAAAAC8/G8xmhjFYpYw/s1600-h/variable5.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCQQdJIkII/AAAAAAAAAC8/G8xmhjFYpYw/s320/variable5.png" /></a> <br />
</div><span style="font-family: Arial,Helvetica,sans-serif;"> </span><br />
<span style="font-family: Arial,Helvetica,sans-serif;">Then to test the variable add a filter to the request:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/SsCRxm9atxI/AAAAAAAAADU/FfmmwIMAOyA/s1600-h/variable8.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/SsCRxm9atxI/AAAAAAAAADU/FfmmwIMAOyA/s320/variable8.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">And the detail of the filter:</span><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCVbOHjabI/AAAAAAAAADk/lQidr4Jdgok/s1600-h/variable9.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCVbOHjabI/AAAAAAAAADk/lQidr4Jdgok/s320/variable9.png" /></a><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">When logged on as matt I see:</span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCRgUg4l9I/AAAAAAAAADM/W3480nbnhZM/s1600-h/variable7.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/SsCRgUg4l9I/AAAAAAAAADM/W3480nbnhZM/s320/variable7.png" /></a><br />
</div><span style="font-family: Arial,Helvetica,sans-serif;">And when logged in as sue: <br />
</span><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCRNvphQXI/AAAAAAAAADE/okPuWGHRwko/s1600-h/variable6.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SsCRNvphQXI/AAAAAAAAADE/okPuWGHRwko/s320/variable6.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><span style="font-family: Arial,Helvetica,sans-serif;">So here you can see that the logged in user has a session variable VAR which holds all the values returned from the row-wise initialisation.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">One way this technique is used in the Oracle Business Intelligence Applications is to go and fetch the web groups that the user should be added to from the source database. For instance, if Siebel is the source then an intialisation block gets all the responsibilities for the user from the Siebel DB and assigns these to a variable called GROUPS. In this way any repsonsiblities that the user is associated to, which have exactly the same name as groups existing in the rpd, will be automatically associated to the user at login. <br />
</span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com5tag:blogger.com,1999:blog-8841585733141037946.post-13522340822311333452009-09-28T07:52:00.003+01:002009-09-28T07:54:13.117+01:00JavaScript help item<div style="font-family: Arial,Helvetica,sans-serif;">One thing that I have found useful at a few different clients is a small item that can be added to a dashboard which helps the user to understand what the dashboard is supposed to achieve, how to use it etc. This can be accomplished fairly easily with a static text view on a report that just brings back a very easy result. However, sometimes this will be even better received if you add a bit of style to it. It is very easy to make an expanding/collapsing item using some simple javascript and css. Here is a very simple example:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">Initial state of item:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/Sr-8t_XG8RI/AAAAAAAAACE/6fG9SHQadiE/s1600-h/JavaScript1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/Sr-8t_XG8RI/AAAAAAAAACE/6fG9SHQadiE/s320/JavaScript1.png" /></a><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">After clicking the "Show Help" link:<br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: center;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/Sr-9Og2aXII/AAAAAAAAACU/sFEXQq1qcIM/s1600-h/JavaScript2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/Sr-9Og2aXII/AAAAAAAAACU/sFEXQq1qcIM/s320/JavaScript2.png" /></a><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
Clicking the Hide Help link will then collapse the item again, this adds a bit of style to the help item and saves screen real-estate on the dashboard when the help is not needed.<br />
<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">This can be easily created in a static text item following these steps:<br />
</div><ol style="font-family: Arial,Helvetica,sans-serif;"><li>Create a very simple report, for example just put the year column from one of the date dimensions in the request and limit it to 2009, something that will return very quickly. You have to have a query underneath a request, even if you are only exposing a static text view.<br />
</li>
<li>Create a static text view, ensure the "Contains HTML Markup" box is <b>ticked </b>otherwise it won't work properly. <br />
</li>
<li>Put the HTML in the box, here is the HTML for my simple example:</li>
</ol><span style="font-family: "Courier New",Courier,monospace;"><html></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <head></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <meta http-equiv="content-type" content="text/html;charset=ISO-8859-1"></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <style type="text/css"></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <!--</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> div.wrapper {</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> text-align:left;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">margin:0 auto;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">width:500px;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">border:2px solid #1358A8;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">background: #9ABFDC;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">padding:10px;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">}</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> #myvar{</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">border:1px solid #1358A8;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">background:#EAEFF5;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">padding:20px;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">font-family:verdana;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> a.link{</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">color:#1358A8;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">cursor:pointer;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">font-family:verdana;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">font-weight:bold;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">text-decoration: underline;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> p.custom{</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">font-family: verdana;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">color: #586073;</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> --></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </style></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <script type="text/javascript"></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <!--</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> function switchMenu(obj) {</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">var el = document.getElementById(obj);</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">var linkVar = document.getElementById("showhidelink");</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">if ( el.style.display != "none" ) {</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">el.style.display = 'none';</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">linkVar.innerHTML = "Show Help";</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> else {</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">el.style.display = 'block';</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </span><span style="font-family: "Courier New",Courier,monospace;">linkVar.innerHTML = "Hide Help";</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> }</span><br />
<span style="font-family: "Courier New",Courier,monospace;"> //--></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </script></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </head></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <body></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <div class="wrapper"></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <p align=center><a title="Show/Hide" onclick="switchMenu('myvar');" class="link" id="showhidelink">Show Help</a></p></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <div id="myvar" style='display:none;'></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <p class = "custom">Here is some help text to give details on what this dashboard is showing</p></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> <p class = "custom">And another paragraph with some more information</p></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </div></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </div></span><br />
<span style="font-family: "Courier New",Courier,monospace;"> </body></span><br />
<span style="font-family: "Courier New",Courier,monospace;"></html></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">As you can imagine using some more complex css means you can make this look even better and make it match any company branding guidelines for your client. </span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com0tag:blogger.com,1999:blog-8841585733141037946.post-73750326090610843872009-09-24T22:23:00.003+01:002009-09-24T22:28:38.478+01:00DAC server and OC4J as Windows services<div style="font-family: Arial,Helvetica,sans-serif;">As I'm sure you are all aware the DAC server runs as a batch command which lives in the user's session, so as soon as that session ends the command dies and the DAC server stops. This is a real pain for a server side app as at many clients the remote desktop sessions for users are automatically disconnected after a period of time. One way to get around this is to create scheduled server tasks to kick off the DAC server process just before the ETL starts. This is far from ideal however.<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">So, a while ago I came across this posting on MySupport (or Metalink as it was then) <span style="font-family: Arial,Helvetica,sans-serif;">(ID 578174.1) </span>which gave a suggestion for using a free utility called JavaService to install OC4J as a service. The utility is available here:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><a href="http://forge.ow2.org/projects/javaservice/">http://forge.ow2.org/projects/javaservice/</a><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: Arial,Helvetica,sans-serif;">This got me to thinking about whether I could get the DAC server to run as a service, after a lot of playing around I finally got it working properly, you need to create the service using JavaService with this command:<br />
</div><div style="font-family: Arial,Helvetica,sans-serif;"><br />
</div><div style="font-family: "Courier New",Courier,monospace;">javaservice -install "Oracle BI: DAC Service" "C:\Progra~1\Java\jdk1.5.0_17\jre\bin\client\jvm.dll" -Xms256m -Xmx1024m "-Djava.class.path=D:\OracleBI\DAC\lib\ojdbc14.jar;D:\OracleBI\DAC\DAWSystem.jar;E:\OracleBI\;D:\OracleBI\DAC\lib\ant-1.6.5.jar;D:\OracleBI\DAC\lib\antlr-2.7.6.jar;D:\OracleBI\DAC\lib\ant-antlr-1.6.5.jar;D:\OracleBI\DAC\lib\asm.jar;D:\OracleBI\DAC\lib\asm-attrs.jar;c3p0-0.9.0.jar;D:\OracleBI\DAC\lib\cglib-2.1.3.jar;D:\OracleBI\DAC\lib\checkstyle-all.jar;D:\OracleBI\DAC\lib\cleanimports;D:\OracleBI\DAC\lib\concurrent-1.3.2.jar;D:\OracleBI\DAC\lib\connector.jar;D:\OracleBI\DAC\lib\dom4j-1.6.1.jar;D:\OracleBI\DAC\lib\ehcache-1.2.3.jar;D:\OracleBI\DAC\lib\hibernate3.jar;D:\OracleBI\DAC\lib\jaas.jar;D:\OracleBI\DAC\lib\jacc-1_0-fr.jar;D:\OracleBI\DAC\lib\javassist.jar;D:\OracleBI\DAC\lib\jaxen-1.1-beta-7.jar;D:\OracleBI\DAC\lib\jboss-cache.jar;D:\OracleBI\DAC\lib\jboss-common.jar;D:\OracleBI\DAC\lib\jboss-jmx.jar;D:\OracleBI\DAC\lib\jboss-system.jar;D:\OracleBI\DAC\lib\jdbc2_0-stdext.jar;D:\OracleBI\DAC\lib\jgroups-2.2.8.jar;D:\OracleBI\DAC\lib\jta.jar;D:\OracleBI\DAC\lib\junit-3.8.1.jar;D:\OracleBI\DAC\lib\log4j-1.2.11.jar;D:\OracleBI\DAC\lib\proxool-0.8.3.jar;;D:\OracleBI\DAC\lib\versioncheck.jar;D:\OracleBI\DAC\lib\xerces-2.6.2.jar;D:\OracleBI\DAC\lib\xml-apis.jar;D:\OracleBI\DAC\lib\jsr173_api.jar;D:\OracleBI\DAC\lib\sjsxp.jar" "-Duser.dir=D:\OracleBI\DAC" -start com.siebel.etl.net.QServer -description "Oracle BI DAC Server Service"<br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">Long I know :-) but the DAC server needs all those jars to work properly. Obviously you need to change the paths if your OBIEE instance is not installed in D:\OracleBI</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">This should create a service which you can start and stop as any other Windows service and runs a perfectly functioning DAC server.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">To get back to the OC4J document I found on MySupport (ID 578174.1) I tried it and initially it appeared to work but I soon realised that there were problems with the BI Publisher server (which runs in OC4J) which meant that the BI Publisher server could not access the report repository properly.</span><br />
<br />
<div style="font-family: Arial,Helvetica,sans-serif;">So I got to work trying to find out why this didn't work and eventually I worked it out, so here are the steps to get an OC4J windows service which allows the BI Publisher to work fully:<br />
</div><ol style="font-family: Arial,Helvetica,sans-serif;"><li>Copy the JavaService.exe executable to the directory OracleBI\oc4j_bi\bin you can rename it to something else if you want, I named it OC4JService.exe. <b>It is vitally important that you copy the executable to this location and use this executable to create the service, otherwise you will have problems with the BI Publisher server not being able to access the BI Publisher repository.</b></li>
<li>Using this executable create the Windows service<span style="font-family: "Courier New",Courier,monospace;">.</span></li>
<li><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;">OC4JJavaservice -install "Oracle BI: OC4J Service" "D:\jre1.5.0_17\bin\client\jvm.dll" -XX:MaxPermSize=128m "-Djava.class.path=D:\OracleBI\oc4j_bi\j2ee\home\oc4j.jar" -start oracle.oc4j.loader.boot.BootStrap -params -out "D:\OracleBI\oc4j_bi\j2ee\home\log\oc4j.log.txt" -err "D:\OracleBI\oc4j_bi\j2ee\home\log\oc4.err.txt" -current "D:\OracleBI\oc4j_bi\bin" -description "Oracle BI Publisher Server"</span></span><br />
</li>
</ol><span style="font-family: Arial,Helvetica,sans-serif;"> </span><span style="font-family: Arial,Helvetica,sans-serif;"> Obviously the first parameter is a personal choice for the service name. The second parameter is the location of the jre to use. Leave the third parameter as is. The fourth parameter is the location of the oc4j.jar, the main jar for OC4J. The fifth parameter is the class to start from that jar, leave this as is. The parameters after -params are optional and are just an output log, an error log and a description for the service.<br />
</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;">Once the command in number 2 has finished the Windows service should be created and it should start, stop and operate normally. The BI Publisher running from this service should work correclty and have full access to the report repository.</span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com9tag:blogger.com,1999:blog-8841585733141037946.post-6772152689984522342009-09-24T10:41:00.001+01:002009-09-24T10:42:48.427+01:00OBIEE Security<div style="font-family: Arial,Helvetica,sans-serif;">OBIEE security boils down into 2 different types:<br />
</div><ul><li style="font-family: Arial,Helvetica,sans-serif;">Object level security</li>
<li><span style="font-family: Arial,Helvetica,sans-serif;">Data level security</span></li>
</ul><span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;">Object Level Security</span></span><br />
<span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">Each object in the RPD can be secured by user group to restrict access.</span> </span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/Srsw-6bilUI/AAAAAAAAABs/Ifr4KUMf6n4/s1600-h/Security1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/Srsw-6bilUI/AAAAAAAAABs/Ifr4KUMf6n4/s320/Security1.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: center;"> <a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/SrsxBsFLdlI/AAAAAAAAAB0/cYErRfah77I/s1600-h/Security2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/SrsxBsFLdlI/AAAAAAAAAB0/cYErRfah77I/s320/Security2.png" /></a><br />
</div><br />
<span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">In this picture everyone has access to this presentation table. To restrict access you click on the tick to change it to a cross for everyone. You can then give access only to specific groups by changing the box to a tick for those groups.</span> <br />
</span></span><br />
<span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;">Data Level Security</span></span><br />
<span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">Data level security is also controlled by user group, filters are defined against user groups and these filters then restrict the data returned to each user by manipulating the WHERE clause in the SQL generated by the server.</span></span></span><br />
<br />
<span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: small;">For instance:</span></span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://3.bp.blogspot.com/_1p_2tFE6snQ/SrsyV6YCyqI/AAAAAAAAAB8/xfKgvBCm-d4/s1600-h/Security3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://3.bp.blogspot.com/_1p_2tFE6snQ/SrsyV6YCyqI/AAAAAAAAAB8/xfKgvBCm-d4/s320/Security3.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">Here you can see the filters that are defined for the user group "Primary Org-Based Security". If we take one example:</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">When a user creates a query including the logical table Core."Dim - Opportunity" the OBIEE server will look up the physical column for the logical column Core."Dim - Opportunity".VIS_PR_BU_ID, and add a clause for this column to the WHERE clause of the SQL query generated. In the case above we are using a session variable called ORGANIZATION, this is generated at login by row-wise initialisation.</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">So we end up with this on the end of the SQL Query:</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: "Courier New",Courier,monospace; text-align: left;">WHERE W_OPTY_D.VIS_PR_BU_ID IN ('1-A1233','1-D453G','1-98GT2')<br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">And so the user will only see data from their organizations. This exact example is based on Analytics Apps 7.9.5 using Siebel as the source OLTP system. So the ORGANIZATION variable is initialised by getting all the user's orgs from the Siebel DB in a SQL statement; you may need to create something similar for your application yourself.</span><br />
</div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com14tag:blogger.com,1999:blog-8841585733141037946.post-5410685481835169652009-09-23T11:35:00.023+01:002009-09-27T20:37:40.213+01:00OBIEE Query Caching<span style="font-family: Arial,Helvetica,sans-serif;">This is something I see come up on the OTN forums every now and then so I thought I would put together a post explaining how OBIEE query caching works and how to administer it.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;">Introduction</span></span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"></span></span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">OBIEE includes two levels of caching, one level works at the OBIEE server level and caches the actual data for a query returned from the database in a file, the other level is at the OBIEE Presentation Server. This post will concentrate on the former.</span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif; font-size: large;">NQSConfig</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">Caching is enabled and configured in the NQSConfig.ini file located at <oraclebi>\server\Config, look for this section:</oraclebi></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><oraclebi><span style="font-family: "Courier New",Courier,monospace;">###############################################################################</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">#</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;"># Query Result Cache Section</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">#</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">###############################################################################</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">[ CACHE ]</span><br style="font-family: "Courier New",Courier,monospace;" /><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">ENABLE = YES;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">// A comma separated list of <directory maxsize=""> pair(s)</directory></span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">// e.g. DATA_STORAGE_PATHS = "d:\OracleBIData\nQSCache" 500 MB;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">DATA_STORAGE_PATHS = "C:\OracleBIData\cache" 500 MB;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">MAX_ROWS_PER_CACHE_ENTRY = 100000; // 0 is unlimited size</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">MAX_CACHE_ENTRY_SIZE = 1 MB;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">MAX_CACHE_ENTRIES = 1000;</span><span style="font-family: "Courier New",Courier,monospace;"> </span></oraclebi></span></span></span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><oraclebi><span style="font-family: "Courier New",Courier,monospace;">POPULATE_AGGREGATE_ROLLUP_HITS = NO;</span><br style="font-family: "Courier New",Courier,monospace;" /><span style="font-family: "Courier New",Courier,monospace;">USE_ADVANCED_HIT_DETECTION = NO;</span></oraclebi></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><oraclebi><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">The first setting here <span style="font-family: "Courier New",Courier,monospace;">ENABLE</span> controls whether caching is enabled on the OBIEE server or not. If this is set to <span style="font-family: "Courier New",Courier,monospace;">NO</span> then the OBIEE server will not cache any queries, if it is set to <span style="font-family: "Courier New",Courier,monospace;">YES</span> then query caching will take place.</span> </span></oraclebi></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><oraclebi><span style="font-family: "Courier New",Courier,monospace;">DATA_STORAGE_PATHS<span style="font-family: Arial,Helvetica,sans-serif;"> is a comma delimited list of paths and data limits used for storing the cache files. This can include multiple locations for storage i.e. <span style="font-family: "Courier New",Courier,monospace;"> </span></span></span></oraclebi></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">C:\OracleBIData\cache" 500 MB, </span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">C:\OracleBIData\cache2" 200 MB</span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><oraclebi><span style="font-family: "Courier New",Courier,monospace;"></span>. Note that although the docs say that the maximum value is 4GB the actual max value is </oraclebi></span></span></span>4294967295.<br />
<br />
<span style="font-family: "Courier New",Courier,monospace;">MAX_ROWS_PER_CACHE_ENTRY</span><span style="font-family: Arial,Helvetica,sans-serif;"> is the limit for the number of rows in a single query cache entry, this is designed to stop huge queries consuming all the cache space.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">MAX_CACHE_ENTRY_SIZE<span style="font-family: Arial,Helvetica,sans-serif;"> is the maximum physical size for a single entry in the query cache. If there are queries you want to enter the cache which are not being stored consider increasing this value to capture those results, Oracle suggest not setting this to more than 10% of the max storage for the query cache.</span></span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">MAX_CACHE_ENTRIES</span></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"> </span></span></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;">is the maximum number of cache entries that can be stored in the query cache, when this limit is reached the entries are replaced using a Least Recently Used (LRU) algorithm.</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">POPULATE_AGGREGATE_ROLLUP_HITS</span></span></span></span><span style="font-family: Arial,Helvetica,sans-serif;"> is whether to populate the cache with a new entry when it has aggregated data from a previous cache entry to fulfil a request. By default this is NO.</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><br />
</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;">USE_ADVANCED_HIT_DETECTION<span style="font-family: Arial,Helvetica,sans-serif;"> if you set this to YES then an advanced two-pass algorithm will be used to search for cache entries rather than the normal one-pass. This means you may get more returns from the cache, but the two-pass algorithm will be computationally more expensive.</span></span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-family: Arial,Helvetica,sans-serif;">Managing cache</span></span></span></span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"> The cache can be managed by opening the Administration Tool and connecting online to the OBIEE server. Go to Manage -> Cache:</span></span></span></span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/SrnsR1RePNI/AAAAAAAAAA4/JKfVRlZPyDM/s1600-h/Cache1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/SrnsR1RePNI/AAAAAAAAAA4/JKfVRlZPyDM/s320/Cache1.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;">This will open a window showing you the cache as it currently is with all entries:<br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div style="text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/Srnsen3-UdI/AAAAAAAAABA/z-3XtM4mJWU/s1600-h/Cache2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/Srnsen3-UdI/AAAAAAAAABA/z-3XtM4mJWU/s320/Cache2.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">This contains some very interesting information about each entry such as when it was created, who created it, what logical SQL it contains, how mnay rows are in the entry, how many times the entry has been used since it was created etc. <br />
</span></span></span></span></span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">To purge cache entries select them in the right hand window and right click, choose purge from the context menu:</span></span></span></span></span><br />
<br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://1.bp.blogspot.com/_1p_2tFE6snQ/Srqgy7cFd8I/AAAAAAAAABk/rjbWDiB8Lns/s1600-h/Cache2a.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://1.bp.blogspot.com/_1p_2tFE6snQ/Srqgy7cFd8I/AAAAAAAAABk/rjbWDiB8Lns/s320/Cache2a.png" /></a><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;">This will remove those cache entries from the query cache and any request which would previously have been fulfilled by this cache entry will instead go to the database (and create a new cache entry). This feature is essential if you make a data fix to the underlying data warehouse and need to ensure this fix is displayed in OBIEE as cache entries will then be stale (outdated because the underlying data does not match the data in the cache any more).</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;">Cache Persistence Timing</span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">In addition to the global cache settings you can set the cache persistence individually on each physical table:</span></span></span><br />
</div><div class="separator" style="clear: both; text-align: center;"><br />
</div><div style="text-align: center;"></div><div style="text-align: center;"></div><div style="text-align: center;"></div><div style="text-align: center;"><a href="http://4.bp.blogspot.com/_1p_2tFE6snQ/Srn0mYf7T-I/AAAAAAAAABY/qSHkuFm_Zxs/s1600-h/Cache4.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://4.bp.blogspot.com/_1p_2tFE6snQ/Srn0mYf7T-I/AAAAAAAAABY/qSHkuFm_Zxs/s320/Cache4.png" /></a><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;">The first option is to make the table cacheable or not, if the table is cacheable then there are a number of other options; "Cache never expires" which is self-explanatory and "Cache persistence time" which accepts a number and a period (days, hours, minutes, seconds). This period and number defines how long an entry for that table will be kept in the query cache, after this time period expires the cache entry for this table will be removed.<br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif; font-size: large;">Programmatically purging the cache</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;">Oracle provide a number of ODBC calls which you can issue to the OBIEE server over the Oracle provided ODBC driver to purge the cache these are:<br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;">SAPurgeCacheByQuery(x)</span> - This will purge the cache of an entry that meets the logical SQL passed to the method</span>.<br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;">SAPurgeCacheByTable(x,y,z,m)</span><span style="font-family: Arial,Helvetica,sans-serif;"> - This will purge the cache for all entries for the specified physical table where:</span><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">x = Database name</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;">y = Catalog name</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;">z = Schema name</span><br />
<span style="font-family: Arial,Helvetica,sans-serif;">m = Table name</span> <br />
<div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-family: "Courier New",Courier,monospace;">SAPurgeAllCache()</span> - This will purge all entries from the query cache.</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;">These ODBC calls can be called by creating a .sql file with the call in it, i.e.:<br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;">Call SAPurgeAllCache();</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: Arial,Helvetica,sans-serif; text-align: left;">Then create a script to call it, on Windows you can create a .cmd file with the contents:<br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;">nqcmd -d "AnalyticsWeb" -u Administrator -p [admin password] -s [path to sql file].sql -o [output log file name]</span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">This will use nqcmd to open an ODBC connection to the ODBC AnalyticsWeb and then run your .sql file which will purge the cache.</span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;">I have used this many times as the only step in a custom Informatica WF which is then added as a task in the DAC and set as a following task for an Execution Plan. This way after the ETL the cache will be purged automatically and there is no risk of stale cache entries. </span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;">Event Polling Tables</span></span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">Another method for purging the cache selectively is to use event polling tables. These are tables which the OBIEE server polls regularly, when it finds a row in the table is processes it and purges any cache entries for the table referenced in the row. Your ETL process could be configured to add rows to an event polling table when it has updated a table and thus ensure the query cache is not stale. An example event polling table:</span></span></span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; font-family: "Courier New",Courier,monospace; text-align: left;"><span style="font-size: large;"><span style="font-size: small;">create table UET (<br />
UpdateType Integer not null,<br />
UpdateTime date DEFAULT SYSDATE not null,<br />
DBName char(40) null,<br />
CatalogName varchar(40) null,<br />
SchemaName varchar(40) null,<br />
TableName varchar(40) not null,<br />
Other varchar(80) DEFAULT NULL<br />
); </span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;">Once this table has been created you need to import it into the physical layer and then use the Tools -> Utilities -> Oracle BI Event Tables option to select the table as an event table.</span></span></span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: "Courier New",Courier,monospace;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-size: large;">Online Modification of RPD<span style="font-size: small;"> </span></span></span></span></span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><span style="font-size: large;"><span style="font-size: small;"><span style="font-size: large;"><span style="font-size: small;">If you modify the RPD online, then any changes that affect a Business Model will force a purge of the cache for any entries relating to that Business Model.</span> </span></span></span></span><br />
</div><div class="separator" style="clear: both; text-align: left;"><span style="font-family: Arial,Helvetica,sans-serif;"><br />
Well I hope that is useful, it is by no means a complete document on Query Caching, but it should give a good overview and some ideas on managing the cache in a live environment.</span><br />
</div>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com2tag:blogger.com,1999:blog-8841585733141037946.post-53018174897610410572009-09-23T08:39:00.002+01:002009-09-25T08:13:50.983+01:00DAC import/export<div style="font-family: Arial,Helvetica,sans-serif;">I came across an interesting foible of the DAC today after opening an SR with Oracle. Apparently using the import function and not selecting Truncate Repository Tables will result in extremely unpredictable results, for instance image suffixes on tables not being imported etc:<br />
</div><br />
<div class="separator" style="clear: both; text-align: center;"><a href="http://2.bp.blogspot.com/_1p_2tFE6snQ/SrnPdxNrXRI/AAAAAAAAAAw/3uAU07Eru4o/s1600-h/TruncateRepositoryTables.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/_1p_2tFE6snQ/SrnPdxNrXRI/AAAAAAAAAAw/3uAU07Eru4o/s320/TruncateRepositoryTables.png" /></a><br />
</div><br />
<span style="font-family: Arial,Helvetica,sans-serif;">The general advice from Oracle seems to be always use the Truncate Repository Tables option, although this means you'll always have to export/import all Applications/SSC which is a pain as the import process is so incredibly slow.</span><br />
<br />
<div style="font-family: Arial,Helvetica,sans-serif;">Also, when exporting you will often find an error in the export.log like this:<br />
</div><br />
<span style="font-family: "Courier New",Courier,monospace;">W_ETL_SA objects: 31</span><br />
<span style="font-family: "Courier New",Courier,monospace;">37 INFO Fri Sep 18 19:02:38 CEST 2009 Exporting entity W_ETL_SA for application Universal</span><br />
<span style="font-family: "Courier New",Courier,monospace;">38 INFO Fri Sep 18 19:02:38 CEST 2009 Moved W_ETL_SA: 62 objects</span><br />
<span style="font-family: "Courier New",Courier,monospace;">39 INFO Fri Sep 18 19:02:38 CEST 2009 Exporting entity W_ETL_SA for application Siebel 8.0 Vertical</span><br />
<span style="font-family: "Courier New",Courier,monospace;">40 WARNING Fri Sep 18 19:02:38 CEST 2009 Mismatch between the number of records in the object reference table and objects</span><br />
<br />
<span style="font-family: Arial,Helvetica,sans-serif;">According to Oracle these errors can be safely ignored, personally I find them a bit disconcerting as I am worried that some objects are being missed in the import...</span>Matthttp://www.blogger.com/profile/06655817850027924176noreply@blogger.com1