In the first of a two-part series, Baya Pavliashvili, database administration expert, offers solutions to your business problem using a data warehouse.Like this article? We recommend
Data warehousing has been around for decades. Yet, many businesspeople and quite a few technical folks don’t know what it takes to build a warehouse. Most people think that a warehouse is a data store that contains all data within the enterprise, is built within a couple of weeks, and thousands of people can use it for years to come without any additional effort or expense. Unfortunately, this view is incorrect.
In this series of articles, I give you a very simplistic example scenario and show you how you can go about resolving a business problem using a data warehouse. I also describe the efforts involved in building a warehouse for technical as well as non-technical individuals.
The first of this two-part article gives you an overview of steps involved in building a data warehouse and introduces the example scenario. It also teaches you how to create and populate a dimensional model. The second article goes into detail about Analysis Services, MDX, and analytical views that are generated from the data warehouse.
Data Warehouse Lifecycle
A Data Warehouse (DW) lifecycle can be summarized as follows:
- Determine the reports that DW is supposed to support.
- Identify data sources.
- Extract data from their transactional sources.
- Populate the staging area with the data extracted from transactional sources.
- Build and populate a dimensional database.
- Build Extraction Transformation and Loading (ETL) routines to populate the dimensional database regularly.
- Build and populate Analysis Services cubes.
- Build reports and analytical views by:
- Using a third-party application.
- Creating a custom analytical application and writing Multi-Dimensional eXpressions (MDX) queries against cubes.
- Maintain the warehouse by adding/changing supported features and reports.
Reading through these points, you should be assured that:
- A DW is never "finished." It is an entity that keeps growing along with the organization’s reporting needs.
- There is no such thing as a free lunch. If you want additional reports to come out of the warehouse, you need to spend additional time (and money) to extend the warehouse.
- Notwithstanding, what I said thus far, you can have DW functioning and available for use with a limited set of features as you build it.
A major international satellite TV service provider came to CloverETL to solve a growing pain. Both the number of subscribers and choices of consumer packages were increasing, which made obtaining detailed marketing reports difficult. Manually created static reports weren’t reliable enough to base major marketing decision on, resulting in reduced operational activity. The need for stronger marketing insights called for a new business intelligence platform where detailed analysis could be distributed automatically to the employees through various channels.
|With this solution, our client can now undertake a more proactive approach to marketing by exploring in detail the impact of new initiatives, product packages, and subscription plans.|
- We developed a full workflow of ETL, data warehouse, and reporting systems to consolidate data
- The new business intelligence platform provides detailed analysis that is distributed to employees automatically
- Easy access to aggregate business data speeds up decision-making process
- Deeper insights enable more informed marketing decisions