Tuesday, August 23, 2011

Gognos IBM vs SAP Business Objects

Here is a nice comparison i have found at http://www.bi-dw.info/ 
The compatirion isn't up to date and missing some important points but in general it is a good strating point for comparing Gognos to SAP Business Objects.
BIscorecard ,Gartner and Forrester are companies that are specialized in evaluating BI platforms and in general if you have the resources, i would reccomend to perform a POC

Here is the link:

Wednesday, August 17, 2011

BI related vs Google Related

Google as just went up with another nice idea that has it's implications on the way we use BI

Just a quick thought :

You are running your regional sales report,you get the results and on the buttom of your browser there is a toobar that shows you:

1.Suggestions for other related reports.
2.You get the info : "people who view this report also viewed these repors" (just like in Amazon site)
3.You get suggestions for further data that can help you analyze the report.
4.You get suggestions " whould you like this reports to be sent to you when a certain value\amount cahnges ?"

Any more suggestions ?

Yoav 

Monday, August 15, 2011

Getting rid of the LOV timestamp

One of the famous problems in Designer is getting rid of the timestamp LOV and sorting the date LOV according to the date column.

Here is the step by step procees in order to achieve that in Oracle:

1.first we have the date object in the universe named :Time Key

2.Inside the object properties we can see the date LOV time stamp:


















3.Now we will create an object that will be used inside the object's LOV,later on we re-map the lov of the date object to this object's LOV:



















this object contain the function to_char in order to format the date object.

4.Inside the time key object properties we will re-map the LOV to the "No time stamp LOV " object and sort it from Z to A

 
5.Now we will get inside the object LOV and esit it's SQL to:

SELECT


to_char(TIME.TIME_KEY,'dd/mm/yyyy')
FROM
TIME
ORDER BY TIME.TIME_KEY

 
 
 
 
 
 
 
 
 

 
the result is :

 
make sure that you thick the "do not genertae" button  and the "export with universe" property of the LOV.

Sunday, August 14, 2011

Time difference calculations for call center - part 1

Here are two scripts that return the time difference between two dates in Oracle:

1.The following script returns the time portion in seperated columns :


SECOUNDS   MINUTES    HOURS    DAYS

     38              8            10           2569 


SELECT TO_CHAR(DATES.TIME_KEY, 'DD/MM/YYYY:HH24:MI:SS') AS TIME_KEY,

TO_CHAR(SYSDATE, 'DD/MM/YYYY:HH24:MI:SS') AS TODAY,
trunc(86400 * (SYSDATE - DATES.TIME_KEY)) -60 * (trunc((86400 * (SYSDATE - DATES.TIME_KEY) / 60))) AS SECOUNDS,trunc((86400 * (SYSDATE - DATES.TIME_KEY)) / 60) -60 *(trunc(((86400 * (SYSDATE - DATES.TIME_KEY)) / 60) / 60)) AS MINUTES,trunc(((86400 * (SYSDATE - DATES.TIME_KEY)) / 60) / 60) -24 *(trunc((((86400 * (SYSDATE - DATES.TIME_KEY)) / 60) / 60) / 24)) AS HOURS,trunc((((86400 * (SYSDATE - DATES.TIME_KEY)) / 60) / 60) / 24) AS DAYS

FROM DATES.TIME_KEY
 

2.The following script returns the time portion in hours,minutes and secounds in one column :

HOURS MINUTES SECOUNDS       
61 :    22 :    11
 
SELECT

TO_CHAR(DATES.TIME_KEY.TIME_KEY, 'DD/MM/YYYY:HH24:MI:SS') AS TIME_KEY,
TO_CHAR(SYSDATE, 'DD/MM/YYYY:HH24:MI:SS') AS TODAY,
(sysdate - nvl(DATES.TIME_KEY.TIME_KEY, sysdate)) * 24 as hour_diff,
floor((SYSDATE - DATES.TIME_KEY.TIME_KEY) * 24) ' : '
mod(floor((SYSDATE - DATES.TIME_KEY.TIME_KEY) * 24 * 60), 60)' : '
mod(floor((SYSDATE - DATES.TIME_KEY.TIME_KEY) * 24 * 60 * 60), 60)' ' time_difference

FROM DATES.TIME_KEY

These scripts can become universe objects and are good for call center calculation

SAP BusinessObjects BI 4.0 Platform Demo

 sneak preview demo of SAP BusinessObjects 4.0 new capabilities in Designer and Webi modules:

Dashboards in SAP BusinessObjects 4.0

Watch this demo of the new Xceslius called SAP Dashboard in version 4.0
 including some new great features like:
direct binding,embedded query designer,parameter selector component







Wednesday, August 10, 2011

YTD and MTD Prompts

Here are code samples for creating YTD and MTD prompts in the universe designer,the prompts are written for Oracle :

1. YTD
This prompt allows the user to enter a date and get all the dates from the month of the chosen date till the beginning of the current month, for example:

If the user will type 05/05/2010 (mm/dd/yyyy) he will get the range: 05/01/2010 till the first day of the current month.

Code:

Table.date BETWEEN trunc(To_Date(@Prompt('Enter Date:','D',,Mono,Free),'mm/dd/yyyy hh24:mi:ss'),'mm') AND TRUNC(SYSDATE,'MM')

2. YTD specific year:

This prompt allows the user to enter a date and get all the dates from the first day of the year of the chosen date till the chosen date, for example:

If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 01/01/2011 till 05/05/2011.

Code:

Table.date between trunc(to_date(substr(@Prompt('Pick date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY'), 'YEAR') and to_date(substr(@Prompt('Pick target date','D','folder\date_object',mono,free),1,10),'mm/dd/YYYY')

3.MTD

This prompt allows the user to enter a date and get all the dates from the first day of the month of the chosen date till the chosen date, for example:

If the user will type 05/05/2011 (mm/dd/yyyy) he will get the range: 05/01/2011 till 05/05/2011.

Code:

trunc(Table.date) BETWEEN trunc(to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR'),'MM')

AND to_date(@Prompt('Enter Date:','A',,mono,free),'DD/MM/RR