2010 - Page 2 of 8 - BI System Builders

Posted on September 15, 2010March 29, 2019 by Russell Beech — Leave a comment

Business Intelligence Based Strategic Marketing

You can build data analytics to leverage your Strategic Marketing data.

‘Marketing is the management process responsible for identifying, anticipating and satisfying customer requirements profitably… Marketing as a process calls for management decisions on product, pricing, distribution, promotion and personal selling, and customer service. These elements are known as the ‘marketing mix.’

The Chartered Institute of Marketing

Data Analytic Based Strategic Marketing Solutions

The marketing management process and the marketing mix can be complex. Effective marketing planning requires not only intelligence on customers but across a wide spectrum of areas such as product lifecycle and supply chain. The reality is that strategic marketers struggle to get information from diverse parts of the business for analysis and decision making in their plans.

The power of data analytics is that they can use combined data from diverse areas of the business in an enterprise data warehouse holding data often referred to as the single version of the truth. Data in an enterprise data warehouse can be combined and turned into information.

In strategic marketing a strategic marketing plan template can be used as the core. The plan usually comprises of two key components; analysis and decision. At each relevant stage of the plan the marketer should be able to access data analytics that provide the latest data for that stage of the plan. As well as providing marketing intelligence the solution gives the ability to plan, monitor and introduce contingency if veering off track.

Visit the website of The Chartered Institute of Marketing for best practice in strategic marketing

Posted on July 23, 2010March 29, 2019 by Russell Beech — Leave a comment

Increased Performance Through Optimised Semantic Layer Design

The semantic layer sits between the reporting tool e.g. Web Intelligence and the data warehouse. It is a type of graphical abstraction of how the data warehouse tables and relational joins would look if they could be viewed with the naked eye.

The critical function of the semantic layer is aptly describer in its name. All data warehouse platforms are interrogated through some type of code. The universe generates code (hence semantic) so that a query can be issued against the data warehouse. This code will be in the form of Structured Query Language (SQL) for relational platforms and Multi Dimensional eXpressions (MDX) for OLAP based universes.

The powerful feature of the semantic layer is that the business information consumer does not need to understand data warehouses or write code. The business user simply drags and drops objects of interest from a side panel on to their report page, e.g. sales revenue and location objects to generate a table displaying sales revenue by location. The semantic layer generates the code automatically behind the scenes.

However, the optimization of the code generated will be dependent upon the structure of the data warehouse tables and semantic layer configuration for relational tables and the cube and query design for OLAP sources.

Whilst it is possible to generate a semantic layer in a few minutes using an automation wizard, the resultant performance of queries may be disappointing. By performance is meant fast queries that return accurate data sets.

With experience it is possible to gain a tool box of best practice techniques to build a semantic layer designed for performance. As a semantic layer designer a good starting place is to build a query and then to ask:

What does the code generated by my semantic layer look like?

If I were to write it freehand in an optimised way, would it be different?

Do I need to change the semantic layer configuration or data warehouse structure to achieve optimised query code?

Posted on July 23, 2010March 29, 2019 by Russell Beech — 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.