IT-eye

When using Qlikview Analyzer Java Client with Qlikview files which are built and used on a high resolution (for example 1920 by 1200). The application in the browser is shown in a smaller resolution so it doesn’t fill the whole screen which results in an ugly scrollbar. When using the Internet Explorer plugin everything looks fine but I prefer to have the freedom of choosing another browser. The following solution will fix the problem:

On your QVServer machine go to C:Program FilesQlikViewServerQvClientsQvJava and right click open in notepad the opendoc.htm and find the line html += ” width=’1000′ height=’700′>”; . This is where the browser height & width settings are set for the java client. you can change it to something like html += ” width=’99%’ height=’720′>”;

Nieuwsgierig naar de onderscheidende rol van IT-Eye ten opzichte van andere Qlikview partners ben ik op onderzoek uitgegaan met de vraag wat de unieke toegevoegde waarde is die IT-Eye als Qlikview partner kan leveren. Er kan worden gesteld dat de focus van de Qlikview partners is onder te verdelen in grofweg vier categorieën:

• Een focus op een specifieke branche met totaal oplossingen (retail, logistiek)
• Het leveren van algemene ICT oplossingen waaronder Business Intelligence
• De core business op een ander ICT vlak (bijvoorbeeld CRM) met BI als ondersteunende oplossing
• Licentieverkopers

Wat direct opvalt, is dat IT-Eye als één van de weinige partners Business Intelligence als core business heeft. Organisaties intelligenter maken door een betere informatievoorziening wat zorgt voor besluitvorming op basis van rationele gronden. De juiste informatie, op het juiste moment, op de juiste plek. Qlikview is in dit perspectief een revolutionaire enabler die een agile ontwikkeling mogelijk maakt. En dat is waar huidige, snel veranderende omgevingen om vragen.

Door de specialistische kennis over het gehele spectrum van het vakgebied Business Intelligence en de praktijk ervaring in vele branches mag IT-Eye oprecht zeggen verstand te hebben van de business en haar informatievraagstukken. Qlikview kan door deze specialistische kennis en praktijkervaring perfect als sluitende of aanvullende oplossing worden ingezet.

Door de combinatie van bovengenoemde factoren kan IT-Eye zich als organisatie onderscheiden en een stuk toegevoegde waarde leveren ten opzichte van haar concurrentie. Immers, door de combinatie van praktijkervaring, “in-depth” kennis van BI en de juiste tools worden oplossingen bereikt die daadwerkelijk toegevoegde waarde aan de business leveren.

One of the features I like in Pentaho Data Integration (version 3.1.0) is the “dimension lookup/update” transformation step to implement a Slowly Changing Dimension type II.
It works quite easy and saves a lot of work. I worked out a small example to illustrate how it works: just an Excel file to populate a dimension table.

The complete transformation from Excel file to dimension table only takes two transformation steps.

The Excel file has the following columns:

• klantnummer (customer number)
• voornaam (first name)
• achternaam (last name)
• woonplaats (city)
• geboortejaar (year of birth)
• geslacht (gender)
• type (type)

The dimension table DIM_KLANT (customer dimension) has the following properties:

As you can see, it has some additional columns compared to the Excel file:

• technical_key
• start_date
• end_date
• version

I skip the first transformation step and go straight to the second one.

Here is how this transformation step is configured:

And here is how it works.

We start with the following data file (Excel).

After running the transformation, the dimension table contains the following data.

As you can see, all records are inserted and have version number 1. Also does every inserted record have a (default) start date and end date. And, of course, a technical key.

Now we change the data in the Excel file.

Let’s modify Ineke’s year of birth (from 2000 in 2001). We also add Janneke, Klaas and Laura.

Run the transformation again. The data in the dimension table should now look like this:

As you can see, Ineke has a new record in the dimension with version number 2. Her (previous) record with version number 1 now has an updated end date. New records are also added for Janneke, Klaas and Laura. All with version number 1 and with a technical key.

Finally, let’s make some changes to the data. We modify the dates of birth for Ineke, Janneke, Klaas and Laura.

After running the transformation, we expect 4 new records to be added to the dimension table. Janneke, Klaas and Laura should get records with version number 2. Ineke shoud get a new record with version number 3. All previous versions of those records should get an updated end date.
This is what the dimension table now looks like.

