OBIEE Date Expressions Reference

During a recent project I had several requirements that involved converting dates to the first or last day of the current month, past month, future month etc. Thanks to that project I’ve become proficient at Date manipulations and thought I would share some of the expressions I had to use on the project and how they worked. For people who have not worked a lot with OBIEE Date manipulation this may come in handy since OBIEE does not offer built in functions like Oracle which has the LAST_DAY function to return the last day of a given month.

Any date manipulation within OBIEE will utilize the TIMESTAMPADD function. For the examples shown in the table below I used CURRENT_DATE as the starting point for all dates. You can replace CURRENT_DATE with whatever valid Date type you need as a starting point or can CAST a CHAR value to a Date type if needed.

The table below describes what is being calculated, the expression to perform the calculation, and then an explanation that explains how the expression arrives at the desired date value.

Date Calculation	OBIEE Expression	Explanation
First Day of the Previous Year	TIMESTAMPADD( SQL_TSI_YEAR , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.
First Day of the Current Year	TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)	This calculation returns the first day of the year by deducting one less than the total number of days in the year.
First Day of the Next Year	TIMESTAMPADD( SQL_TSI_YEAR , 1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.
First Day of the Previous Month	TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.
First Day of the Current Month	TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)	This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.
First Day of the Next Month	TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.
First Day of Current Quarter	TIMESTAMPADD( SQL_TSI_DAY ,DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)	This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.
Last Day of the Previous Month	TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.
Last Day of Current Month	TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_MONTH , 1,TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.
Last Day of the Next Month	TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_MONTH , 2,TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.
Last Day of Previous Year	TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.
Last Day of Current Year	TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.
Last Day of the Next Year	TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1,TIMESTAMPADD( SQL_TSI_DAY ,EXTRACT( DAY_OF_YEAR FROMCURRENT_DATE) * -(1) + 1,CURRENT_DATE)))	From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.
Last Day of Current Quarter	TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY ,DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))	Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day from the date to arrive at the last day of the Current Quarter.
Number of days between First Day of Year and Last Day of Current Month	TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1),TIMESTAMPADD( SQL_TSI_MONTH , 1,TIMESTAMPADD( SQL_TSI_DAY ,DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))	For simplicity I hard coded the January 1, 2010 date and CAST it to a date. I could have used the First Day of the Current Year calculation but didn’t want to over clutter the example. The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

OBIEE ibots

What are iBots?

iBots are intelligence agents or Bots. These agents are triggered by a schedule or condition that in turn generates a request to perform analytics on data based upon defined criteria.

Where do iBots originate from?

Oracle BI Delivers is the application to create, modify and manage iBots.

How do iBots work?

In order to understand how an iBot works, we should start by first creating a new iBot from scratch.

Navigate to "Delivers" ......


.....and click the link to create a new iBot.



From here, we will tackle each individual tab to get an understanding of what the process is to create an iBot.

1) Overview tab

This view is a summary of the current settings for the selected iBot.

One can navigate to specific settings by clicking on the links in the summary or clicking on the tabs located at the top.

2) General tab


This is where the priority of the iBot and how to send the delivery content are set.

The priority options are low, normal, or high. The priority works with the delivery profile for a user to determine the destination for alerts of different priorities.

The following Data Visibility options affect the customization of the delivery content:

Personalized (individual data visibility): 
- uses the data visibility of each recipient to customize iBot delivery content for each recipient. Note that The Run As field if not available.

Not personalized (use the Run As user's data visibility): 
- sends the iBot's delivery content to the specified recipients. All users receive the same content as if they were the user specified in the Run As field.

Not personalized (use iBot owner’s data visibility):
- sends the iBot’s delivery content to the specified recipients using the data visibility of the specified user (Run As box field) who created the iBot. In other words, all recipients will receive the content that's viewable to the Run As user.


3) Conditional Request tab


This page is used to select a request to trigger the iBot. The results of the conditional request determine whether the iBot sends its delivery content and initiates any subsequent actions:

- If the request does not return any rows, the iBot is not triggered.

- If the request returns at least one row, the iBot sends its delivery content and initiates any subsequent actions.

Requests can be chained together to create complex conditional logic.

4) Schedule tab

iBots can be executed based on a specified schedule. You can define a starting date and time for the iBot, a recurrence schedule, and an ending date.

5) Recipients tab

Use this tab to select the users and groups to receive the delivery content of the iBot.

One has the ability to select specific individual users and groups to receive the contents of the iBot.
**You can select multiple users by holding down the SHIFT or CTRL keys and selecting adjacent or nonadjacent users.**

