Enhance Business Intelligence Performance

Hi All,

In any Business Intelligence Implementation, the key factor is the performance. Performance factor always plays a key role in User accepting or liking the application.
We should do everything possible to enhance the performance and here are some tips some of which are very generic and can be used in any BI Implementation.
From a solution perspective:
1. Use of Datawarehouse: Though a datawarehouse is not compulsory for any BI Implementation, we cannot simply think about a BI solution without a datawarehouse because of the advantages it offers in terms of performance and simplicity. This is important for small implementations who sometimes neglect and underestimate the use of datawarehouse.
From a BI Tool Perspective:
1. For every tool it is important to reduce the size of the application by removing the unnecessary objects.
2. Try to create different database connections for different set of users based on the priority.
3. Try to create a seperate database connection for populating any session level variables.
4. Try to make the best use of system Cache. If the tool allows to cache the results of the queries, use it and if possible pre populate the queries which are very frequent and used mostly.
5. Minimise the calculations happening at the BI level by pre calculating them in datawarehouse.
From a database perspective:
1. The most important thing is to perform every possible calculation you can do in database. We very frequently neglect this saying this is a small thing and cal be calculated or performed at BI level. We should avoid this and if something is possible in ETL or database, do it here even it cost you adding a few extra columns or tables.
2. If you can create a perfect star models, nothing like that.
3. Try to use the database techniques like Partitioning and indexing to enhance the performance of database queries.

There may be several other tips and techniques which we can follow to improve performance and if you have any, please feel free to share.
Till next time.

Regards
Manohar Rana

BI Project Implementation Life Cycle

Hi,

Recently I went through the website of Qliktech and read where it says "And unlike traditional BI, QlikView delivers immediate value with payback measured in days or weeks rather than months, years, or not at all.". It made me think for a while. what are they referring to ? Do they mean Qlikview as a BI solution can be implemented in weeks? Are they talking only about small department level deployments or just the development phase of the BI project life cycle or is it just a sales pitch? I am just wondering how much part a tool plays in a BI project Implementation life cycle and how does a tool effect the development phase.
Whether you use Qlikview or some traditional BI like OBIEE for that matter, about 70% of the project life cycle remains unaffected or untouched.
Then I thought about my recent Oracle BI Project. If we would have replaced OBIEE with Qlikview, will the whole project gets implemented in weeks instead of months or years. I don't think so.

Well, The topic is BI Project Implementation Life Cycle and not Qlikview.
I have worked on some small Qlikview project Implementations which generally span from 2-4 Months and also large OBIEE implementations ranging from 9 months to close to 1 year.
My latest assignment was a large Oracle BI (OBIEE) Global Implementation Project.
I would like to share my knowledge and learnings from the current as well as previous projects. I will also discuss how large deployments differ from smaller ones and also the different phases involved in a BI project.
Big deployments generally includes ETL as well as BI and takes somewhere around 9 months to 2 Years to implement the solution. But when we compare it with small deployments using tools like Qlikview, it has a small life cycle ranging from somewhere around 3 months to 6 months because they generally do not involve a datawarehouse.
There are various reasons for not having a datawarehouse like

1. Time and Cost: For smaller organisations the IT budgets are generally small. Including Datawarehouse in a BI initiative increases the project cost significantly. The organisations needs to Purchase additional license for a new database. Even if they already have a database in place, it can not be used for various reasons and is not recommended as well. The organisation also needs to purchase a license for a ETL tool and required hardware. Other than the software and Hardware, the cost is involved in hiring resources for database and ETL.

2. Small data size: As small organisations do not have large or huge data sets (Even if they have multiple applications) and hence generally neglect the relevance and importance of having a datawarehouse in place. Not only it complements BI by providing faster response times for BI end users but also it reduces the development cycle of the BI by reducing the level of complexities of the logic. Using a datawarehouse helps in having much simpler BI data models which requires less development time and maintainance.

