DWH News

Qlik Technologies Inc., a provider of business intelligence software, today announced financial results for the three month period ended June 30, 2010.

Lars Björk, chief executive officer of QlikTech, stated, “We are pleased with QlikTech’s business momentum during the second quarter, which was highlighted by revenue growth of 56% compared to the second quarter of 2009. We saw strong demand across each of our major global regions, and business was well balanced across both new and existing customers as we executed against our land and expand strategy. This performance also contributed to a strong increase in our company’s operating profitability and margin.”

One important new feature of BI EE 11g is the ability to report on CLOBs and other large objects. The main difference between normal Varchar/Numeric columns and LOB datatype columns is, LOB columns do not support GROUP BY, DISTINCT & ORDER BY operations on them (from a reporting perspective). Any query that BI EE generates will always have a GROUP BY or a DISTINCT in it. So, in 10g reporting on LOB columns have always been an issue(will result in Ora-XXXX errors). One had to resort to different types of workarounds for reporting on LOB columns.

In BI EE 11g, its possible to isolate different columns from the GROUP BY/DISTINCT operations by using the SPARSE/DENSE lookup operators. I have already blogged about how Sparse & Dense lookups work in general here. Lets see how the same can be used for CLOB reporting. To demonstrate this i shall be using the v$sql view. This view basically contains a CLOB called SQL_FULLTEXT. This column contains the SQL queries stored in the form of CLOB, that are fired to the Oracle database by multiple users. Our aim is to report on this view and display the SQL Queries from the CLOB column.

We start with granting select on the v_$sql view to SH schema and then importing it in BI EE.

Then edit the column properties of the SQL_FULLTEXT column and increase the size to 100000 (or higher) so that BI EE can display the values from CLOB.

Start with modeling a dimension and a fact in the BMM layer as shown below

Then create another logical table (same as the dimension) containing SQL_ID as the primary key. The idea is to use this primary key and show the SQL_FULLTEXT using a DENSE lookup. Mark the new logical table as a lookup table.

In the dimension logical table create a new column and use the following formula to derive the SQL_FULLTEXT as a lookup column. This will always push a separate SQL without DISTINCT/GROUP BYs on the SQL_FULLTEXT column.

Lets now include this new column in the presentation layer and use it for reporting.

As you see, we should now be able to see the CLOB data without any restrictions. If we look at the query generated, you can basically see 2 separate SQL queries, one with the group-by and the other with just the lookup.

In BI EE 11g, usage tracking can now log logical sql’s greater than 4000 characters into a separate CLOB column.

One of the biggest new features in BI EE 11g is the ability to create native mapviewer maps and then overlay reporting components/visualizations on top of maps. For example if you are doing a country based sales analysis, its easy to color code each country across the world right within the maps. It is also possible to overlay pie charts, bar charts in the maps thereby providing an extensive visual analysis capability. In 10g, one had to go through some complex java scripting to achieve BI EE and mapviewer integration. I have blogged about it here and here. 11g makes this entire integration a lot easier.

Lets try to understand the integration through a simple example. Shown below is a report of Sales performance of 3 US states across 4 years.

We would like to depict this using a map as shown below.

As you see we have 2 visualizations applied on the same map. The first one is a different color coding for each of the 3 states depending on the sales performance. The other is a pivot representation of yearly performance for each state, all within a single map.

To do this we start with enabling the Mapviewer integration by updating the instanceconfig.xml to include the following tags (in each presentation server)

<SpatialMaps>
<ColocatedOracleMapViewerContextPath>/mapviewer</ColocatedOracleMapViewerContextPath>
<RemoteOracleMapViewerAbsoluteURL></RemoteOracleMapViewerAbsoluteURL> <LayerDataLayout>
<MaxRecords>600</MaxRecords>
</LayerDataLayout>
</SpatialMaps>

Then we need to restart the presentation service. This enables the connectivity between BI EE and Mapviewer. After enabling the integration, we then import the standard spatial MVDemo schema into the Oracle Database. This schema contains Layers, Themes and some Base maps for all the major US States, Cities & some inter-state highways. After importing and installing the MVDemo schema, we need to download the Mapbuilder application which will enable us to create a custom map. We will be using this map for rendering in BI EE. The Mapbuilder application can be downloaded from here.

After downloading the Mapbuilder app, we start the application and login to the MVDemo schema. By default this schema contains a lot of built-in themes, Base Maps and Tile Layers.

We start off with creating a simple Base Map called STATE_MAP which will be using 2 out of the box themes THEME_DEMO_STATES and THEME_DEMO_STATES_LINE

This step will enable us to render all the US States and its boundaries within a very simple map as shown below

Once the base map is created, lets create a new tile layer called STATE_TILE_MAP which will use this base map for rendering. We can define all the zoom levels, boundaries etc within the tile layer

Now we have a map that can show all the US states and its boundaries. We need to now expose this map to the mapviewer application (as an admin user through Mapviewer admin screen) by taking it online as shown below

Now the map is available for consumption through BI EE. Remember, the theme that we used above, THEME_DEMO_STATES has an identifier called STATE_ABBR which is basically the key that we need to pass from BI EE. Relationship between BI EE repository columns/subject areas and Mapviewer maps are done from the Manage Map Data screen in the presentation services admin console.

In this screen, we need to import the layers, maps and the images that we want to include as shown below

Edit the THEME_DEMO_STATES theme and map the State column from the repository. There need not be any relation between the column used in spatial and the column mapped in BI EE. All we need to ensure is the attributes should match. In my case, the State abbreviations come from the SH schema and the THEME_DEMO_STATES come from STATES table in the MVDemo schema.

Then navigate to the STATE_TILE_MAP in the background maps and add the THEME_DEMO_STATES again.

