New 1.6 version of the Ultimate Generic Date/Time Framework released!

In one of my previous posts I promised to release a Date/Time Framework for Sybase (Adaptive Server Enterprise) and yesterday I suggested a solution for the parse-error.  I’m glad to announce the release of the Sybase universe and the adjustment of the other universes with the dummy table to eliminate the parse-error.

• Sybase 15.x (Adaptive Server Enterprise 15.x) (free download from my dropbox)
• Oracle 10g or higher  (free download from my dropbox)
• DB2 v9 or higher  (free download from my dropbox)
• MS-Access (free download from my dropbox)
• MS SQL 2008 or higer  (free download from my dropbox)

Check my previous posts on how to implement the framework in your own universe. Or view the video’s published on my YouTube channel.

You can follow the same procedure, of course you need an extra copy of the dummy-table.

In short, the implementation procedure can be summarized into  three easy steps:

1. Copy all the tables from the above template universe into your target universe.
2. Copy all the classes from the above template universe into your target universe.
3. Create a customized LOV for the (hidden) field ‘GenericDateDimension’ in the class Generic Timestamp Dimension (+ check the option export with universe)

There is a small exception for DB2, but please read the implementation instructions in the description of the  ‘GenericDateDimension’ field for more info.

The next challenge is the release of the framework for BO BI 4.x. in other words a .unx version. A few extra lonely evenings will be needed :-).

Advertisement

Semantic Layer

3.1, BO, businessobjects, calendar, conversion, date, format, Framework, time, Ultimate, universe

02/26/2013

9 Comments

How to get rid of parse-errors in the ‘Ultimate Generic Date/Time’-framework?

Before you release a new or changed universe to your production environment, you should always do an integrity check using the menu-option  Tools/Check Integrity… , in designer and resolve the problems.

But what about the parse errors you get from the date/time-objects in the Ultimate Generic Date/Time-framework? First of all, why do you get these errors?

In fact each object you create has to be connected to a table! Otherwise you will receive a parse-error!

For example, in DB2 using  current_date – 1 day will return yesterday as date.  In BO you can create an object and use it in a query with other objects without any problem. But using it as a standalone object in your select-statement or doing a parse on the object will result in an error message.

Parse error

Is there a way to solve this problem?

Semantic Layer

02/25/2013

3 Comments

Unambiguous dates and times in Adaptive Server Enterprise 15.x

Tired of finding the right style convertion for your datetime-input?  Try using the ambiguous date- and time format.

Dates in the format  yyyy/mm/dd  or  yyyy-mm-dd are always recognized unambiguously as dates, regardless of the DATE_ORDER setting. In other words, this is a platform independent solution.

Even other characters can be used as separators instead of ”  /”  or ”  –  “; for example, ”  ?  “, a space character, or ”  , “. Also, a string of the form hh : mi : ss . sss is interpreted unambiguously as a time.

For combinations of dates and times, any unambiguous date and any unambiguous time yield an unambiguous date-time value.

Also, the form yyyy/mm/dd hh:mi:ss.sss is an unambiguous date-time value.

Having this information, ask your end-user for a date time-value with a character prompt in a predefined filter, and use the convert-function to convert the date/time-character value to a valid timestamp, without the style-conversion-parameter!

@Select(On The Fly Timestamp Table\Date (as Timestamp-type))=convert(datetime,@Prompt(‘Enter user defined date/time (format:yyyy/mm/dd hh:mi:ss.sss)’,‘A’,,MONO,FREE,PERSISTENT))

It still works even if you omit a part or the complete time-value ‘hh:mm:ss.sss’ ! So get rid of the BO date-picker, and use the above platform-independant solution.

And now the weird part. Do you need the convert-function? No, you don’t need the conversion-function!  In other words

@Select(On The Fly Timestamp Table\Date (as Timestamp-type))=@Prompt(‘Enter user defined date/time (format:yyyy/mm/dd hh:mi:ss.sss)’,‘A’,,MONO,FREE,PERSISTENT)

is also correct!

Why? Comparing =’yyyy/mm/dd hh:mi:ss.ssss’ is comparing a date-type with a string-literal. And this is impossible, so, the SQL compiler does an implicite type-casting from the literal ‘yyyy/mm/dd hh:mi:ss.sss’ to a timestamp. In fact the convert-function has been used behind the scenes.

Do you need more datetime-solutions? Just wait a few days, at the end of the month, I’ll publish the ‘Ultimate Generic Date/Time framework’ for the Adaptive Server Enterprise.

Semantic Layer, Time-Series Analysis, Uncategorized

adaptive, convert, date, Framework, picker, prompt BO, Sybase, time, timestamp, Ultimate

