Frank Davis' Blog: OBIEE ONE

Calculating grand total column or % of grand total is easy in pivot table view.  But how can I create the same in a table view?  Well in table view you can create a grand total row with the table view options!  But, that is not what we want.  We want it so that you either have grand total as a column or % grand total.  To do this I will be using sample sales to demonstrate.

First we create the base report.  This base report will have the Month column and the monthly revenue.

The result of this base report is:

Now we want to include the grand total as a column for each row.  To do this we create a new column called grand total.

With expression:

See what we did!  We wrote the expression formula to sum() the metric.  This metric is already defined in the metadata or BMM layer as a metric with aggregation rule of sum.  So by wrapping or using this metric within a sum() within the report, we are telling the BI Server we want the sum of the aggregated sum(s) or pretty much the Grand total.  Here are the results:

Now let’s say we do not want to show the Grand Total column per row but the actual % of Grand Total.  Here we change the Grand Total columns column heading and expression:

Note:  We also needed to change the column properties Data Format to properly show the data in the correct format.

Here are the results.

So there is how you create a “Grand Total” or “% grand total” in table view.

I have been seeing a lot where people take paint or samplesales webcat and either cloning it or modifying it to make a new webcat.  This has its issues and problems.  For one, you inherit all the users and privileges that are there, especially if you were using and modifying this before you decided that it was not a good idea and that you should have a webcat unique for your area or application.

Why do all that work? There is a faster and better way to make a new webcat.  Fastest and easiest way to make brand new webcat:

1. Modify ORACLEBIDATA_HOME/web/config/instanceconfig.xml. Change location of webcat to that of the new webcat.  For my example, I changed from samplesales to testCat.

E:/OracleBIData/web/catalog/testCat</CatalogPath>

2. Restart Presentation Services

Thats it! Simple right.  Well this is what is happening when you restart presentation services.  Presentation Services looks at configuration and see that the webcat to use is testCat.  It looks for webcat in the location specified to load.  If it does not find it, it creates one for you.

It has been a long time, couple of months, since I last posted.  But busy beaver that I am, took me away from blogging.  Anyways, I got a breather and will add-on.

Filters and the ‘is prompted’ operator.  Many might think they know how this works and get shocked when things just don’t quite work out the way they do.  For instance lets say for some reason, you created an answers request which has ‘QTR’ filtered for a static value, lets say ’2007 Q4′.  This report then is pretty static right. It is a report for only that given quarter.  Add this to a dashboard page which has a prompt set for same ‘qtr’ field.  And play around with selecting values and submitting it.  What, the report changes!!! “How can this be, I did not set that field to ‘is prompted’ operator!”  As you scratch your head and ponder what has just happened.  Well it appears that adding static filters to a report and adding to a dashboard with same field filtered does the following.  If no values are selected, the report will always be for ’2007 Q4′.  But when the user selects different quarters, the static (default) value will be overwritten with the users selections.  So the prompted values over rule any static filters added.  Unless of course, how Nag commented below and reminded me,  the criteria filter protection option allows the user/developer to protect the filter from this overwriting rule from dash prompts.

Now lets say we add two filters to same said report for same field ‘qtr’.  One is for static value ’2007 Q4′ and the other is for ‘is prompted’.  You might think that the report will now correct itself, that is in your mind, and filter by the static value ‘AND’ the selected prompted values.  WRONG!  The prompted values will now be in both filter criteria as shown below with the filter view.

Changing the filter criteria logic to ‘OR’ does not help either.  As seen here:

Now lets see if setting the filter criteria to a variable, lets say a repository variable.  What happens now?  Same thing!  The filter’s bound values will be overwritten by selected value(s) from the prompt.

Now how to get around this.  Well, prompts replace the bound values by the fields they are representing.  For our example qtr field.  We can fake this or alter the behavior either by changing the prompt expression or filter expression as easily as:

case when 1=1 then qtr end  = ’2007 Q4′ and keeping the other filter is prompted.  Since the expression for the filter is just not ‘qtr’ anymore it will not be overwritten.  As follows:

This was done using ‘AND’ logic.  We just change the logic to ‘OR’ and we will get the static value and the selected values.