3. Less complex data models: Small organisations generally have small customised business applications running which do not have very complex OLTP data structures and hence it becomes relatively easy to design BI data models eliminating the need for a datawarehouse solution.

4. Less number of Users: Organisations having smaller number of BI users (Using the application at a given point of time) generally have this option of building their BI solution directly on top of their OLTP database. They can either allow BI users to directly query the OLTP database which in some way reduces the performance of the business application users already connected or they can use tools like Qlikview or Hyperion Essbase which employs this technique of storing the data in their proprietery data file formats and allow a disconnected type analysis. The later one is a much better option as it provides a much faster performance as against querying an OLTP database as for some reason unknown to me these proprietary data files are highly optimized to be used by their respective applications and also this do not impact the performance of the OLTP application.

Now let's discuss a typical BI Project life cycle which comprises of following phases:

1. Project Initiation and Analysis:
For any project to start a business need is a must. for example "replacing an old customized reporting system with a new reporting and analysis system which is more flexible, cost effective, fast and requires less maintainance" or "Need to consolidate data from disparate sources and a common standardized platform for reporting and analysis" could be a business need.This business need is evaluated at a very high level as to how critical is the need and how it well it falls in line with the strategic objectives of an Organization. Formally a Project manager is identified and appointed who further investigate and perform some first level of analysis.
It is then Project manager who creates a document with details like Business case, initial risk assessment, scheduling and budgeting, stakeholders identification etc taking help from the available resources and then a formal approval is taken.

2. Planning and Designing:
Requirements gathering is the most important and critical part of the whole project. A requirement document is created for this purpose and all the requirements are documented in much details. The requirements are finalized and then project scope is created. To fulfill these requirements, various tools are evaluated and whichever is the best fit is selected. Now the team resources are identified. All the logistics, hardware and software requirements are identified and procured.
Data models are designed and it is documented in a Technical design document which also specifies other technical details like features and functionalities of the design, security and authentication, best practices, repository variables, layers, database connections, connection pools etc etc..
Prototypes are designed to show the features and functionalities as documented in the requirement document and is formally approved. Project kickoff.

3. Development:
Before starting the development of the data models and reports, the project scope is divided into small more manageable modules or batches.
It is a good practice to divide it on the basis of functional areas and subject areas.
So let's suppose we decided to do it for functional area Human Resources among other functional areas like Sales and Distribution, finance, Inventory and Manufacturing etc under which we are planning to create different subject areas like

Employee Compensation and Bonus, Learning and Development, Employee movements and transfers etc. You may have one data model for complete HR or seperate data models for each subject areas.
For smaller organizations with less complex OLTP data structures, it is possible and feasible to have a single data model for complete Human Resources.
For large and complex OLTP structures, it is generally not possible as otherwise the size of the fact table will be extremely large horizontally as well as vertically. This will give an extremely slow performance as well as from maintainance perspective also the time taken to load the fact table will be more and unpractical.
Once we decide on our strategy for the development, we start with developing the data model as per the designs created in the previous phase i.e Planning and Designing.
The data model is developed generally in layers. In Oracle OBIEE, there are three layers and Cognos allows you to build as many layers as you wish and BO provides 2 layers(I am not very sure on this and would request some comments on this).
In Qlikview, we can make it single layered or 2 layered by renaming the column names in the script.
For all practical purposes, upto 3 layers is a good idea but you may agree or disagree on that. Based on your
requirements of maintainance you can decide on that.
OBIEE has 3 predefined layers namely Physical Layer, Business and Modeling layer and Presentation layer.
Physical layer is where we simply make connections to the database and import the metadata for database objects like tables, columns, views, primary and foreign key relationships. Now we do not make any changes related to changing the names of the columns which help the administrator and developers from maintainance perspective.
Based on the available physical layer objects we create our OLAP models in Business layer by defining dimensions, facts and Hierarchies.
In the presentation layer, we categorize the objects based on subject areas from the objects available in OLAP model in Business layer. We rename the objects present in Presentation layer from end users perspective or business terminology.
This whole process really helps the developers to understand and visualize the complete model and saves lot of time in debugging or day to day maintainance activities. This process oriented approach is again an attempt to divide and rule and making our life a bit simpler.
Once you are done with your model, the next step is to start developing your reports and bringing in the identified resources for report development into the team.
The reports based on the subject area are divided among the team and with the help of report specifications available in Technical design document created in the previous phase.The reports are generally designed by report developers.
While the report designers are engaged, the data model developers work developing the next subject area. Initially the team size is less and as the work keeps on growing, more people are added in the team.
The development also includes setting up the object level as well as data level security , creating users and groups and creating variables as per the technical design document.