Here ends this small example. The functionality of the transformation step “dimension lookup/update” works quite nice and easy. It also saves a lot of work compared to programming this functionality yourselves.

Yesterday, when I was using the cross table functionality in Oracle BI Publisher, I retrieved an unexpected result. I had a simple dataset that contains the information of persons with their salary and office location. A simple overview where the a pivot table is very useful. The result of the report was not what I expected:

See the first row and column containing only 0. The xml data used in this report is not strange at all. Here a small sample of the dataset:
<PERSON_SALARY>
<EUROPE>
<PERSON_SALARY>
<FUNCTION>Administrator</FUNCTION>
<SALARY>40000</SALARY>
<OFFICE>Amsterdam</OFFICE>
</PERSON_SALARY>
<PERSON_SALARY>
<FUNCTION>Administrator</FUNCTION>
<SALARY>35000</SALARY>
<OFFICE>Paris</OFFICE>
</PERSON_SALARY>
...
<PERSON_SALARY>
<FUNCTION>Administrator</FUNCTION>
<SALARY>40000</SALARY>
<OFFICE>London</OFFICE>
</PERSON_SALARY>
<PERSON_SALARY>
<FUNCTION>Accountant</FUNCTION>
<SALARY>30000</SALARY>
<OFFICE>London</OFFICE>
</PERSON_SALARY>
</EUROPE>
</PERSON_SALARY>

If you look careful at the xml you see double use of the element ‘PERSON_SALARY’. This is the reason that the result of the cross table is what we would expect. If we take a look at the xpath querys in the rtf we see the following:

There we see that all elements of ‘PERSON_SALARY’ are included in the result of the cross table. In the dataset we discovered already the double existence of this element. To validate our analysis, we renamed the root element to LIST_PERSON_SALARY and regenerated the report with the following result:

And now we have the expected result. This example shows that the names of elements have to be rightly chosen, to avoid problems of this kind.

Update 11-08-2008:

How to create a pivot table in a BI Publisher report.

Install the BI Publisher Desktop software available from the oracle website. This installation requires also the installation of the .NET framework 2.0.

When you start MS Word you see that there is a new menu item available. Load first an xml file with data (you can use the provided xml from the blog).

Insert a pivot table (in older plug-ins this option was called cross table)

Then drag and drop the elements from the xml structure shown on the left to the place that you want.

A very often heard question of customers is; “can we change the header/footer of our reports with this or that…?”. Of course we can change this, but it can be a timespending operation. If you have a couple of reports than it is not a problem to change each report manually, but most organisations have dozens of reports. The magic word for this situation is: subtemplate.

What are subtemplates? Subtemplates are pre-defined templates with a default header, footer and other recurring reporting objects. The most common situation is a default header with a logo, report-header and a page navigation.



Which elements do we declare in a subtemplate:
1. – header: between the start and end-tag we just insert an image, declare a variable and insert the page navigation.

< ?template:Header?>
	insert your text and/or image
< ?end Header?>

2. – footer: In the footer we alse can define just the text or graphics we want. In this example I wrote the company missionstatement.

< ?template:Footer?>
	insert your text and/or image
< ?end Footer?>

Now we have created the subtemplate all our reports should use it. In the template you can refer to defined report-objects in the subtemplate. In this example we can refer to the header and footer. Beside refering to the report-object we have to declare where the subtemplate resides. When generating the report from the server the subtemplate should also reside on the server or a network-drive which the server can access.



The template can call so called inlines from the subtemplate. In this example we call the footer and the header. These statements are made in the actual header of the RTF-template.
In the body of the template we declare where the subtemplate resides. This import statement looks like this:

< ?import: file:///D:OracleBIDatasubTemplate_a4.rtf?>

Be aware of the three forward slashes!

The option “Disable external references” is default disabled for a BI-pubisher report. If you want to make use of the subtemplate this option should be enabled.

When your reports are using subtemplates than the question above is not timespending at all. Just change your logo or some text in the subtemplate and your reports have the new layout!

Searching through the documentation of BI-Publisher there is mentioned how to create an LOV. But there is no paragraph for showing this LOV as an drop-down list.

