Data Warehouse: Evaluation and Implementation
Guest author Naveen Jain is the founder and president of CULytics. He's a credit union leader experienced in actionable strategic planning, data analytics, marketing and innovation that deliver immediate ROI at multi-billion dollar institutions. He can be reached at email@example.com.
Data is so important now; we can use it to predict what to do in the business. For making predictions, we collect data in the data warehouse. The warehouse is used to collect and analyze business data from different sources. It is the core of the Business Intelligence System that is used for analyzing data and reporting. A data warehouse is a blend of technologies and components that allow the strategic use of data. It is like the electronic storage of a large amount of data that is designed for query and analysis.
Financial institutions including credit unions are investing heavily in big data and analytics. One of the terms closely connected to big data and analytics is data warehouse. But deciding whether to build or buy a data warehouse is important. It is a powerful tool in the enterprise data management strategy.
To help us understand it in-depth, Bob Little (Advisor, CULytics) and I hosted a workshop. We addressed why we should have a data warehouse, what are the goals, how to implement it, and how to select vendors.
The Need for a Data Warehouse
When we do some analysis, most of the time goes for gathering data from the system. With the help of data warehousing, the time required for data gathering got compressed. Data is available from a central repository, so analysts do not need to spend time on data collection. This increases efficiency. It is one benefit of adopting data warehousing. Keeping a historical trail of data for a business perspective is another benefit of a data warehouse.
In core banking, some applications don’t have data about the change in attributes and elements. With a data warehouse, it has become very easy to maintain such records that are not possible to maintain in the operational system. And, these historical records can be used for certain analysis. Adopting a data warehouse:
Provides ease of data collection
Brings trust in the system as records are available
Improves data quality and consistency
Unburdens the IT department
Helps in risk management as access to more data helps in making better and known decisions
Data Analytics Maturity Models for Credit Unions
How to get the value from a business perspective is important. As at the end of the day, if the business can see the value and drive it, then we are successful. When we talk about data warehouse or data analytics strategy, we understand what is important for the business and then we can go down the path for implementing any technology in place. In the overall process of evaluating data warehouse and business intelligence solutions, we need to first look internally and understand the things that are important for the business users so that you can make an investment that can return value in less time. It depends upon different dimensions like the people, process, and technology that are being implemented to do data analytics. There are four models:
Ad Hoc: There are fragmented analytics point solutions. Analytics is generally reactive and descriptive of what has happened in the past.
Basic: A data warehouse has been implemented with integrated reporting and dashboards. Analytics is diagnostic and helps determine why things happen.
Managed: There are automated reporting and alerts. There is higher organizational trust in the data. Analytics is beginning to be predictive of what will happen.
Optimized: Analytics has been integrated with business processes. Analytics is prescriptive for what should be done in the business going forward.
Data Warehouse Architecture
There are many data warehouse models available, but they work similarly. An ideal data warehouse architecture is comprised of:
Data sources: Source of data could be an operational system, external sources, flat files, etc.
Staging Area: Where things are collected together and transformation including normalizing, formatting, etc., happens. ETL
Warehouse: Keeps data after cleansing in the form of Summary Data, Meta Data, and Raw Data.
Data Marts: It contains a subset of data available in the warehouse. Comprised of Branches, Loans, and Operations, for example.
Users: Analytics, Reporting, and Mining of data happen at this stage.
An ideal data warehouse should have basic capabilities: provisioning, ingestion, scalability, performance, provenance, integration, administration, and security. These are several models that can be used. Also, we can build something on our own. When we do this, we get to know which ETL technology we are going to use. Which technology for staging server – be it SQL, Hadoop, etc. Similarly, for analytics, reporting, and mining, different technologies can be used. So, when we build our model, it is a very resource-intensive process not just for building it but also for managing it on day-to-day basis.
Data warehouse infrastructure supports a data warehousing environment in combination with technologies. In other words, it’s a repository of all sorts of data the implementing organization would need in the present and future. But in reality, the features and functions of the data warehouse may vary depending upon the needs of the organization. Organizations have to consider a few parameters before implementing the data warehouse. Try to find out the model requirements: on-premise, cloud, or hybrid. Also, work on tools that are required to improve performance and reduce the cost. System availability is crucial, so make sure that it is available for completing the task in time. The total cost of ownership, training, and installation space among others also require attention.
Before looking for the vendor solutions, discover the priorities, success metrics, challenges, and opportunities from an organization and business function perspective. Different data warehouse solutions operate differently. Some solutions are with end-to-end integration with data warehouse and business intelligence. Some solutions are in the cloud or on-premise. Each of these has its own set of complexities.
Data Warehouse Implementation
When it comes to a data warehouse implementation, a team of Data Architect, Technical Architect, Database Administrator, Data Integration Developer, Report/ Dashboard Writer, Data Scientist, and Data Analyst is required. It is not mandatory to have all these in house; the operations done by these professionals can be outsourced as a part of managed services. For implementation, resources, timeline, and training are crucial. Which resources does your credit union need to deploy? What will be the contribution from your vendor? Will there be a need for third-party vendors? Work on these to get an estimate of cost and the amount of collaboration required. Keep in mind that all the resources are not required in hand on the first day. As we move forward, based on the requirements, these resources will be required for getting the value from the data that we have. About timeline, evaluate how long it will take for vendor evaluation, when the implementation begins and end. Training is the most important part; as is knowing who will need training. Deciding whether it should be online or in-person can help you perform better and get optimum outcomes.
The key to success is vision and strategy, so make sure you have it. Implementation, integration, and other operations can be outsourced.
Data Warehouse and Business Intelligence
When we talk about the data warehouse, it becomes important to introduce the BI system. Without a data warehouse, the BI system can’t exist and without BI system, we don’t need a data warehouse. These things work closely. Data Warehouse is useful for storing data from multiple resources, whereas Business Intelligence helps in generating business insights. Data Warehouse gives unified data for upstream BI applications; and Data Visualization, Dashboard Creation, & Reporting are done through Business Intelligence. Data Engineers and Back-end Developers are the audiences of Data Warehouse and the audience of business intelligence are Executives, Managers, & Data Analysts.
Some vendors offer solutions for Data Warehouse and Business Intelligence together and individually as per the requirements.
Credit unions don’t have that many resources; that is where solution providers come into the picture. Some of them will provide end-to-end solutions. When you go out for vendor selection, in addition to features and functionalities, look for:
Pricing: Understanding the requirements can help in understanding which pricing model suits the best.
Referenceability: Don’t forget to analyze the reviews posted by different types of customers before finalizing the vendor.
Ecosystem: Make sure that the vendor can integrate with applications and datasets in less time and in a cost-effective manner.
Product Roadmap: Look for upcoming features and release cycles.
To start with the data warehouse, try to know the requirements (how to use data, how to drive growth, how to do segmentation) of the business as these are different for different organizations. To be successful in data warehouse implementation, focus on a few parameters, and make investments if required. These things are executive sponsorship, business engagement and alignment, planning, communication technical capabilities and skills, and developing good vendor participation.
Watch the full workshop around Evaluating Data Warehouse Solutions, here.