4. Testing
Testing is one of the most critical phase and also sometimes most time consuming phase of a BI project Implementation.
There are 3 types of testing done on a project namely Unit Testing (UT), System Integration Testing (SIT) and User Acceptance Testing (UAT).
Unit Testing is generally done by the developers and they test that the code or report they have developed is working as per the requirement or specifications. This is performed in Development environment where Developers had developed the
report. Developers prepare a test case plan for themselves listing all the cases they would like to test. These cases could be testing the font size, color, spell check, prompts, filters, data values etc.
Sometimes developers may exchange the reports with their team members to perform a peer unit testing and this is a good practice as it is little easier to find out mistakes in other's report than your own.
Once Unit testing is complete, the code (data models and reports) are transferred to Test Environment.
This Testing Environment is generally similar to the production environment but that is not the case always. Having the test environment same as production environment allows us to anticipate the performance and behavior of the developed solution in a much better way.
Once the code is transferred to Test environment, System Integration Testing is performed. SIT checks how all the individually pieces works collectively and are integrated well to produce the desired output. This test is performed by the IT team members or by identified testers from the client side. However before they perform the test a sampling based dry run is required to be performed by the development team.
Once the testing team start testing the application, they put all the defects in a defect log sheet mentioning the
details of the defect.
At this point of time, it is recommended to appoint some dedicated members from the development team to fix those identified defects and update the defect log sheet. While this activity is going on, other team members are assigned next set of development work and they keep working on developing next batch of reports. It may happen that the same team will fix the defects by allocating some portion of their time and rest of the time in developing next batch reports. But this may bring some imbalance or turbulence in the system as it will become very difficult to really work on two things simultaneously. Bug fixing involves lot of coordination with ETL team as well as testers and sometimes consumes time more than what was anticipated which ultimately may impact the development activities. Having a dedicated team for bux fixing activities would be very useful and effective.
Once all bugs are identified and fixed, the Business users are asked to perform the User acceptance test. The test cases are prepared by business users and they check if all the requirements are fulfilled and they really getting what they want. Here business users compare the result set with the result set from their existing system and verify the results.
One of the biggest challenge in SIT or UAT is if any data related inaccuracies are found, it becomes really difficult to find the root cause. The developer needs to check which version is true. There may be some internal logic or transformations or formulas applied in the existing application and this analysis consumes whole lot of time requiring lot of coordination with the team supporting the existing application or system.

5. Implementation
After the code is tested and verified completely, it is transferred to the production environment and opened for the real end users. Before that general awareness sessions and training sessions are held for the end users to use the new system. For some time the new system is put on a stabilization period (Generally ranges from 15 days to 2 or 3 months but it could be even more) where in case a bug occurs, it is fixed by the same development team.
During this time the new system or application is made to run parallel with the existing system or application and once the stabilization period is over, the old system is replaced partially or completely.
Once the stabilization period is over and the system gets stabilized, the support team is provided with all the project related knowledge and the development team is rolled off.
The implementation of BI Project gets over.

Please feel free to share your comments.

Manohar Rana

Hyperion Essbase Book

Hi,

One good news for people who wants to learn Hyperion Essbase on their own.
There are not many books available on Essbase and I know only one book which is available 'Look smarter than you are with Hyperion Essbase' by Edward Roske.

