Tag: Query & Analysis

Posted on by

Web Intelligence

SAP BusinessObjects Web Intelligence makes Self-service BI a reality by providing access to data in an intuitive information analysis product. By simply dragging and dropping objects into their reports users can gain business insights and turn these into true Business Intelligence for effective decision making. A business user can quickly create a query from scratch with a few mouse clicks. This is frequently termed ad hoc analysis or creating queries on the fly. Users can then format the retrieved information and analyze it to understand underlying data trends and root causes. The Web Intelligence interface is intuitive in design allowing business analysts and non-technical information consumers to ask spontaneous and iterative business questions of their data using their everyday business language. For example, users could easily create a query that allowed them to compare the performance of their top customers this year over last year. The user could then create their own variables, for example a forecast algorithm and add this to their report. Web Intelligence allows trends to be quickly identified along with sub-optimal areas. Users can drill down and drill across on their data. Web Intelligence access to the data is via a robust semantic layer known as the SAP BusinessObjects universe. The universe objects make query building easy and shields business users from needing to understand database technicalities. Multiple universes from different data sources can be combined in a single report.

The earliest versions of Web Intelligence were only available in thin client. In other words Web Intelligence had to be accessed via InfoView and a web browser. Web Intelligence is now available in the Rich Client version. This is loaded on to the users local machine. A report can now be viewed or developed on the local machine and then published to the BI Platform and shared and viewed as thin client via InfoView. This is great news for people working from home or on a train etc. They can now make changes to their Web Intelligence document off-line and then publish their changes next time they connect.

Posted on by — Leave a comment

Improving Web Intelligence Deep Drill Down Performance

Business Intelligence information consumers and analysts often have the important requirement to drill up and down through an organisation’s data hierarchies. The ability to drill up and down through an organisation’s data enables effective analysis allowing the analyst to identify both underperforming and over performing areas.

Native drilling capabilities exist within Web Intelligence under the name of ‘Scope of Analysis’. To understand Scope of Analysis it is important to know that a Web Intelligence report always returns data from the database in the form of a microcube. Enabling the ‘Scope of Analysis’ functionality then allows drill down and drill up functionality to be performed on the microcube. Web Intelligence uses a microcube because it is built for flexible ad hoc ability to quickly build reports on the fly and for fast query execution. However, the limitations of the ‘Scope of Analysis’ functionality must be understood. When a microcube is built with drilling capabilities beyond four levels (that is four dimensions) the query performance can suffer. Large multi-dimensional queries requiring drill down through numerous dimensions require tools such as OLAP.

Many organisations have hierarchies that run beyond four levels. When large hierarchies are being utilised it is best to use the SAP OLAP client or a suppression technique within a Crystal Report. However, if Web Intelligence must be used then the requirement to drill down beyond four levels without a performance hit can be realised through vertical drilling hierarchy tables.

The basic technique is to map the source system data through staging tables to a horizontal dimension table with hierarchies and then transpose the table to a vertical hierarchy dimension table. The existing horizontal hierarchy dimension data can be transposed to a vertical hierarchy structure using SAP BusinessObjects Data Integrator as part of the ETL process. Before this can happen the vertical hierarchy dimension tables must be modelled in the data warehouse.

The hierarchy tables are then configured in the SAP BusinessObjects universe where they become available to Web Intelligence. There is some complexity that must now occur in the development of the Web Intelligence report. Basically a data provider with a query which includes the @prompt syntax in a filter is developed to run against the vertical tables. After that a block is developed in the report with hyperlinks that use the opendocument.jsp function. The parent report hyperlinks are designed to call themselves with the opendocument.jsp function (the parent and target reports are the same report) but the logic of the report is such that the prompt will be populated with a node of the hierarchy one level up or down from the current level. This forces the report data to refresh at a level higher or level lower than the current and effectively allows the user to drill up or down through the hierarchy.

There are some limitations to this system. Firstly, the development overhead for the tables and ETL jobs. Secondly, it takes some advanced skill to be able to develop the report. Thirdly, drill down is only at one level per click i.e. there is no multi-level tree walking. That is that from level one in the hierarchy the user must go to level two, it is not possible to jump immediately to level three. This is not true if Crystal reports is utilised.

Posted on by

Team

Russell Beech, Founder of BI System Builders & Cornerstone Solution®

I architect data solutions and superintend the solutions that I architect. I build teams that are usually a mix of full-time employees and sub-contractors. I ensure mentoring and knowledge transfer. The emergence of big data has opened the door to a rise in interest in predictive analytics aka machine learning. The technology changes have provided the opportunity to architect data solutions which combine enterprise data warehousing experience with data lake concepts and to apply knowledge of statistics to that data to deliver predictive analytics. To that end I’m putting effort into understanding how the evolving technologies hook in to each other. I have a focused interest in big data technologies especially on the Google Cloud Platform. Technologies such as Hadoop, Spark, Apache Beam, BigQuery and Tensorflow (machine learning) and their integration/virtualization with the enterprise data warehouse.

You can find me on LinkedIn here and subscribe to watch BI System Builders videos on our YouTube channel here.