01/22/2013

Leave a comment

CSV to XML Converter

CSVtoXML.jar is a ‘command-line driven’ Java-program.

The tool converts one or more CSV-files to one or more Xcelsius (SAP BO Dashboards 4.x) compliant XML-files.

Download  CSVtoXML.jar from my public Dropbox.

The PowerPoint below explains all features of the tool.

 

Tools

4, BO, CSV, Dashboards, SAP, Xcelsius, XML, XML Dataconnector

01/02/2013

Leave a comment

Design Principles: Where to Put the Intelligence?

Cindy Howson has spend a separated chapter about this question. Please read her splendid book. BusinessObjects XI: The complete reference.

Occasionally, there is a single, clear-cut choice for where to put the intelligence. You are low on disk space, so you add it to the universe; the calculation is too complex for SQL, so you create a report variable. These are the easy answers. The not-so-easy answers are everything else in between.

The best place for intelligence is as suggested by the consultant’s annoying but valid answer “it depends.”

Intelligence is information with a business context. QUANTITY may be a physical column in a table. Add a time period such as month, then a context to the time period such as order month, and multiply the column by a price, and you arrive at Sales Revenue, something with meaning and value to business users.

And now the dilemma? I have a date-field, let’s say order-date for every order in my fact-table.

The end-user want to display and count all orders for the previous months until to date (YTD).

Without the ‘Ultimate Generic Date/Time framework’ implemented in your universe. You probably create a month & year object in your universe.  And try to extract the information by dropping following objects

• month(order_date)
• year (order_date)
• count(order_id)

in the query panel, and using a where-clause like this

• year(order_date) = 2012

But what if you have also a sales_date, billing_date, pre_order_date, cancel_date, delivery_date,… and so on, and you also want to know the quarter, the semester,… of these dates.

Then you will need to create lot of objects (semantic-logic) in your universe. Not implementing all these options from the beginning, can result in a higher maintenance cost afterwards. Not preferable if you want to cut your maintenance costs.

Learn the end-user how to create a month, year,… variable in the report (should be a common skill of a report creator) and how to use ‘Ultimate Generic Date/Time Framework’.

Double click to enlarge the image

The end-user only has to drop the order_date into the query panel. And use the following where clause (based on objects in the date/time framework.)

Double Click to enlarge the image

Then the report creator need to create some variables using the date-time functions in his report.

Difficult? No, you only have to provide the user with a template report, with the most common functions, like those below.

Double click to enlarge the image

No time to create such a template report? No panic, just download it from my dropbox

Semantic Layer, Time-Series Analysis

06/20/2012

1 Comment

New 1.5 version of the Ultimate Generic Date/Time Framework released!

In my previous post, I published the ‘Ultimate Generic Date/Time Framework’ with the super-filter for MS SQL Server.
Today I’m glad to announce the new 1.5 version of the ‘Ultimate Generic Date/Time Framework’ with the super-filter implementation for:

• Oracle 10g or higher  (free download from my dropbox)
• DB2 v9 or higher  (free download from my dropbox)
• MS-Access  (free download from my dropbox)
• MS SQL 2008 or higer  (free download from my dropbox)

Indeed the MS-Access Date/Time Framework universe is new!  MS-Access is not a corporate database, but lots of people are using BusinessObjects to create reports on this personal DB. And from now on, also those end-users can make benefit by implementing  this framework in there universe.

There is one prerequisite before you can start using the MS-Access framework. You have to create one physical ‘Dual’ table. This table simulates the dummy table like Dual in Oracle and Sysibm.sysdummy1 in DB2. (click the picture to enlarge)

Navigate to my YouTube Channel for a two minutes  implementation tutorial.

Semantic Layer, Time-Series Analysis

05/21/2012

2 Comments

How to implement the new version of the Ultimate Generic Date/Time Framework for MS SQL Server?

Hi BO designers,
In my previous Blog I released a video showing the possibility to use a ‘Super Date/Time Filter’ in your own universe. Some small adjustments and supplementary filters (objects) are needed in the framework to implement this new feature into your own universe.

This adjusted framework 1.5 is now available for MS SQL Server from my dropbox.

But I also released some new video’s explaining step by step, on how to implement this Generic Date/Time Framework into your own universe.

The first video is a step by step tutorial to implement the basic functionalities of the framework into a target universe (you only need 2 minutes, to implement it!).

The video show you the target universe ‘BOE4_Audit tables’ with only one table (to keep it simple) with the recorded BO Events of a BO 4.0 environment. Every BO-event is recorded with a ‘Start Time’ – timestamp. We will use this field as an example to setup the filters and to show you the possibilities of this framework.