Sarma and Joseph have written a 444 page book on Hyperion Essbase 9 with the title

'Oracle Essbase 9 Implementation Guide'.

The book covers Installation, Essbase cube designing and creation, loading data, Excel add-in, MDX, Max-L, calculation scripts, reports etc and explained them with real time examples in sufficient step by step details with the help of pictures, Notes and Hints.

The book is for students who are new to Essbase and Multidimensional OLAP concepts as well as professionals who are already working on the Essbase technology wants to refresh their knowledge.

For those who feel that Essbase is only for financial applications, this book will completely change their belief as at one place the author mentioned as
"Oracle Essbase is widely known as a financial analytical tool. We want to change the mindset just a bit, right here and now. Oracle Essbase absolutely is a superior financial OLAP tool, but it is an equally superior OLAP tool for just about any type of data analysis."
As the Authors have rich real time implementation experience in a Motor company, most of the examples are from that domain only. I am sure the readers would greatly benefit from these real time examples.

I am happy to see that Venkat has reviewed this book and feel proud to have worked with him in Oracle.

Though this is a great attempt to bring out such a nice book there are few points which I think should have been added in this book to make it more useful and practical.

1. As Essbase is not like other BI tools where you can visualise the schema and how dimensions are joined or related to facts, it becomes difficult in Essbase to visualise that. For that reason, it would have been helpful if a small cube have been designed with some small dimensions and facts and a relation is shown with a OLAP schema diagram. I am saying this because I myself was not able to understand how the data which is getting loaded into the Essbase cube is related to each other and it took me so much time to understand that. Please excuse me if I am the only one having this problem.

2. I do not think Essbase is complete without EIS(Essbase Integration Services) in version 9.3.1 or Essbase studio in Version 11.
Even though you can do almost all of the Essbase stuff without them but it would have been much better even if a small portion has been described about either of them in the book. As going forward Essbase studio is going to replace EIS, a small chapter on Essbase studio is desirable.

You can download one complete chapter Essbase Data and Design Considerations. By downloading this chapter in pdf format you can also see the list of chapters and table of contents.

All in all, a great attempt and thanks to people involved in bringing out a much needed book on Essbase.

Manohar Rana

Qlikview Personal Edition

Hi,

Qlikview is the emerging name in the Business Intelligence world and becoming very popular among the user community due to its easy to use interface, fast deployment and very strong visualisation.

Qliktech released its version 9 recently with a new Edition called Qlikview 9Personal Edition having all the developer features.

Earlier Qlikview use to provide only 15 days of evaluation period for people who want to give it a try which is really a very small duration to try or evaluate a Business Intelligence Tool.
This limits the user to use it beyond that period.

Now with version 9, Qliktech allows to download the personal edition from their website for free and use the product for personal use for an unlimited period of time and does not require any key.

So, that in real sense a very good news for the student community as well as for startup businesses or small growing businesses to use Qlikview for their personal use.

As small business generally has lot of data in flat files or excel files, Qlikview becomes a very handy Business Intelligence Software as Qlikview is very easy and effective when you have lot of excel files or combination of flat files, desktop database and relational databases.

Now you can develop or get developed Business Intelligence applications and do your analysis to know more about your business. You can export the reports/charts to excel,pdf,gif or png images and email it to your business partners or other members of your Business.
You can also copy/send the complete Qlikview document to another machine where Qlikview free personal edition is installed.

And in future as your business grow and you have the right budget, you can get your personal edition converted to a licenced version without affecting your Qlikview Business Intelligence Application that you had created.

But there are some limitations of Qliview personal edition. As your Qlikview is not a licensed one, you cannot ask for any support from Qliktech which I think is not a big issue. You can manage your application without any support which generally is required for big deployments.

Another limitation is that you cannot use an application developed in Licensed version in your personal edition however an application developed in personal edition can be used with a licenced version.
The application developed in a licenced version when opened in personal edition does not load the data hence a reload of the Qlikview application with the fresh data is required which effectively means that the person having the personal edition should have all the data(flat files or excel files) or access to relational databases to reload the application.

