| I have posted the same article on my blog here. you | | | | This will give an extremely slow performance as well |
| can also read my other articles. | | | | as from maintainance perspective also the time taken |
| Recently I went through the website of Qliktech and | | | | to load the fact table will be more and unpractical. |
| read where it says "And unlike traditional BI, QlikView | | | | Once we decide on our strategy for the development, |
| delivers immediate value with payback measured in | | | | we start with developing the data model as per the |
| days or weeks rather than months, years, or not at | | | | designs created in the previous phase i.e Planning and |
| all.". It made me think for a while. what are they | | | | Designing. |
| referring to ? Do they mean Qlikview as a BI solution | | | | The data model is developed generally in layers. In |
| can be implemented in weeks? Are they talking only | | | | Oracle OBIEE, there are three layers and Cognos |
| about small department level deployments or just the | | | | allows you to build as many layers as you wish and |
| development phase of the BI project life cycle or is it | | | | BO provides 2 layers(I am not very sure on this and |
| just a sales pitch? I am just wondering how much part | | | | would request some comments on this). |
| a tool plays in a BI project Implementation life cycle and | | | | In Qlikview, we can make it single layered or 2 layered |
| how does a tool effect the development phase. | | | | by renaming the column names in the script. |
| Whether you use Qlikview or some traditional BI like | | | | For all practical purposes, upto 3 layers is a good idea |
| OBIEE for that matter, about 70% of the project life | | | | but you may agree or disagree on that. Based on |
| cycle remains unaffected or untouched. | | | | yourrequirements of maintainance you can decide on |
| Then I thought about my recent Oracle BI Project. If | | | | that. |
| we would have replaced OBIEE with Qlikview, will the | | | | OBIEE has 3 predefined layers namely Physical Layer, |
| whole project gets implemented in weeks instead of | | | | Business and Modeling layer and Presentation layer. |
| months or years. I don't think so. | | | | Physical layer is where we simply make connections |
| Well, The topic is BI Project Implementation Life Cycle | | | | to the database and import the metadata for |
| and not Qlikview. | | | | database objects like tables, columns, views, primary |
| I have worked on some small Qlikview project | | | | and foreign key relationships. Now we do not make |
| Implementations which generally span from 2-4 Months | | | | any changes related to changing the names of the |
| and also large OBIEE implementations ranging from 9 | | | | columns which help the administrator and developers |
| months to close to 1 year. | | | | from maintainance perspective. |
| My latest assignment was a large Oracle BI (OBIEE) | | | | Based on the available physical layer objects we |
| Global Implementation Project. | | | | create our OLAP models in Business layer by defining |
| I would like to share my knowledge and learnings from | | | | dimensions, facts and Hierarchies. |
| the current as well as previous projects. I will also | | | | In the presentation layer, we categorize the objects |
| discuss how large deployments differ from smaller | | | | based on subject areas from the objects available in |
| ones and also the different phases involved in a BI | | | | OLAP model in Business layer. We rename the |
| project. | | | | objects present in Presentation layer from end users |
| Big deployments generally includes ETL as well as BI | | | | perspective or business terminology. |
| and takes somewhere around 9 months to 2 Years to | | | | This whole process really helps the developers to |
| implement the solution. But when we compare it with | | | | understand and visualize the complete model and |
| small deployments using tools like Qlikview, it has a | | | | saves lot of time in debugging or day to day |
| small life cycle ranging from somewhere around 3 | | | | maintainance activities. This process oriented approach |
| months to 6 months because they generally do not | | | | is again an attempt to divide and rule and making our |
| involve a datawarehouse. | | | | life a bit simpler. |
| There are various reasons for not having a | | | | Once you are done with your model, the next step is |
| datawarehouse like | | | | to start developing your reports and bringing in the |
| 1. Time and Cost: For smaller organisations the IT | | | | identified resources for report development into the |
| budgets are generally small. Including Datawarehouse in | | | | team. |
| a BI initiative increases the project cost significantly. | | | | The reports based on the subject area are divided |
| The organisations needs to Purchase additional license | | | | among the team and with the help of report |
| for a new database. Even if they already have a | | | | specifications available in Technical design document |
| database in place, it can not be used for various | | | | created in the previous phase.The reports are |
| reasons and is not recommended as well. The | | | | generally designed by report developers. |
| organisation also needs to purchase a license for a | | | | While the report designers are engaged, the data |
| ETL tool and required hardware. Other than the | | | | model developers work developing the next subject |
| software and Hardware, the cost is involved in hiring | | | | area. Initially the team size is less and as the work |
| resources for database and ETL. | | | | keeps on growing, more people are added in the team. |
| 2. Small data size: As small organisations do not have | | | | The development also includes setting up the object |
| large or huge data sets (Even if they have multiple | | | | level as well as data level security , creating users and |
| applications) and hence generally neglect the relevance | | | | groups and creating variables as per the technical |
| and importance of having a datawarehouse in place. | | | | design document. |
| Not only it complements BI by providing faster | | | | 4. Testing |
| response times for BI end users but also it reduces the | | | | Testing is one of the most critical phase and also |
| development cycle of the BI by reducing the level of | | | | sometimes most time consuming phase of a BI project |
| complexities of the logic. Using a datawarehouse helps | | | | Implementation. |
| in having much simpler BI data models which requires | | | | There are 3 types of testing done on a project |
| less development time and maintainance. | | | | namely Unit Testing (UT), System Integration Testing |
| 3. Less complex data models: Small organisations | | | | (SIT) and User Acceptance Testing (UAT). |
| generally have small customised business applications | | | | Unit Testing is generally done by the developers and |
| running which do not have very complex OLTP data | | | | they test that the code or report they have developed |
| structures and hence it becomes relatively easy to | | | | is working as per the requirement or specifications. |
| design BI data models eliminating the need for a | | | | This is performed in Development environment where |
| datawarehouse solution. | | | | Developers had developed thereport. Developers |
| 4. Less number of Users: Organisations having smaller | | | | prepare a test case plan for themselves listing all the |
| number of BI users (Using the application at a given | | | | cases they would like to test. These cases could be |
| point of time) generally have this option of building their | | | | testing the font size, color, spell check, prompts, filters, |
| BI solution directly on top of their OLTP database. | | | | data values etc. |
| They can either allow BI users to directly query the | | | | Sometimes developers may exchange the reports |
| OLTP database which in some way reduces the | | | | with their team members to perform a peer unit |
| performance of the business application users already | | | | testing and this is a good practice as it is little easier to |
| connected or they can use tools like Qlikview or | | | | find out mistakes in other's report than your own. |
| Hyperion Essbase which employs this technique of | | | | Once Unit testing is complete, the code (data models |
| storing the data in their proprietery data file formats | | | | and reports) are transferred to Test Environment. |
| and allow a disconnected type analysis. The later one | | | | This Testing Environment is generally similar to the |
| is a much better option as it provides a much faster | | | | production environment but that is not the case |
| performance as against querying an OLTP database | | | | always. Having the test environment same as |
| as for some reason unknown to me these proprietary | | | | production environment allows us to anticipate the |
| data files are highly optimized to be used by their | | | | performance and behavior of the developed solution in |
| respective applications and also this do not impact the | | | | a much better way. |
| performance of the OLTP application. | | | | Once the code is transferred to Test environment, |
| Now let's discuss a typical BI Project life cycle which | | | | System Integration Testing is performed. SIT checks |
| comprises of following phases: | | | | how all the individually pieces works collectively and |
| 1. Project Initiation and Analysis: | | | | are integrated well to produce the desired output. This |
| For any project to start a business need is a must. for | | | | test is performed by the IT team members or by |
| example "replacing an old customized reporting | | | | identified testers from the client side. However before |
| system with a new reporting and analysis system | | | | they perform the test a sampling based dry run is |
| which is more flexible, cost effective, fast and requires | | | | required to be performed by the development team. |
| less maintainance" or "Need to consolidate data from | | | | Once the testing team start testing the application, they |
| disparate sources and a common standardized | | | | put all the defects in a defect log sheet mentioning |
| platform for reporting and analysis" could be a | | | | thedetails of the defect. |
| business need.This business need is evaluated at a | | | | At this point of time, it is recommended to appoint |
| very high level as to how critical is the need and how it | | | | some dedicated members from the development |
| well it falls in line with the strategic objectives of an | | | | team to fix those identified defects and update the |
| Organization. Formally a Project manager is identified | | | | defect log sheet. While this activity is going on, other |
| and appointed who further investigate and perform | | | | team members are assigned next set of development |
| some first level of analysis. | | | | work and they keep working on developing next batch |
| It is then Project manager who creates a document | | | | of reports. It may happen that the same team will fix |
| with details like Business case, initial risk assessment, | | | | the defects by allocating some portion of their time |
| scheduling and budgeting, stakeholders identification etc | | | | and rest of the time in developing next batch reports. |
| taking help from the available resources and then a | | | | But this may bring some imbalance or turbulence in the |
| formal approval is taken. | | | | system as it will become very difficult to really work |
| 2. Planning and Designing: | | | | on two things simultaneously. Bug fixing involves lot of |
| Requirements gathering is the most important and | | | | coordination with ETL team as well as testers and |
| critical part of the whole project. A requirement | | | | sometimes consumes time more than what was |
| document is created for this purpose and all the | | | | anticipated which ultimately may impact the |
| requirements are documented in much details. The | | | | development activities. Having a dedicated team for |
| requirements are finalized and then project scope is | | | | bux fixing activities would be very useful and effective. |
| created. To fulfill these requirements, various tools are | | | | Once all bugs are identified and fixed, the Business |
| evaluated and whichever is the best fit is selected. | | | | users are asked to perform the User acceptance test. |
| Now the team resources are identified. All the logistics, | | | | The test cases are prepared by business users and |
| hardware and software requirements are identified | | | | they check if all the requirements are fulfilled and they |
| and procured. | | | | really getting what they want. Here business users |
| Data models are designed and it is documented in a | | | | compare the result set with the result set from their |
| Technical design document which also specifies other | | | | existing system and verify the results. |
| technical details like features and functionalities of the | | | | One of the biggest challenge in SIT or UAT is if any |
| design, security and authentication, best practices, | | | | data related inaccuracies are found, it becomes really |
| repository variables, layers, database connections, | | | | difficult to find the root cause. The developer needs to |
| connection pools etc etc.. | | | | check which version is true. There may be some |
| Prototypes are designed to show the features and | | | | internal logic or transformations or formulas applied in |
| functionalities as documented in the requirement | | | | the existing application and this analysis consumes |
| document and is formally approved. Project kickoff. | | | | whole lot of time requiring lot of coordination with the |
| 3. Development: | | | | team supporting the existing application or system. |
| Before starting the development of the data models | | | | 5. Implementation |
| and reports, the project scope is divided into small | | | | After the code is tested and verified completely, it is |
| more manageable modules or batches. | | | | transferred to the production environment and opened |
| It is a good practice to divide it on the basis of | | | | for the real end users. Before that general awareness |
| functional areas and subject areas. | | | | sessions and training sessions are held for the end |
| So let's suppose we decided to do it for functional | | | | users to use the new system. For some time the new |
| area Human Resources among other functional areas | | | | system is put on a stabalization period (Generally |
| like Sales and Distribution, finance, Inventory and | | | | ranges from 15 days to 2 or 3 months) where in case |
| Manufacturing etc under which we are planning to | | | | a bug occurs, it is fixed by the same development |
| create different subject areas like | | | | team. |
| Employee Compensation and Bonus, Learning and | | | | During this time the new system or application is made |
| Development, Employee movements and transfers | | | | to run parallely with the existing system or application |
| etc. You may have one data model for complete HR | | | | and once the stabilisation period is over, the old system |
| or seperate data models for each subject areas. | | | | is replaced partially or completely. |
| For smaller organizations with less complex OLTP | | | | Once the stabilization period is over and the system |
| data structures, it is possible and feasible to have a | | | | gets stabilised, the support team is provided with all the |
| single data model for complete Human Resources. | | | | project related knowledge and the development team |
| For large and complex OLTP structures, it is generally | | | | is rolled off. |
| not possible as otherwise the size of the fact table will | | | | The implementation of BI Project gets over. |
| be extremely large horizontally as well as vertically. | | | | |