This the HowTo for creating an LOV drop-down list:
1. – Create a LOV –> enter a sql-statement or use fixed-data.
2. – Create a paramter –> Select Paramater TYPE as Menu -> a new sub-block appears, select the LOV you created in step 1.

After a day of searching and trying to create an BIP report based on a data template with parameter(s) the bug is countered.

What is going on in a BIP report based on a data template with one or more parameters. I just created a simple report with a data template but the parameters are not passed too the defined query. The used structure is working perfectly in version 10.1.3.2 but in version 10.1.3.3 the parameter is not passed at all!

Data template example

<datatemplate name="test" dataSourceRef="test">
<!-- Parameters -->
<parameters>
  <parameter name="P_BUNIT" dataType="character" defaultValue="RS"/>
</parameters>
<!-- Query -->
<dataquery>
<sqlstatement name="Q1">
< ![CDATA[select nvl(:P_BUNIT, 'empty' ) param, bu, projectnr, projectomschrijving
from project_status
where status = '1'
and bu =  _BUNIT
and rownum < 6]]>
</sqlstatement>
</dataquery>
<!-- Datastructure -->
<datastructure>
  <group name="PROJECT_ACQUISITIE" source="Q1">
    <element name="parameter" value="param"/>
    <element name="business_unit" value="bu"/>
    <element name="projectnr" value="projectnr"/>
    <element name="projectomschrijving" value="projectomschrijving"/>
  </group>
</datastructure>
</datatemplate>

The above example is working perfect in version 10.1.3.2 and shows us the 5 (rownum < 6) records. In version 10.1.3.3 we do not get any records at all!

Solution

The solution is simple; just define the parameter with the same name at report level.

Defining the parameter P_BUNIT at report-level does the tric. If you defined a default-value for the parameter this should also be filled out for the parameter at report level.

An often heard question of customers is: Can we add explanatory text into an report defined in OBI EE. The answer is yes and the report-view you need to use is narrative.

By defining variables in the administrator tool and using them in an Answer-report you can add explanatory text that is stored in any database.

Repository variables can be assigned in 2 ways. One is assigned a value directly to the variable and the other is via Initialization Blocks.

This example has an variable which is based on a Initilization block.

If we want to add some explanatory text to an Answer report we can use the narrative view. In this narrative view we can use the just created variable. In order to access this Repository variable from Answers you need the following syntax:

@{biServer.variables['variablename']} 

@{biServer.variables['var_country']}

On the blog of Rittman Mead Consulting i’m reading about his first look at one the new tools Oracle lately bought, Oracle Real-Time Decisions (RTD).

At the moment BI and business modeling are two different worlds. Also at my current project we’re still missing the real integration between BI and the business processes we’re modeling. Oracle got a nice stack of products for fulfill the BI needs, and to model the business processes we use Oracle BPEL with a ‘custom business rule implementation’.

Nice tools for both worlds, but still no integration between them. Oracle Real-Time Decisions, as what we can expect from the name, gives us real-time decisions. So we can model the business process and include real-time decisions in it, instead of ’static’ (non-self-learning) rules. In short, RTD uses a data-mining engine which can receive all the data/events needed to take a real-time self-learning decision and this outcome can be used in the business-process. See this post for the complete article.

On OTN you will find a nice ‘Oracle by Example’ for getting started.

Its too early to draw any conclusions, but i see a lot of added value for RTD. IT-eye does a lot of BI and SOA projects, and the customer will see nice results when both can be integrated for fulfilling his business needs.

Introduction

Oracle launched on June 12th, 2007, the long anticipated release of the Oracle Business Intelligence Standard Edition One Suite. It concerns the release 10.1.3.2.1 and this version number is on par with the OBI EE Suite. Just like the other Oracle Standard Edition One offerings this release seems to be a good offer. Not only includes this release a series of very promising tools, but what’s more, the pricing is just above that of the OBI Standard Edition (the release without the “One”). So the question would be: are there any drawbacks to this offer?

The Offer

Let’s start with a complete list of features of the different suites
Table 1: What you get

* = OWB Core ETL is a part of the database since May 2006 and no longer a part of the Internet Developers Suite.