Another limitation is that you cannot publish your dashboards over the web to share it with other users as the personal edition does not provide the web component which allows the users to access the application over the web using Internet browsers.
However, you can use utilities like desktop sharing, remote login or web conferences to allow someone to use that application but in that case you will have to expose your complete application. The person accessing the application through remote login can delete the application or make any modifications.

These limitations may not be relevant to most of the Students or Business Owners to use Qlikview personal edition for learning Qlikview or Using for your analysis.

Replacing scattered excel files with one single source of information will be a great benefit in terms of managebility, reusability and productivity which will save lot of your time and efforts.
Top class visualisation in reports and dashboards and ease of perfoming analytic operations like drill down, Top N analysis, Pivoting,identifying problems etc will be an added advantage.

From Qliktech's perspective, they have made a very strategic move and will give them benefits in the coming years and will also help them in growing their prospects base and users base. This will definately make Qliktech's reputation much better in the market and help in developing Brand.

Qliktech's major install base or user base is small business or large business with small deployments and this move will make its presence more stronger in this area. Also, the buyers risk on investment will be greatly reduced.

Not many companies provide free unlimited period evaluation versions of their Busniess Intelligence softwares. I know only one name i.e Oracle. Even SAP Business Objects and IBM Cognos does not provide more than 30 days evaluation.

I am hopeful that Qliktech very soon will appear in Gartner's Business Intelligence Magic Quadrant and will give a direct competition to the bigger vendors.

Manohar Rana

Pentaho - Open Source BI

Hi,

From past few days I was really very anxious to try my hands on Pentaho.

I heard a lot about the open source Business Intelligence Tools and two names that comes to my mind are Pentaho and Jaspersoft.

So I simply logged on to the Pentaho Website and looked for some demos. Then I looked at the hosted environment. The website is bit confusing and a new visitor like me found it really difficult to search for things may be because there are lot of things there.
Anyway, the good thing I found is this 'Try our Hosted Demo' which not many websites has.

The first screen after logging in looked pretty simple.

This is good. End users generally gets confused if you have so many buttons and they start loosing insterest from the first step itself. If the interface is simple, it gives them some sort of relief and confidence. The same is true with me.
Upper Left side pane shows the folder available and below it will show the files available in that.
The three icons in the middle of the center window provides you options to choose to create a simple tabular report(or a print quality report) or to create some charts. The third button is unclear.
Let's try on the first option first.

OK. This is a wizard. End users love wizards. If you can do something by simply clicking on Next button using defaults, nothing like that. so far so good.
Here I am not considering how the datasets are selected or from where the data is coming simply because the hosted demo environment does'nt have anything like that. So that's a seperate story and I will try to uncover that as well.
So selecting a Business Model, I think will be selecting a cube created for the purpose. That means we are doing OLAP. Now its not clear as of now what technology Pentaho uses, I mean ROLAP or MOLAP. That's a question as of now.
Let's move on and see what's there in Next.

that's a standard wizard. Available items and selected items. There is a distinct selection check box. I think here we do not have option to select which items we need to have distinct values. Checking this option will give distinct values for all items. Little confused here. No worry. let's move ahead.

Here we have an option to categorise the items under levels and put filters as well. Though I did not checked these options, I expect them to be standard.


Formatting options. Good. Users can do such things in the wizard itself. We have a huge list of paper options as well which can help in getting better print quality. You can preview the report in various standard formats. HTML, Pdf, Excel etc. Now the Next button has got disabled, that means we are all set to go and see some action now. Let's click on GO.

Here is my report. Not looking that bad. If I can get a report like this just by clicking on Next button, I am fine with that. But now I know I can make it much better. Interesting so far.
Lets move ahead and create an analysis report.
Simply click on the button having a chart icon on the top bar.