Also, one can select which users or groups can subscribe to the iBot. This allows users the flexibility to receive and view the contents of the iBot AND customize prompted filter values for columns.

Keep in mind that in order to publish iBots, the content must be shared.


6) Delivery Content tab

Use the Delivery Content tab to specify the type of content to deliver with the iBot, such as a
dashboard page or a saved request. The delivery format for the content can also be selected, such
as HTML, PDF, XLS, CSV, or text.

The contents of the Headline field will appear as the subject of the request.

The two field text boxes below allows one to attach a message when the iBot is delivered depending on if the iBot is delivered as an attachment or if there are no records returned by the conditional request.

7) Destinations tab

The User Destinations and Specific Devices portions of this tab are pretty self explanatory.

As for System Services...

Oracle BI Server Cache is used for seeding cache.

(** To have the cache created for individual users, the Personalized option for data visibility in the General tab must be selected.)

Disconnected Application Cache is only available to companies that have licensed Disconnected Analytics.

(** To have the disconnected application cache created for individual users, the Personalized(individual data visibility) option for Data Visibility in the General tab must be selected.)

(**Nonpersonalized data is not used for Disconnected Analytics users.)


8) Advanced tab


Use the Advanced tab to specify one or more actions to execute after the iBot is finished running.

Actions include the execution of other iBots, custom scripts, custom Java programs or Workflows.




For further reading on iBots, I would suggest downloading the Oracle Business Intelligence Answers, Delivers, and Interactive Dashboards User Guide to read further about creating iBots. What I provided is just a brief glimpse of iBots.