This establishes a relationship between the Subject Area and the Map thereby enabling us to add the mapview from answers. After doing this, lets create the report from the SH schema containing just the 3 states for 4 years.

Use the new Map View option to create the map.

This will render the map that we created in mapviewer. If you notice carefully, it will automatically apply some color coding to the 3 states that are part of the report.

The color coding is actually part of default visualization that Map Views apply on a map. Lets change this to something as shown below

Lets now create one more pie chart visualization (for obtaining the yearly breakup of sales in all the 3 states)

Lets use the Year as the column providing the necessary slices for the pie charts

This will give us the necessary visualization using Mapviewer.

Though there are a lot of steps involved, majority of the steps are for setting up the mapviewer. Once we have the maps set up the entire process of adding visualization to maps is quite straight forward.

At a co-located event today in Boston and Frankfurt, company leaders from SAP AG and Sybase came together for the first time to share the strategy and joint product direction for the newly combined companies. In a bold move that brings together established industry leaders and changes the game for enterprise mobility, SAP announced its intent to acquire Sybase on May 12, 2010. In the few months since that time, the two companies have moved quickly to create product road maps and innovations to transform the way people and businesses work. The combined companies now represent the market-leading position in enterprise applications, business analytics and mobile infrastructure.

Pentaho Corporation, the open source business intelligence (BI) and data integraton leader, today announced that Power Costs, Inc. (PCI), the leading global provider of energy supply management software, has successfully deployed the Pentaho BI Suite Enterprise Edition integrated with QlikView at three energy companies. The joint solution allows Power Costs' customers to mine massive amounts of data quickly to help improve their Day Ahead and Real-Time operational Profit and Loss (P&L).

Information Builders, an independent leader in business intelligence ( BI) solutions, today announced that Hercules Tire & Rubber Company has standardized on Information Builders WebFOCUS BI platform to develop Web-based dashboards and self-service reporting applications for managers throughout the organization. As the industry leader in marketing private label tires in the global replacement tire market, Hercules will rely on WebFOCUS to analyze sales data, manage inventory levels, synchronize warehouse operations, and improve fill rates to meet customer demand.

Panopticon Software, the leading provider of real-time data visualization software for monitoring and fast analysis of real-time data, today announced that it has signed a partnership agreement with Kognitio, a global technology firm that offers data warehousing and business intelligence products and services. Under the terms of the agreement, Kognitio is a technology partner for Panopticon's visual analysis software. 

Por quinto año consecutivo, Packt Publishing ha preparado un evento online para distinguir las mejoras soluciones Open Source en diferentes categorías. Si os interesa dar reconocimiento público de vuestra solución open source favorita, podéis nominarla (hasta el 17 de setiembre) o votarla (desde el 27 de setiembre hasta el 5 de noviembre). Los ganadores recibirán no sólo reconocimiento público sino una aportación que bien ayuda para continuar el desarrollo de dichas soluciones.

Ah, y los que votamos podemos ganar un kindle para leer nuestros libros favoritos de packt. Es, sin duda, un buen incentivo. Yo ya he votado.

Más información, aquí.

I’m pleased to announce that, in line with the London and Atlanta Oracle BI 11g Training Days events, our event in Bangalore will now be hands-on.

As a reminder, the Rittman Mead Oracle BI 11g Training Days event is three-day of intense, hands-on technical training on the new 11g release of Oracle Business Intelligence. Over the three days, you’ll learn how to install and configure OBIEE 11g, create the metadata repository, create reports and dashboards, and use new features such as scorecards and the action framework. Aimed at developers with some experience with OBIEE 10g, and covering things at a fast pace, this is ideal training for BI developers looking to get up to speed quickly with the 11g release. Full details of the agenda can be found at http://www.rittmanmead.com/trainingdays 2010, and the event is running at the Fortune Select Trinity Hotel, Bengaluru (Bangalore), India, on November 15th, 16th, 17th 2010.

To take part in the hands-on labs, you will need to bring a laptop with the following minimum specification:

• 3GB of RAM (4GB+ if you intend to use VMWare for your environment, 6GB+ is preferable)
• 50GB of free disk space
• Windows XP SP2 Professional, Windows Server 2003 Enterprise Edition or higher. Note that we have not tested against Windows Vista or Windows 7, and our preferred OS version is Windows Server 2003 Enterprise Edition
• Either Firefox 3, or IE7 or higher (IE6 is not supported for OBIEE 11g)

You will also need to download OBIEE 11g prior to the event, and bring it along with you (either under your own company’s license, or the OTN trial license). We cannot distribute Oracle software at the event.

If you would like to reserve a place, contact us at trainingdays2010@rittmanmead.com. Places are limited to 30, and around 15 have already been reserved as of now.

Klipfolio, Inc., developer of Klipfolio Dashboard, today announced the immediate availability of Klipfolio Publisher. This unique, one-to-many, enterprise notification system operates as a free addition to the firm’s flagship dashboard software platform, Klipfolio Dashboard.

Following strong interest in the beta version of Klipfolio Publisher from corporate communications teams, colleges, universities, and call centers, Klipfolio has released enterprise notification that managers and communicators can easily use to cut through the chatter of corporate communications without adding to the IT workload.

Data Foundations, Inc., a leading developer of enterprise data management and gov­ernance solutions, today announced that the company was ranked highest for MDM technology in The Information Difference's "The MDM Landscape Q2 2010" report released August 10, 2010.  Additionally, Data Foundations ranked highest in customer satisfaction among all twenty- one MDM vendors included in the annual round-up. 

I just published a blog on my music site about the economics of FREE music. Blog authors are, quite naturally, people who adopt a price of FREE strategy. The payout typically coming in other ways and through other avenues.

It can range from prestige, future speaking or consulting