After the implementation of this basic functions, you can already experiment with some basic functions, you don’t even need to implement some filters. The end-user can start immediately.

Semantic Layer, Time-Series Analysis

businessobjects, designer, ms sql server, software, UGDT-Framework, ultimate generic datetime, universe

03/30/2012

6 Comments

Video examples of the Ultimate Generic Date/Time framework.

Hi Folks! I just released some YouTube video’s showing  the user experience when using a universe with the ‘Ultimate Generic Date/Time framework’ implemented.

Just visit my Youtube- Channel to see the framework in action.

In the YouTube video ‘Super User Defined and Ad hoc LOV’ Example of the Ultimate Generic DateTime Framework, I show you a filter-example combining a user defined input of a begin- and end-period in combination with the ad hoc List-of-Values with predefined date-periods.
The end-user has the possibility  to select from a LOV with predefined periods (date-ranges) or to fill in his own begin- and end-date.

This is extremely user friendly. The end-user only has to make one report, then he can use it on-demand using user-defined periods or use the same report and schedule it for a specific time-interval like previous year, previous month, previous week, … .

I’ll soon reengineer all my previous published frameworks (Oracle, DB2 and MS SQL Server)  with this new super-filter. I’m also working on a MS-Access and Sybase version of the framework. please keep following my blog, and invite your BO-friends.

Semantic Layer, Uncategorized

enterprise-it, ms sql server, software, software-development, technology, youtube video

03/27/2012

7 Comments

The Ultimate Generic Date/Time Framework now available for Informix (Dynamic Server 11.5)

A few weeks after the release of the Date/Time framework for MS SQL Server, Romain Gouyet (from Irium France) was so enthusiastic, he immediately started to adapt the framework for Informix.

To my great pleasure and with the permission of Romain, I can announce the availability of this Informix version, ready to download from my public Dropbox Folder. Because we don’t use Informix in our company, I only have the opportunity to do a quick review, but as I can see, lot of time has been invested by Romain to realise this migration. Thanks Romain!

To customize your own universes with this generic Date/Time Framework, simply follow the installation guidelines for MS SQL Server published in my previous post. Or download the PowerPoint presentation from Slideshare.

And Romain, also suggested another very nice tip. In case you want to customize the ‘Ad hoc’ list of values for several languages.

You simply surround the range text-field (Tomorrow, Yesterday, Today,…) with the following do case structure (example provided for MS SQL Server):

select ’00’ as Sort,(CASE CAST(@Variable(‘DOMINANT_PREFERRED_VIEWING_LOCALE’) as char(2))

when ‘fr’ then ‘Demain’

when ‘en’ then ‘Tomorrow’

when ‘de’ then ‘Morgen’

when ‘nl’ then ‘Morgen’

when ‘es’ then ‘mañana’

else ‘Tomorrow’

End) as Date_range,dateadd(dd, datediff(dd, 0, current_timestamp)+1, 0)  as Begin_Date, dateadd(ms,-3,dateadd(dd, datediff(dd, 0, current_timestamp)+2, 0))  as End_Date

 

Using this new variable DOMINANT_PREFERRED_VIEWING_LOCALE (new in BO XI 3.1 SP2), you can determine the end-user local and customize the LOV in his preferred viewing local.

Again, Romain, thank you very much for your contribution to the SAP – BO designer community.

 

 

Semantic Layer, Time-Series Analysis

Date/Time, Dynamic, Framework, Generic, Informix, layer, semantic, Server, Ultimate

03/14/2012

Leave a comment

The Ultimate Generic Date/Time Framework now available for MS SQL Server

Some people asked me, if it’s possible to migrate the framework to MS SQL Server.

The framework is based on the trunc-function in DB2 & Oracle and the existance of a dual-table in Oracle and the sysibm.sysdummy1 table in DB2. It’s possible to substitute the trunc-function with a combination of the dateadd & datediff function. Read my previous post for more information.

And do you need an equivalent for the Dual-table in MS SQL Server? The good news is…, there is no need of Dual table in SQL Server at all.

And now the good news. Below you can download a the Ultimate Generic Date/Time framework for MS SQL Server universe from my public dropbox <<DateTimeDimensionMSsqlTemplate.unv>>

But before you start to implement the framework in your own universe, just take a little bit of time to study the embedded Powerpoint below (Tip: you can maximize the window by clicking in the lower-right corner of the slideshare presentation.)

The Ultimate Date Time Universe solution (Quick Start and Setup MS SQL Server)

View more PowerPoint from Gino Scheppers

Semantic Layer, Time-Series Analysis

03/05/2012

2 Comments