A small window appeared. choose the schema and the cube. I think this schema is similar to Essbase application which has multiple cubes inside it. The application generally is based on the functional aspect of an organisation like sales, finance and inventory etc..
For this demo we just have one schema and a cube inside it. Let's click on OK.

Now wait for a minute and take a look a it. Some buttons appeared on the top. In a way this is good to present the necessary buttons only. I mean when we were creating the static report, these buttons were not there and that saved a lot of confusion probably. Adding things could be easy but to make things clean and simple is really difficult. Let's appreciate this.
Let's click on all regions(+).


I generally like this kind of pivoting using +_ signs. Not every tool have this. I did not see this in Oracle OBIEE ans while talking to customers, I have observed that this is the most preferred way of having a pivot report. Also, one more thing I liked here is the ability to expand the departments for a particular region.

Now let's see what does these buttons means. The first button looking like a cube is OLAP Navigator. From this what I understand is you an add or modify the cube members. Click on it.

The icons are self explanatory but I did not understood there use until I tried them. So here you can change them to appear in rows and columns. By default, dimensions are under rows and measures under columns which is pretty logical. You can rearrange the order of their appearance and also apply filters. So I created a filter to have only one region.

Here is some difference. Here you can check or uncheck the members. But I did not see anything to select multiple members. I need to tick them individually to select. Here if we had that available and selected sections, it would have been much better. Because in actual scenarios, you have a huge list of members in your dimensions and in those cases it would be practically difficult to tick each member manually.
Also, I wanted to remove or hide Positions dimension from my report and failed to find any option to do that. Quite possible that I am not able to find it but if it not there, Pentao will earn one more negetive point.
Ok let's move ahead and look at the next option. The button says MDX. let's look at it.

It seems the database is Multidimentional but which one is not clear. Quite possible that Pentaho can connect to multidimensional sources directly. In that case, this would be very useful tool.
Other few options are very general like sorting and changing the layout, swapping rows and columns.
I am very eager to see some charts now. let's click on the chart button. I hope this would be some wizard based.

I clicked on it. no wizard but nothing less than that. A chart got created automatically. That's OK.
I actually wanted to see the options. There is another button besides the chart button. That is to edit the properties.

Options looking pretty OK. But I did not see anything in case I want to have the numbers or figures appearing on the bars or atleast on the chart against legends. Almost all standard chart types are available.

Two uncommon charts which I found are Pie charts by column and by rows. Not all the tools does have these charts. At the same time I did not see other advance charts like scatter charts. I am not sure if it has gauge charts and sliders. Quite possible that this may be available in some other version or component. One thing wrth mentioning is drill through capability. In properties only there is an option Enable drill through. I checked it. Lets see how does it work.

Here I selected pie charts by columns just to see how does it look. Charts are fine. Enabling drill through activated the hyperlink on the charts. clicking on the chart will create the drill through report.

This is fantastic. Generally creating drill through reports are not so easy. But this is something really easy and user friendly.
Now lets take a look at some sample dashboard.

Here on Dashboards, I see the values are displayed. But I am not sure whether these are text boxes created for the purposes or available in properties. The charts were also not interactive. I mean when I clicked on the region, though the sales trend chart below changed but didnt show any option to drill down. My guess is the first chart(Territory) is static with values pasted pasted on it. In that case the dashoard needs to be made more functional and interactive.
Another dashboard which attracted my attention is this one.

I dont know how this has been made or how difficult it is to create something like this but it's looking pretty interactive. On the map, if you click on any region, the values will pop up like this.

Overall, the tool is user friendly, easy to use, easy to understand, easy to manage and having good reporting, ad-hoc query, dashboard and OLAP analysis capabilities.

Though I did not checked its scheduling, notification and advance functionalities like row level security which may be out of the scope of this onlie demo. But I will try to gather all these information and probably will try to download the tool and loo at it from the scratch.

Any inputs from experts are welcome. Please spare me if you find anything incorrect and let me know.