In the above table you can see that the OBI SE Suite consists of the “old” Oracle BI tools. The OBI EE Suite is completely assembled of the old Siebel Analytic Tools. And the OBI SE One Suite offers just a bit of everything, combined with a database and Oracle Warehouse Builder. Unfortunately, there are some limitations. For example, the database can only be used on a server with a maximum of 2 CPU’s. OWB contains only the Core ETL version. And Core ETL Features corresponds largely with the previous release of OWB (release 10G R1). So features like data pump, transportable table spaces, target load ordering and RAC system support are missing. These are the so-called Enterprise ETL features. Also missing are data profiling and other data quality features. Finally the ERP/CRM connectivity is missing. You won’t have connectors for SAP, People soft and the E-Business suite. For the reporting part, you will be missing the BI Scheduler and the possibility to have off-line reporting. But is this a real problem?

Everyday use

In every day use, my experience is that the use of the different tools is restraint to the features which are part of the OBI SE One Suite. Usually, people develop a Business Model in the BI administrator, then reports are developed in BI Answers and those reports assembled in a dashboard with the help of BI Dashboards. 95% of the enterprises won’t need off-line reporting, since everyone is connected to the enterprise servers. Same applies for report scheduling. In my view, report scheduling is too often a sign of bad architecture and bad business modeling and has no connection to the real purpose of scheduling that is to have a report on a certain moment.
What people really use is reporting for external purposes. Composing of calculations or invoices can be done with the BI Publisher. And developing and running of data warehouse ETL can be done with Core ETL. After all, OWB 10G R1 and the Oracle Database 10G served in a perfect way before and will be doing it now. Happily, both are part of the OBI SE One Suite.

And what about OBI SE?

This version of the Oracle BI tools hasn’t finished yet. Companies who are working with Oracle Discoverer and Oracle Reports can continue in doing so. Both are proven technology; for both there will be a continued development and both will be supported by Oracle. Oracle just made an announcement about the Lifetime Support of Oracle Discoverer.
But for companies who want to be ready for the future, there is no escaping from Oracle Analytics. One day in the far future, support for Discoverer10G will stop, just like it did for 9i or 4i. And although Oracle is working on a migration and upgrade path from Discoverer to Analytics, it is recommended to make this migration step in an early stage. After all: Reports developed with Analytics don’t need to be migrated anymore.

Pricing

Now here is the fun part. See table2, below. The price of the OBI SE One Suite is considerably below the price of the OBI EE Suite. And with prices as low as 3700 Euros (minimum price), it’s just above the price of the OBI SE Suite (2960 Euros, minimum price). But as mentioned above: the OBI SE One Suite integrates a database. Would you add this database to the SE Suite, the price would rise with another 550 Euros (minimum price). The resulting price difference is marginal with 190 Euros. Off course the difference would increase with the number of users.
Table2: pricing (all prices are based on information of Oracle the Netherlands)

Example:
Corporation with 50 BI users, database is Oracle Database Standard Edition One and it is running on a 2 CPU server.

OBI SE suite:

OBI SE One suite:

OBI EE suite:

The OBI SE Suite is the cheapest one of all. But the difference in price between OBI SE One and OBI SE One is not that big. And the real challenge: the OBI EE Suite is considerably more expensive. Oracle itself targets the small and medium large companies with the OBI SE One Suite. And I think it’s a bull’s eye. But perhaps OBI SE One is also interesting for companies which are larger than medium large. Analytics doesn’t require very much hardware (memory is the most important part). And for the database: Oracle has a “50 concurrent users per CPU” rule of thump. But in a data warehouse environment with a 2 CPU server this means 200 to 400 BI users. Because not all the users will require performance from this database at the same time. And a number of 400 BI users mean a company with up to 1000 employees. That is not a medium large company anymore.

Final thoughts

When a company wants to renew its BI-licenses, this company is confronted with a couple of questions. First: continue with Oracle Discoverer or not. If the company wants to migrate from Oracle Discoverer, then there is the choice of the OBI SE One or the OBI EE Suite. Depending on the needs, choices will be made. But my estimation is that the need 95% of the companies will be satisfied by the OBI SE One Suite. So the true winner in Oracle BI country is your company.

Real BI on a real budget!

Urls:
OBI SE One at Oracle
OBI SE One Download
OBI SE One documentation