STRATEGIC USE OF INFORMATION SYSTEM
Executive Summary
In this competitive market, most retail based businesses that do not implement EDW will find themselves less competitive and struggle to survive in the market (Tim, 2002). EDW as part of ERP system has been the only solution in analysing collaborated data from various sources. EDW will make cross-departments data integration possible.
Similar to any other new system implementation, it is important to make a systematic preparation and careful feasibility studies. Good Health does not currently have necessary capability to implement EDW, however with adequate trainings and step-by-step guidance, Good Health will be able to successfully implement EDW. For the existing infrastructure, Good Health will also need to improve / upgrade them. In overall, Good Health has self-prepared to accept the new system and supported by motivation to improve its competitiveness as well as willingness to dedicate its infrastructure and employees towards this EDW, Good Health will be able to successfully complete the project without any major problems.
EDW being implemented in Good Health will be provided by PeopleSoft as the front-end supported by UNIX-based IBM DB/2 database as back-end. A server equipped with 16 CPUs and multi-threading technology will be installed to support this EDW while the database will be kept in separate data centre where all the RAID system and independent tape-backup will be installed. Network infrastructures will be upgraded to fibre for the backbone and running managed gigabit network throughout sub-servers. Workstations will be connected with full-duplex network and wireless (WIFI) where needed. These all additional equipments are necessary to support the current and future need of Good Health data warehousing.
EDW implementation will be divided into 6 stages to simplify the monitoring and measuring its achievements. For the first stage, this document will explain:
- Good Health reasons and objectives for EDW initiatives
- Deliver the costs and benefits within the context of its scope, infrastructure and development requirements, necessary operational and cultural changes and business outcomes.
- Describes the overall approach to the project schedule, structure and governance
- Brings up issues and risks to be addressed and proposed improvement strategies to be put in place to deliver its full potential
- Suggests additional releases of EDW to continue Good Health to become a leading health solutions retailer
At this first release, EDW has been identified with the following principles:
- EDW will provide an early stream of strong financial business outcomes to meet the implementation cost within 1 year from its implementation.
- EDW outcomes can be achieved using data already existing at Good Health, making it independent of other projects and their schedules.
- EDW will provide significant contribution to meet the cost of the future implementation of data warehousing (EDW).
- EDW will strategically contribute to the long term EDW information and knowledge management direction by demonstrating capabilities of the EDW solution proposed.
- EDW will adhere to the Good Health EDW and CRM Strategy and Architecture in terms of development standards and business usage.
EDW will provide sufficient reporting and analytical capabilities and information to retail to enable sufficiently detailed and timely analysis of provider services, performance and claim history. This will allow Good Health to negotiate better contractual terms with providers while ensuring the best service to members.
The associated impact on Good Health operating costs has been estimated as:
· $12-18M pa reduction in hospital benefit outlays (total revenue of $1.2B pa) within 12-18 months (committed)
· up to $28.5M pa saving through error reduction (uncommitted)
· up to $1M pa saving in prosthesis benefits outlays (uncommitted)
The approximate total cost of implementing EDW $20M.
Other EDW implementation stages which are recommended
· Release 2: Fraud and Overpayment Reduction
è Provide sufficient claims, membership and financial (SAP) information in the EDW, and reporting / analytical capabilities to enable more effective Risk Management. These can be done by its ability to data mine the EDW to identify investigation targets and ability to change provider behaviour by automating alert / request for information processes generating letters to providers without conducting intensive individual provider investigation.
·
Release 3: Retail Centres Performance
Management
è Provide information from existing systems and the new EDW and Property Management solutions to enable simple and efficient calculation of Retail Centres Score Card. This will give Good Health the capability to reducing the effort to prepare monthly Retail Centres Score Card (saving of $72,000 pa). It also adds the ability to access information online and easy to use tools will reduce the need in business analysts (saving of up to $900,000 for 3 regions x 3 analysts x $100,000 pa).
·
Release 4: Integrate EDW and CRM Data Mart
è In relation to the first stage of EDW, it is accepted that the initial implementation of ERM Data Mart will not be in line with the overall EDW architecture. Data is going to be sourced from a variety of systems into the CRM Data Mart directly. In line with the Good Health EDW architecture, data should be loaded into the EDW first and then if necessary copied into Data Marts.
·
Release 5: Actuarial Analysis and Reporting
è This release should cater for Actuarial requirements to complement those already met by the first implementation through the CRM Data Mart.
·
Release 6: Financial Reporting
è This release will combine in the EDW financial (SAP) data with Contributions, Payments, Membership and Product information, together with necessary extraction, reporting and analytical tools to enable effective and efficient management reporting. Benefit of this release include timely, reliable and trusted financial reports produced from a single source using set reporting rules, with results not open to interpretation.
Stakeholder review and approval
The EDW Project stakeholders have been interviewed during the development of the project plan and have reviewed the document and acknowledge that their interest have been appropriately addressed in this document. Relevant project managers have also been informed.
Figure 1 shows that the stakeholders are agree with the recommendation, approve the project and guarantee the resources necessary for its implementation.
|
MP Level |
Authority |
Review |
Signature |
Date |
|
Project Owner |
Knowledge and Information manager |
Review |
|
|
|
Corporate Program Office |
CPO Director |
Review |
|
|
|
Health Solutions |
Director, CARE Project |
Review |
|
|
|
Health Solutions |
Manager, Distribution Network |
Review |
|
|
|
Retail |
Manager, Provider Services |
Review |
|
|
|
Retail |
Manager, Risk Management |
Review |
|
|
|
Finance and Corporate Services |
Manager, Actuarial |
Review |
|
|
|
Finance and Corporate Services |
National Audit Manager |
Review |
|
|
|
Financer and Corporate Services |
Manager, Business Performance and Analysis |
Review |
|
|
Figure 1
Project Objectives
The main reason for implementing EDW is to integrate all business system into a central location (integrating all application data into one single repository) so that Good Health can analyse further, planning future business strategies (strategy to improve internal performance and satisfy customer needs by identifying their future needs in advance). Good Health wants to integrate all its data so that it can identify its new market, new opportunities and to improve its service level. Good Health does also want to have a better negotiation power with its service providers, which can be achieved by analysing the data it has in its database. For this, Good Health needs to analyse its entire data. This data was distributed in different systems, such as its legacy system or the data from external sources therefore they have to be brought into a single database/repository. For this, Good Health needs to prepare a repository where it can have all their data and access and analyse from one place. That is why the EDW is required. So that data is stored in single platform and in the same level (transactional level) and it can be analysed for various purposes (marketing, customer service and market analysis).
Good Health does actually have existing warehouse built 8 – 9 years ago as the base; however this new EDW has come up as an independent data warehouse as part of overall new business strategy to compete with other providers. To compete with other businesses Good Health needs to have a better internal system so that it can analyse, compare (what its competitors are doing), how its service level is, customer service analysis, analyse whether the business is losing or getting new customers, negotiating with their service providers and explore new market (where Good Health should open new retail centres) as well as calculating revenues. To understand all those components, Good Health needs to have past data (customer data, product data and all market data) together and being analysed. This is the main initiative of EDW: bringing all business data to a common place is where Good Health has to start.
The problems with the existing system are accessibility and availability of data. Without a single repository / data warehouse, it has always been difficult to access entire data from various system (some of the data can be located in SAP or mainframe), and some times from external sources. To access all this data and to analyse them which came from different systems and different formats was the problem and Good Health would not be able to analyse to identify the customer’s needs and make any progress. Good Health has had to bring all these data together into one location and then analyse them, which has always been a challenge. This new approach will provide a better way of data accessibility and availability.
Good Health does
have choice not to implement the project but it is compromising its longevity
business in the same time. Good Health can still use its existing system, but
that will not give entire picture required and it will not gather data analysis
it wants. This decision will show how serious Good Health wants to improve its
business performance. Without implementing this project, business will still be
running but without improvement and if Good Health wants to compete better in
the market it needs to have this kind of system otherwise it might lose its
customers. The health cover business is very competitive in
The project team has done the cost benefit analysis. The immediate return on the investment is better look of the analysis. But a significant monetary value increase is expected in 5 to 6 years after the data warehouse has been implemented properly according to the Good Health needs. That means recognising the needs of the organisation and had the data mining set up properly. As the cost of the project is huge, any mishap could lead to disaster, careful calculations have to be done.
Figure 2 demonstrates how major project activities will contribute to Good Health attaining targeted business outcomes. Both committed and uncommitted outcomes are shown.
Figure 2
Data Representation and Data Model
Good Health will be using data warehousing data model in IN3NF (Normalised ER model). Some of data marts will be designed with this model Hybrid OLAP (share data marts) and it represent that in multidimensional model. Good Health will be able to gather data in different forms (VSAM data, COBOL data, relational data, data from flat / text file and also data from web log file).
Data can be classified in different ways
- Business Data
- Application Data
- Transaction Data
Generally as in retail business system Good Health will have various kinds of data (all above). Good Health will have to categorise data elements and identify for its business. Good Health will have to identify those data elements from business application and some of them can be acquired from third party data. Based on those, Good Health will create data model.
Project Stability
System will be tested thoroughly within various levels (3 or 4 level of testing). Any possible problems would normally arise during these testing stages and will be rectified immediately to avoid any problem in life environment. All the non-defects problem will be fixed as well. Once the system goes to the life environment, business would not normally found these sorts of problems. There may be some problem such as network failure, system failure. As most of the systems (server, network, workstation and infrastructures) will be thoroughly prepared before being brought to the life, they are more likely being stable.
There will always be minor problems during the implementation now and then as there is no such perfect system and Good Health as any other business are unique business, which requires software to be fully customised. In overall, Good Health may expect a pretty much stable system. Good Health may be confronted initial hiccups in the early stages of project preparation; however this will also be cleared immediately.
Benefits
The implementation will certainly add value to the organisation. Directly not increasing the value of the organisation straight away, but after the project has implemented the organisation will be able to see increment in their customer numbers or membership numbers, which indirectly will be adding value to the organisation. That means the organisation has better positioned itself in the market to compete with. Generally the organisation will be placed in a better shape than before as many numbers of factors that will affect has been dealt with carefully. The organisation has certainly been positive before and about implementing data warehouse in many issues such as customer satisfaction, better approach to the customers. The organisation is confident in up selling their products, which will add value to the organisation. And the main value to the organisation, which will be added, is customer satisfaction. This will definitely add value to this organisation according to their marketing division.
The main division, which will see significant improvement in the value, is finance. As many numbers of facts, which determine the organisations value has been looked into at the designing stage and dealt with. The revenue will be slightly up in the beginning, which can be seen straight away. The analysis time and turn around time will be significantly cut down which will add to the department’s value. The other department, which will have considerable added in value, is marketing. As the number of customers will be increased, this will be as the improvement of the customer service will be noticed immediately. As the organisation will get the access for better analyses of the data they have been able to concentrate on the customers’ needs and behaviours, which will give better customer satisfaction. This will improve the number of customers by a small margin, which in turn will have a significant effect on the finance department. According to the marketing division they are confident of improving sales as they have better inside look of the customer’s behaviour. As they cannot put direct value into the area of customer’s satisfaction, but they say this is the main point to get the business improve.
Tangible benefits
As the goal of any organisation will be revenue, this organisation is no different to others. Improved revenues can be identified very soon, as the customer needs have been identified and dealt with. As the customer satisfaction has been improved the number of customers has been improved. As the number of customers has been improved, profit will be improved and market growth can be seen. The organisations value has been changed as a result of all these factors. All the above factors are interlinked. This could even see a boom in customer numbers. As a result of this organisations profit will be improved.
Intangible benefits
The customer service will be improved drastically (by over 50%) according to the organisation customer service and marketing departments. As the customer service staff will get better picture of the customers needs they will be able to up sell their products. As the customer service gets improved the customer satisfaction will be improved. This will be a dream in achieving for any organisation, which will have a direct impact on the business. As the required time for accessing, processing, and analysing will be reduced significantly which means saving lot of time, which cannot be mentioned in terms of money. But for the business point of view reduced time means more customers can be served at less time and number of customers that a customer service will be serving has been increased. Operating costs will be reduced as a result of this (maintaining different systems). The organisation will have better negotiating power with their service providers, which will save significant amount of money as well.
Project feasibility
The original cost of the project has been estimated at 20 million dollars, and if the project gets delayed for unforeseen reasons the cost will be blown up by 50% for every year, which will be 30 million dollars. The original time allocated for this project was only 2 years in the beginning. But taking into account any unforeseen reasons the project has the chance of prolonging for another year, which will have the cost gone up by 50%. So steps for careful planning and monitoring needs to be taken. The ongoing expenses for this project will be over a million dollars a year to maintain the system and technical staff as well. Most of the cost is towards the technical team and this will be outsourced to maintain for the initial years into the project, till the in house team gets full training.
The project will be handled by many external service providers (such as IBM, DMR and People soft). As there are many service providers careful monitoring will be taken. Apart from time the project cost will be blown away by 10 million dollars for every year late monetary value. This will mainly because there will not be enough coordination between the external service providers as they want to compete with each other. Every one of them will try to blame other service provider in case of any hiccups. As the project has not been implemented it seems it is too early to predict whether it is successful or not fully. The success factor can only be judged once the project has been implemented. As far as the project implementation was considered the organisation is very confident that it will be successful. At this stage it is very difficult to have a concrete opinion on feasibility, as the project has not been implemented. They need more time to study feasibility.
Cost benefit analysis
The organisation has done the cost benefit analysis. Yes they have conducted a study for this project regarding the needs for going ahead with this project and the ROI. The organisation will outsource the project of cost benefit analysis for external organisation. Their main goal is to compete and see how their competitors are placed in the market with the implementation of EDW and the benefits that can be achieved.
Figure 3 shows the actual and accumulated cash flow, demonstrating that this first release of EDW will pay for itself within the first seven months.
|
Cost / Benefits Items, $M |
Cost (-) and Benefits (+) in $M |
|||||||||||
|
Year 1 |
Year 2 |
Year 3 |
||||||||||
|
Q1 |
Q2 |
Q3 |
Q4 |
Q1 |
Q2 |
Q3 |
Q4 |
Q1 |
Q2 |
Q3 |
Q4 |
|
|
Cost |
|
|
|
|
|
|
|
|
|
|
|
|
|
EDW Infrastructure (h/w
and s/w) |
-0.50 |
|
|
|
|
|
|
|
|
|
|
|
|
Populating EDW with data |
|
-0.50 |
|
|
|
|
|
|
|
|
|
|
|
User training (8 users at
10 days per user) |
|
|
-0.04 |
|
|
|
|
|
|
|
|
|
|
EDW administration and
maintenance |
|
|
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
-0.10 |
|
Benefits |
|
|
|
|
|
|
|
|
|
|
|
|
|
Decreased payments to
contracted hospitals |
|
|
0.00 |
0.50 |
1.00 |
1.50 |
2.00 |
2.50 |
3.00 |
3.00 |
3.00 |
3.00 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Cashflow |
-0.50 |
-0.50 |
-0.14 |
0.40 |
0.90 |
1.40 |
1.90 |
2.40 |
2.90 |
2.90 |
2.90 |
2.90 |
|
Accumulated cashflow |
-0.50 |
-1.00 |
-1.14 |
-0.74 |
0.16 |
1.56 |
3.46 |
5.86 |
8.76 |
11.66 |
14.56 |
17.46 |
Figure 3
ROI (Return on Investment) factor with this kind of system basically is calculated for a certain period of time (4-5 years). Good Health would not be able to get instant return instead ROI factors are spread over a number of coming years (long term benefits). Very hard to calculate and generalise these points as they are depending on how efficient Good Health build its analytical model, how the market is responding and Good Health financial condition.
Project Management
The organisations in house project team in coordination with external service providers will manage this project. Different external providers in case of implementation will be dealing the organisations project. IBM supports the in house team in server technologies and networking part. This means IBM will install the server and maintain them for certain number of years. At the same time IBM will train the in house technical team on the server issues. As the front end is going to be People Soft and the end users will be given extensive training by the PeopleSoft team on how to use and navigate. Once the training has been finished the PeopleSoft has got very little to do in the organisation, until some sort of problems arises. The data conversions and transferring will be done through DMR. They will have to work on this project for certain no of years and train in house team in this area. Once the training has been finished and the in house team has got enough confidence DMR will totally move out of this project as well. The organisation has got a change team in preparing the employees and guiding them through the changes. For some time it has outsourced change management side. As far as the maintenance is considered the in house team will take care of it.
Post implementation issues
As in every implementation we can expect problems after implementing any new system, the most common problem the organisation can encounter will be is with LAN. There will be regular networking problems after the implementation and some server problems arise as well. One of the area needs special consideration is performance issue. Though it has been designed with 14 CPUs server the performance will be slow if special care hasn’t been taken. The users will have the impression that will never retrieve the data. It will significantly reduce the performance of the system. As the end users will have significant problems with accessing data, that is as the system is slow, they will have bad experience with the customers as well. The customers will not be happy for waiting so long on the phone, which would have a devastating effect on the business. Fine-tuning the performance will rectify for some degree; however the server should be multithreaded as well, where the problem will be fixed. The main issue they will have to tackle apart from the above mentions issues will be more related to the data warehouse it self. The organisation currently has got a mainframe (OS390) on UNIX platform. For the data warehouse we have to replicate the database of the mainframe. The organisation was doing payment run once a month for the service providers. As the payment run was done over night and all the data needs to be updated in the data warehouse overnight for consistency and having data for analysis. The EAI, if it is not carefully designed, will do transactional processing instead of batch processing. The EAI (Enterprise Application Integration) tools that will have for integrating the data from mainframe to data warehouse are MQ series. By using this method, data will not be transferred in batch process; as a result the window time limit will be exceeded. Which means the data is transferring a record at a time. As a result the data will not be ready for the next day business and the end users will face problems with incorrect data in regards payments. The technical team has come up with a solution in case if this problem occurs. Then the data should be avoided passing through the EAI. For rectifying this problem, the technical team has a suggestion for using ETL (Informatics) tool to send the data straight to data warehouse without going through EAI. By doing this we can manage to send the data as a batch file, instead of transaction by transaction. Now by the time the end users come to work next day all the data, which is in their legacy system, is readily available in the data warehouse as well. This will be a main issue with the system if not dealt with properly. The technical support team from external service providers have got contingency plan in place, for unexpected hiccups with implementation.
Paripurna Somavarapu, Lukman Susanto, Michael Thayaseelaan