So remember or think about this when you have a filter not working quite like you expect it to.  This will give you more options in your filtering and prompting.  But please keep in mind whatever you use in the filter, like the case statement I used as example goes to the DBMS in the predicate and if that field is indexed, the index will no longer be used for that predicate.

I have been asked to create custom functionality whereby certain dashboard prompts are required, and the user should not be able to continue until all required fields are selected before they can query.  So I ventured out and created the following script below to do just that.  This script is in addition to the ‘Global’ Go button, or what I referred to in a previous post as the ‘Custom’ Go button.  What this script does is allow the developer to specify which prompt fields they would like to be required.  If any of the fields are not populated by the user an alert/warning box will popup specifying which fields are required in which they did not populate.  This is done in one alert window instead of multiple in order to not be annoying and done correctly.  The rest of the javascript which pulls the data and posts to the presentation server will not be fired, thus the request is blocked until all required fields are populated.

The script is here and the description/breakdown follows:

<script> function GlobalGo(){
try{
var aElm = document.getElementsByTagName(‘table’);
var tTableArray = new Array();
var k = 0;

//Required Field validation variables
var aSpanElm = document.getElementsByTagName(‘span’);
var aReqFields = new Array();
var oneTimeValExists = ‘N’;
//var oneXXXValExists = ‘N’;
var sCaption;
var errMsg = ”;
var i = 0;

//BEGIN ********** DEVELOPER(s): Add Required Fields here
aReqFields[0] = ‘Brand’;
aReqFields[1] = ‘Region’;
//END **********

//Get Captions and Controls to check for required fields/values
for(var a=0; a<aSpanElm.length; a++){

//Get Caption
if(aSpanElm[a].className==’GFPCaption’){
sCaption = aSpanElm[a].firstChild.data;
}

//Check if required value exists
if(aSpanElm[a].className==’GFPControl’){

var aInput = aSpanElm[a].getElementsByTagName(‘input’); // multi-select and text boxes
var aOption = aSpanElm[a].getElementsByTagName(‘option’);  // dropdown

//Check if a required field
for(var b=0; b<aReqFields.length; b++){
if(sCaption==aReqFields[b]){
var hasValue = ‘N’;

//Check if multi-select or text box with caption has value
for(var c=0; c<aInput.length; c++){

if(aInput[c].value.length > 0){
hasValue = ‘Y’;
}
}

//Check if dropdown with caption has value
for(var c=0; c<aOption.length; c++){

if(aOption[c].selected && aOption[c].value.trim().length > 0){
hasValue = ‘Y’;
}
}

//if Value does not exists add to errMsg

if(hasValue==’N'){errMsg = errMsg+’* ‘ + aReqFields[b] +’ is a Required Field!\n’;}
}
} //EndFor required Fields

//BEGIN ********** DEVELOPER(s): Add Time Fields here
//check if time field
if(sCaption==’Fiscal Period’ || sCaption==’Fiscal Week’ || sCaption==’Dates’|| sCaption==’Year’){
for(var c=0; c<aInput.length; c++){

if(aInput[c].value.trim().length > 0){
oneTimeValExists = ‘Y’;
}
}
} //Endif check time field
//END **********

//BEGIN ********** DEVELOPER(s): This is template check for ‘OR’ed fields or (At Least one)
//check if time field
//if(sCaption==’Caption1′ || sCaption==’Caption2′ || sCaption==’Caption3′){
//  for(var c=0; c<aInput.length; c++){
//
//    if(aInput[c].value.trim().length > 0){
//      {Need to add one time exists variable here} = ‘Y’;
//    }
//  }
//} //Endif field
//END **********

} //Endif GFPControl
} //EndFor Caption and Controls

if(oneTimeValExists==’N'){errMsg = ‘* At least one time field is Required!\n’+errMsg;}
//{Add same if block above for each ‘At least’ that you created with errmsg}

if(errMsg != ”){
alert(errMsg);
return;
}

for(var i=0; i<aElm.length; i++){
if(aElm[i].className==’GFPBox’){
tTableArray[k] = document.getElementById(aElm[i].id);
k++;
}
} //EndFor Get Prompt Values to send in Post
GFPDoFilters_samvi(”, tTableArray, true);

}
catch(e){alert(‘XXX ‘ + e);}
}

String.prototype.trim = function() {
return this.replace(/^\s*|\s*$/, “”);
}
</script>

<div align=”left”><a href=”#” onclick=”javascript:GlobalGo();”>Go</a></div>

<div align=”left”><a href=”#” onclick=”return
PersonalizationEditor.removeDefaultSelection(false) “>Clear</a></div>

Here are the notes:

• The section in red: This is where the developer will add the required fields into the array.  Each field added needs to increment the array index or it will over write.  The names put in the required field area are the Dashboard prompt captions.
• The sections in blue: This section is for the time field validation.  This checks that at least one time field is populated with data.  It also highlights the errMsg used.
• The sections in purple: This is where a developer can use as a template to create the scenario like time where ‘at least’ one field needs to be used.

Here is a screen shot of it working within SampleSales.  One of the required fields is selected (Brand).  And as you can see the alert is shown signifying to the user that ‘at least one time field’ and ‘Region’ are required.  The report is not fired for these prompts until all required validation is met.

Required Field Message

Until next time…I am currently working on script to stop the dashboard page from firing off report queries on entry.  Almost complete this should be added shortly.

-Frank

There are alot of users that wish that reports within dashboards would not fire automatically once entering a dashboard or dashboard page.  So what is the solution?   There is a pretty good solution to this problem out there that uses the concept of guided navigation; but, it doesn’t work.  On the surface it looks like it works but in reality the report queries are still being fired off, as one would be able to see via the NQQUERY.LOG.  Now keep in mind if caching is turned on, and the report queries are cached you may not see the queries in the log file.  But you most definitely will see that they were fired off if you turn caching off.  Now my solution to the problem is as follows:

You will need Dashboard->”Save Selections”, and Dashboard->”Assign Default Selections” privileges.

1. Put the reports that are required to not fire off within a collapsible dashboard section.  The collapsible property is a section property.  Save dashboard page.

2. Now go to your dashboard and the dashboard page.  Clear out all the dashboard prompts’ values.

3. Collapse the sections you do not want to be fired off.

4. Select “page options”->”save current selections”->”For Others”.   A “Save Current Selections” box will appear like below:

Put in the selection name, check off “Make this my default for this page” and then click “Set Permissions”.  Here in the permissions select all groups you want to apply this default selection to, which most likely is all of them.

And you are finished.

One point to make.  Some may ask, Why did I have to clear out dashboard prompts and Won’t my saved selections now not show the default values of the prompts that are set.  The answer is, we cleared out the values of the prompts so that the selection we are saving only saves that the sections are collapsed and that we are not saving any prompt values.  When the prompts are clear it is not saying to clear out prompts whose default values are set via the dashboard prompts.  On the contrary, you will see when you go back to this dashboard page that the default prompts are correctly set and the sections are collapsed.

This might not be the most elaborate solution since the sections are collapsed and it entails that users uncollapse the sections to see the reports and fire them off.  But for the current release 10.3.4.1 this is the best solution that I could come up with that worked.

Now what this will do is show your dashboard prompts and all the sections.  When a user want to then see a report and fire off the query all they will have to do is click on the collapsible button, which will open it up and fire off all reports contained.

How this works is that all sections that are collapsed are disabled and will not be enabled until opening up.  The guided navigation solution fired reports off since all that it does is hide the section but the section is still enabled.

During migration or copying of presentation web catalogs, some will no doubt come across this error for users when they log in:

access denied for user to path/users/{username}/_portal/dashboard layout

What this generally is saying is that the user does not have access to their dashboard or any dashboard.  They could type ‘answers’ after the ‘?’ in the URL and be able to access answers, if they have that privilege.  Pretty much what happened is that the migration/copy of the web catalog user folder did not copy the users’ permissions correct.

If you want, you could open the source web catalog that was copied and the web catalog that has the issue via cat manager and see the permissions for that user folder for each web catalog.  You should see that the permissions are different.

Now to fix the issue.  It is pretty simple, open cat manager and set permissions for the user’s folder to that user.  So the user needs at least ‘Change/Delete’ permissions on that folder and have this permission set recursively to sub-items and sub-folders.

And that should fix it.

-Frank

If one installs scheduler via the typical setup; they will not or should not have any issues.  It is pretty simple.  You need a database for your scheduler tables(This is a given and requirement).  You need the scheduler service and you need the presentation service.  Now, you really don’t need the presentation services if you are just using scheduler to run only bi publisher jobs!  But don’t get me started on BI Punisher…err… I mean BI Publisher(This is another post topic entirely).  Scheduler is just a service whose sole purpose is.. you guessed it…scheduling jobs.  We use the scheduler with presentation server for iBots.  iBots are presentation server specific jobs to pre-run long running reports or run reports to be sent via email, dashboards or to other devices.  Oracle highly recommends running scheduler service on the same box as presentation server.  This is for reasons discussed later and also due to fact that scheduler will more than likely be used solely with presentation server to scheduler iBot jobs via Delivers.  Now, don’t get me wrong you could run scheduler on a different box; but, there are more specific configurations needed.  For example, lets say you want to run presentation services on box1, bi server and scheduler on box2.  This is what needs to be done.

1. Configure you scheduler either via the job manager UI or at the root config file, which is located at OracleBIData\scheduler\config\instanceconfig.xml (NOTICE HOW THIS IS LOCATED WITHIN THE ORACLEBIDATA DIRECTORY.  That is because it is presumed to run on the same machine as the presentation services!!!)

You will want to add the tns entry for your scheduler database here along with the userid/password to access scheduler tables. (As seen below)

Here are the contents of the schedulers instanceconfig.  As you can see there are lots of entries.  I find it best to use the UI in order to not make XML errors!  But for those with lots of XML experience its up to you.  As for me I can do both;)  (In any case please keep note of the settings highlighted in blue. PLEASE TAKE NOTICE OF DATABASE PASSWORD AS IT IS ENCRYPTED THAT’S WHY IT IS GOOD TO USE THE UI TOOL TO CONFIGURE ALSO.)

<!– Oracle BI scheduler Config file. –>
<!– This is a generated file, user-added comments may be lost. –>
<!– User added config values will be maintained though. –>
<scheduler>
<ServerInstance>
<AdminName>Administrator</AdminName>
<AdminPswd>a9ff96857fba4f6a</AdminPswd>
<BulkFetchBufferSize>33792</BulkFetchBufferSize>
<CLIType>12</CLIType>
<ClusterPort>9708</ClusterPort>
<DB_Column_Names>
<TABLE_ERRMSGS>S_NQ_ERR_MSG</TABLE_ERRMSGS>
<TABLE_INSTANCES>S_NQ_INSTANCE</TABLE_INSTANCES>
<TABLE_JOBS>S_NQ_JOB</TABLE_JOBS>
<TABLE_PARAMS>S_NQ_JOB_PARAM</TABLE_PARAMS>
</DB_Column_Names>
<DSN>tnsnames_entry_here</DSN>
<DefaultScriptPath>e:\OracleBI\server\Scripts\Common</DefaultScriptPath>
<Java_Extension>
<Java_Host_Server>localhost:9810</Java_Host_Server>
</Java_Extension>
<LogAllSqlStmts>False</LogAllSqlStmts>
<Mail>
<From>Oracle Delivers</From>
<MaxRecipients>0</MaxRecipients>
<Password>a9ff96857fba4f6a</Password>
<SMTP_Port>25</SMTP_Port>
<SMTP_Server>defaultmailserver.com</SMTP_Server>
<Sender>defaultuser@defaultmailserver.com</Sender>
<SmtpCaCertificateDirectory/>
<SmtpCaCertificateFile/>
<SmtpCaCertificateVerificationDepth>0</SmtpCaCertificateVerificationDepth>
<SmtpCipherList/>
<Try>1</Try>
<UseBcc>True</UseBcc>
<UseSSL>False</UseSSL>
<Username/>
</Mail>
<MaxExecThreads>100</MaxExecThreads>
<MinExecThreads>1</MinExecThreads>
<NumDbConnections>5</NumDbConnections>
<PartOfCluster>True</PartOfCluster>
<PauseOnStartup>False</PauseOnStartup>
<PoolPswd>f0126d06538c5ad301b41bb70f50912d0cf9f2f3e7b923d8</PoolPswd>
<PoolTimeout>60</PoolTimeout>
<PoolUsername>S_NQ_SCHED</PoolUsername>
<PortString>9705</PortString>
<PurgeInstDays>7</PurgeInstDays>
<PurgeIntervalMinutes>5</PurgeIntervalMinutes>
<SSL>
<CACertificateDir/>
<CACertificateFile/>
<CertPrivateKeyFileName>e:\OracleBI\server\config\private.pem</CertPrivateKeyFileName>
<CertificateFileName>e:\OracleBI\server\config\public.pem</CertificateFileName>
<CertificateVerifyDepth>0</CertificateVerifyDepth>
<CipherList/>
<PassphraseFileName>e:\OracleBIData\scheduler\config\passphrase</PassphraseFileName>
<PassphraseProgramName/>
<TrustedPeerDNs/>
<UseSSL>False</UseSSL>
<VerifyPeer>False</VerifyPeer>
</SSL>
<SchedulerScriptPath>e:\OracleBI\server\Scripts\Scheduler</SchedulerScriptPath>
<ScriptEngine>
<ScriptRPCPort>9707</ScriptRPCPort>
</ScriptEngine>
<TargetType>46</TargetType>
<TempPath>e:\OracleBIData\tmp</TempPath>
<Workflow>
<Auth_PWD>706b3e23758697344f58fc12b0b1487b</Auth_PWD>
<Auth_User>sadmin</Auth_User>
<Server>http://localhost</Server>
<Service>ANALYTICS</Service>
<System>eai_enu</System>
</Workflow>
<iBots>
<Debug>False</Debug>
<KeepErrorLogFiles>True</KeepErrorLogFiles>
<LogPurgeDays>7</LogPurgeDays>
<Log_Dir>e:\OracleBI\server\Log\iBots</Log_Dir>
<MaxDeliverySleepSecs>10</MaxDeliverySleepSecs>
<MaxGlobalSleepSecs>10</MaxGlobalSleepSecs>
<MaxRequestSleepSecs>10</MaxRequestSleepSecs>
<MaxRowsTimesColumns>10000</MaxRowsTimesColumns>
<MinDeliverySleepSecs>5</MinDeliverySleepSecs>
<MinGlobalSleepSecs>3</MinGlobalSleepSecs>
<MinRequestSleepSecs>2</MinRequestSleepSecs>
<NumDeliveryRetries>4</NumDeliveryRetries>
<NumGlobalRetries>2</NumGlobalRetries>
<NumRequestRetries>3</NumRequestRetries>
<Web_Server>box1:9710</Web_Server>
</iBots>
</ServerInstance>
</scheduler>

2. Run job manager to validate the scheduler services is running correctly and can communicate with the scheduler tables.  You could do this on any machine with the job manager exe but just make it simple and run on the box it is setup on.  In our case box2.  If job manager connects fine to our scheduler all is well with the service itself.  If you have issues, look in OracleBI\server\log\ for the scheduler.log for specific issues.

3. Now since scheduler is verified and running, we need to configure presentation services for scheduler so that it can talk with the scheduler service and create jobs.  First we need to run cryptotools like it says within the infrastructure guide.  Cryptotools.exe can be found in “OracleBI\web\bin” dir.  This tool is used to enter the username and password for scheduler within the credentialstore.xml located in “OracleBIData\web\config” and to encrypt it, if need be, with passphrase.  Scheduler uses the alias of “admin”.  Once this is complete you will need to tell presentation services where the scheduler services is and also to find and locate the credentialstore.xml.  If schedulerServer is not specified correctly you will get error from delivers when trying to create ibot.  Something like service or server can not be found or communication error. (This is not a problem with standard install where sched server is on same machine as presentation server)  Also if you get Authentication error when trying to create iBot this is because the credential store and alias is either not setup or not setup right.

Your instanceconfig.xml for presentation services should now look like this after making entries.  The entries for this step is highlighted in blue.

<?xml version=”1.0″ encoding=”utf-8″?>
<WebConfig>
<ServerInstance>
<DSN>AnalyticsWeb</DSN>
<CatalogPath>E:/OracleBIData/web/catalog/samplesales</CatalogPath>
<Alerts>
<ScheduleServer>box2</ScheduleServer>
</Alerts>
<AdvancedReporting>
<ReportingEngine>XmlP</ReportingEngine>
<Volume>XmlP</Volume>
<ServerURL>http://FRANK-G8ANIAY3K:9704/xmlpserver/services/XMLPService</ServerURL>
<WebURL>http://FRANK-G8ANIAY3K:9704/xmlpserver</WebURL>
<AdminURL>http://FRANK-G8ANIAY3K:9704/xmlpserver/servlet/admin</AdminURL>
<AdminCredentialAlias>bipublisheradmin</AdminCredentialAlias>
</AdvancedReporting>
<JavaHome>C:\Program Files (x86)\Java\jdk1.5.0_12</JavaHome>
<BIforOfficeURL>client/OracleBIOffice.exe</BIforOfficeURL>
<!– To configure a limited set of languages to be available to users uncomment the <AllowedLanguages> tag below and choose a subset set of language tags from the list. Values must be comma separated. –>
<!– <AllowedLanguages>ar,cs,da,de,el,en,es,fi,fr,hr,hu,it,iw,ja,ko,nl,no,pl,pt,pt-br,ro,ru,sk,sv,th,tr,zh,zh-tw</AllowedLanguages> –>
<!– To configure a limited set of locales to be available to users uncomment the <AllowedLocales> tag below and choose a subset set of locale tags from the list. Values must be comma separated. –>
<!– <AllowedLocales>ar-dz,ar-bh,ar-dj,ar-eg,ar-iq,ar-jo,ar-kw,ar-lb,ar-ly,ar-ma,ar-om,ar-qa,ar-sa,ar-so,ar-sd,ar-sy,ar-tn,ar-ae,ar-ye,cs-cz,da-dk,de-at,de-ch,de-de,de-li,de-lu,el-gr,en-au,en-ca,en-cb,en-gb,en-hk,en-ie,en-in,en-jm,en-nz,en-ph,en-us,en-za,en-zw,es-ar,es-bo,es-cl,es-co,es-cr,es-do,es-ec,es-es,es-gt,es-hn,es-mx,es-ni,es-pa,es-pe,es-pr,es-py,es-sv,es-uy,es-ve,fi-fi,fr-be,fr-ca,fr-ch,fr-fr,fr-lu,fr-mc,hr-hr,hu-hu,id-id,it-ch,it-it,iw-il,ja-jp,ko-kr,ms-my,nl-be,nl-nl,no-no,pl-pl,pt-br,pt-pt,ro-ro,ru-ru,sk-sk,sv-fi,sv-se,th-th,tr-tr,zh-cn,zh-mo,zh-sg,zh-tw</AllowedLocales> –>
<!– <Disconnected><ArchiveIbots>true</ArchiveIbots><DisconnectedDir>disconnected</DisconnectedDir></Disconnected> –>
<CredentialStore>
<CredentialStorage type=”file” path=”e:\oraclebidata\web\config\credentialstore.xml”/>
</CredentialStore>

</ServerInstance>
</WebConfig>

4. One last thing to remember and this is important and is typically overlooked.  You need to configure scheduler to communicate with the presentation server.  This way when it runs reports via an iBot, it can run them.  Scheduler runs iBots through presentation server as that is where the reports themselves live.  And then presentation server in turn submits the report to BI Server to fulfill the logical data request like usual.  If you setup scheduler on another box other than presentation server you might get the 77030 error when running an ibot job since the default value for this is local:9710.

So thinking about it, it does not make sense to have scheduler on another box as it needs to communicate directly with presentation server.  Thus one more network hop to get a job complete.  In our example scheduler on box2 needed to communicate to box 1 (presentation server) to run an iBot job.  Then box1 (presentation server) need to communicate to box 2(bi server) to run the report.  If we kept presentation server and scheduler on box1 then we would eliminate that one network request as it would just be local request.

If you have any ?s or comments please let me know and I will try to answer them.

-Frank