(http://download.oracle.com/docs/cd/B40078_02/doc/bi.1013/b31767.pdf)

BI Publisher and OBIEE: Integration with Dashboards & Prompts

In this post I will demonstrate another way to "integrate" OBIEE with BI Publisher ("BIP"): adding a pre-built BIP report to a Dashboard and configuring it to interact with a Dashboard Prompt.

For this example I've already logged into BIP as "Administrator" and created a report named "BI Publisher Subject Area" including some simple dimensions and facts from the "Paint Exec" demo Subject Area. I've uploaded a simple table template aptly named "SubjectAreaTable" using Word to this report. The resulting report viewed in HTML looks like this:

Step 1: Add a BIP Report to Dashboard

From a blank Dashboard page, drag & drop "BI Publisher Report" object into the blank Column. Click "Properties" then "Browse" to the location of the existing BIP report "BI Publisher Request." (Note what happens when you "hover" over the report name - that's the value of the report's Description field.)

Click on the report name and then OK. Click OK again in the Properties window (accept the default parameters for now). Save your changes then view the new Dashboard. Note that the standard BIP controls are available - you can select a Template & format and even choose to deliver the output using the standard BIP options (which are a bit different from OBIEE's options).

Step 2: Configure the BIP Report to respond to a Parameter for "Region"

Now switch to BI Publisher (More Products > BI Publisher). Navigate to "My Folders," then click the "Edit" link under the "BI Publisher Request" report.

Click the "Parameters" option in the left-hand nav, then click "New" to create a new parameters.

Fill in the values as follows:

Identifier: "REGION_NAME"

This value is fairly arbitrary, but as you will see below we will be referencing it in Answers so it doesn't hurt to consider some sort of naming convention. I am also deliberately making the identifier different than the name of the Presentation Column in Answers to clarify the distinction between the two.

Data type: "String"

I've had spotty results using anything but "String" -- Date in particular gave me trouble.

Parameter type: "Text"

There are some interesting uses for other types, but we'll save that discussion for later.

Display Label: "Region Name"

This value can also be arbitrary

Text Field Size: [leave blank]
Options: [leave blank]

Both of these are also out of scope for this discussion.

Now we click into the Data Set (within the Data Model "folder") and add the line "WHERE Markets.Region = :REGION_NAME" to the SQL Query input box. Note we are using the "Identifier" of the Parameter we just created.

Save your changes (click the disk icon in the upper LH corner) and view the results - Note the results by default will be blank because we haven't entered a value. Enter "CENTRAL REGION" in the parameter box and click "View."

Step 3: Add a Prompt to your dashboard

Go back to Answers and create a new Dashboard Prompt based on the "Region" Presentation Column. Here's the important part: Configure the prompt to set a Presentation Variable named - you guessed it - "REGION_NAME" and save your new prompt.

OK last step: Still in Answers, go back to modify your Dashboard and add the new prompt. Might as well put it on top of the existing BI Publisher report.

 Save your changes and view the Dashboard. Again, because the default value for Region is blank, you won't get any results unless you pick one from your new prompt.

But, uh oh, how can I select ALL regions? The "All Choices" selection doesn't seem to work the same way with BI Publisher as it does with native OBIEE reports...

Hmm. How about I give a banana to whomever can chime in with the solution to THAT problem?

Remember that reports in BI Publisher can be created against other data sources besides OBIEE... but the Dashboard integration is essentially the same. I'll save that topic for another post.

Fragmentation in OBIEE

We use fragmentation when we have fact or dimensional data in one or more different tables or data is splitted in different data sources. Then each logical table source represents one data segment.

For example, clients, in the first table are clients from A to M, in the second from M to Z.

Fragmentation of dimension

If our dimensional data is located on two or more physical tables in database (tables) or it is on separeted data sources then we can handle this by using UNION ALL views in physical layer.

However for testing this option we'll simulate this by using CHANNELS table. So we splitt it to a table CHANNELS_OTHER and CHANNELS. Fragmentation attrubur is CHANNEL_ID.

In our SALES fact table that we used here we use CHANNEL_ID from both tables CHANNELS and CHANNELS_OTHER.

Physical model, join:

SALES.CHANNEL_ID >- CHANNELS.CHANNEL_ID
SALES.CHANNEL_ID >- CHANNELS_OTHER.CHANNEL_ID

BMM:

Settings:

Test in Answers:

Result:

NQQuery.log:

We see that UNION ALL is generated.

If we choose:

NQQuery.log:

We see that the SQL is using only the second fragmented logical table source and the condition is applied only for that logical table source.

If we choose:

NQQuery.log:

If we now choose any other attribut (CHANNEL_CLASS) that is not CHANELL_ID which we used as a fragmentation key:

Then this condition is applied on both logical table sources CHANNEL and CHANNEL_OTHER:

Fragmentation of fact table

Let's split data for SALES 1998 in separate table SALES_HIST. Data from 1998 we leave in SALES.

Physical model, join:

SALES.TIME_ID >- TIMES.TIME_ID
SALES_HIST.TIME_ID >- TIMES.TIME_ID

BMM:

Settings (for a fragmentation key we choose CALENDAR_YEAR):

Test in Answers:

Result:

NQQuery.log:

If we choose:

NQQuerylog:

If we choose:

NQQuery.log:

If we now choose any other attribut (CALENDAR_MONTH_DESC) that is not CALENDAR_YEAR which we used as a fragmentation key:

Then this condition is applied on both logical table sources SALES and SALES_HIST:

In this post we showed how to combine different sources of information using fragmentation option. We see that at any moment we can see which source OBIEE takes while generating code. 

SAP GUI Scripting and OBIEE

SAP has several connectors to automate all its transactions.
http://service.sap.com/connectors

One of the most interest things, is its capability to record all what you do with SAP and to save it in a script. You can then use it to automate all your tasks and embedded it in a Browser or for instance in a Web Based Software like Oracle OBIEE.

The following chapter come from SAP SDN where you can find all the useful informations that you require.

SAP GUI Scripting

Automate User Interaction with SAP GUI – and more

SAP GUI Scripting is an automation interface that enhances the capabilities of SAP GUI for Windows and Java. Using this interface, end users may automate repetitive tasks by recording and running macro-like scripts. Administrators and developers on the other hand may build tools for server-side application testing or client-side application integration.

• Literature
• Presentation and Exercises
• Availability

Literature

Overview (PDF 18 KB)

This whitepaper offers an overview of SAP GUI Scripting. A user can utilize SAP GUI Scripting to automate SAP GUI by executing scripts that emulate user interaction. These scripts can be created manually from scratch or by recording user interaction.

SAP GUI Scripting API (Zip archive 2,3 MB)
SAP GUI Scripting provides a set of scriptable objects wrapping SAP GUI. The document describes the interface that was designed to resemble user interaction, which can then be emulated using a script.

Security Considerations (PDF 185 KB)

This guide deals with security aspects when using the SAP GUI Scripting API.

Presentation and Exercises

SAP GUI Scripting API Presentation (PDF 1,3 MB)

This presentation explains the benefits you have when using scripts in your daily work. Also, it describes the security settings and recommendations for SAP GUI Scripting. In addition, you will learn to access the Scripting Object Model to collect and modify data in SAPGUI and how to use the SAP sample applications that help you to get started with your own development. Also, you will get to know some prominent appplications built using the SAP GUI Scripting API.

SAP GUI Scripting API TechEd Workshop Exercise (PDF 278 KB)

In this exercise, you learn how to record and play back scripts using SAP GUI for Windows and SAP GUI for Java.
Availability

SAP GUI Scripting is available as of SAP GUI 6.20 for Windows and SAP GUI 6.20 for Java Revision 6. You find download information on the SAP GUI Family page. SAP System versions beginning with 3.1I are supported. A complete list of all requirements is available in the security documentation above.

How to record a script?

To record a script you can use the recorder embedded in SAP GUI (Alt + F12 to open the menu) or download the recorder from SAP SDN. The recorder produce VBScript or JScript.

Script

Here below is a script to launch the material transaction (mm02) for a special material code.
I changed it a little bit :

• I added a parameter Vs_MaterialCode. In this way, we can launch the material transaction according to the material code.
• I formatted it with the <script></script> tag to be able to use it in the Internet Explorer Browser.
• I added this instruction

Set Wrp = CreateObject ("SapROTWr.SapROTWrapper")
Set SapGui = Wrp.GetROTEntry ("SAPGUI")

because unfortunately GetObject will not access the Running Object Table when you call it from inside IE. This only works in the Windows Script Host.
ROT is the Running Object Table where active COM objects are registered.

<script LANGUAGE="vbscript" type="text/vbscript">
Function LaunchMaterialSAP(Vs_MaterialCode)
If Not IsObject(application) Then
   Set Wrp = CreateObject ("SapROTWr.SapROTWrapper")
   Set SapGui = Wrp.GetROTEntry ("SAPGUI")
   Set application = sapgui.GetScriptingEngine
End If
If Not IsObject(connection) Then
   Set connection = application.Children(0)
End If
If Not IsObject(session) Then
   Set session    = connection.Children(0)
End If
If IsObject(WScript) Then
   WScript.ConnectObject session,     "on"
   WScript.ConnectObject application, "on"
End If
session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").text = "/Nmm02"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").text = Vs_MaterialCode
session.findById("wnd[0]/usr/ctxtRMMG1-MATNR").caretPosition = 7
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[1]/usr/tblSAPLMGMMTC_VIEW").getAbsoluteRow(0).selected = true
session.findById("wnd[1]/tbar[0]/btn[0]").press
End Function
</script>

How to integrate the SAP script in OBIEE Dashboard

If you want to be able to integrate this script in Oracle OBIEE, you must use the narrative view and :

• add in the narrative part the call of the vbsscript :

<a HREF=vbscript:LaunchMaterialSAP("@1")  language="VBScript" > @1</a> - <b>@2</b>

• add in the prefix form the above script. In a other way, you can embedded in the OBIEE skin all your vbs library.

Security

Internet Explorer (Client)

The script launch an active X. Thus, if you want to be able to run the above script. You must enable the use of Active X.

To enable the ActiveX .. use the below procedure:

• Go to Tools → Internet Options → Security → Custom Level
• Select Prompt in Download and others questions concerned the signed ActiveX controls.
• When prompted select Yes and you will be able to create the ActiveX object.

SAP Server

You have more information in the Security Considerations guide but to allow the scripting.
Use the transaction RZ11 to set the parameter sapgui/user_scripting to true to enable scripting at the server.

Micrsoft Script Windows Debugging

To be able to debug a Windows script, you must install the Script Debugger for Windows NT 4.0 and Later and turn on the debugging in IE

Go to tools→internet options→advanced. Make sure that “Disable Script Debugging (other)” and “Disable Script Debugging (Internet Explorer) are NOT checked.

You can find more information about debugging in this good article HOW-TO: Debug JavaScript in Internet Explorer

Script Editor

• VbsFactory a french but good and free editor
• Microsoft Editor Deliver with Miscrosoft Office MSE or MSE7.exe.

Other Interesting Links

• JavaScript: File: Executable: How to run executable file in JavaScript?
• Good thread in the SAP forum

Support

ActiveX Can't Create Object on Internet Explorer
Verify that you have this instruction and not the orginal instruction from the recorder.

Set Wrp = CreateObject ("SapROTWr.SapROTWrapper")
   Set SapGui = Wrp.GetROTEntry ("SAPGUI")

Tags: OBIEE, SAP, Scripting

This entry was posted on Monday, September 29th, 2008 at 12:52 pm and is filed under OBIEE. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

1. Blogue de Py » Blog Archive » Sap scripting engine via Python says:

   February 9, 2009 at 5:32 pm

   [...] liens intéressant sur le sujet Category: Python You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site. Leave a Reply [...]
2. muscade77 says:

   February 9, 2009 at 5:35 pm

   Text with Sap gui scripting for Python using Win32com

   http://www.samuelbriere.com/?p=127