I recently spoke to a friend of mine who works as a business intelligence consultant about writing an article for Information Management with a new twist on the operational data store which I call D’ORDS. After a moment of silence, he said, “A what?”  I said, “A D’ORDS. It’s a dimensional operational reporting data store.” Then he asked, in his very low gravelly voice, “You have my attention.” So I proceeded to describe a recent project I had completed for a very small startup company with a unique business model. 

Last year, I was contacted by a representative of this large company about my expertise on building data warehouses. For about 15 minutes, they described their business in general terms and their desire to create a data warehouse. I listened intently and took many mental notes. Taking a few minutes to gather my thoughts, I proceeded to describe several options. One option is to create an operational data store, which would alleviate the biggest problem of executing complex report queries against a transaction system. I proceeded to describe what they could do with this ODS and how they could subsequently create a real data warehouse as the main repository at a future date. We also discussed the transaction database issues, hardware configuration and their network. 

D’ORDS, the dimensional operational reporting data store, or was born out of a typical modeling effort first to establish a quick win solution and satisfy the clients’ immediate reporting requirement. The decision was to incorporate a Kimball approach with a little bit of Claudia Imhoff’s Corporate Information Factory strategy to design this business intelligence solution. In a flurry of activity over the course of about nine working days, different business process models were discussed, meetings were held, email sent and responded to and several conference calls scheduled. My client was understandably adamant about proceeding as quickly as possible with a delivery date of yesterday. As best I could, I explained that in order to birth a bouncing baby data warehouse and BI solution, there would need to be plenty of design sessions to conduct, define and map the enrollment process, perform data discovery and exploration of the data in their current transaction system, not to mention software evaluations and interviewing of prospective contractors before a deliverable date can be set.

The purpose of the ODS was to be a stepping stone, in accordance with the techniques of the CIF, followed by the development of an actual data warehouse. This final warehouse database was going to have a different design from the ODS. Instead of a dimensional database for reporting, the data warehouse would be constructed in the third normal form with some Boyce/Codd normalization in specific areas to satisfy the business requirement. It would be where the history was maintained. 

The initial creation of the ODS was constructed to relieve the reporting issues. After go-live, the data would be pared down to accommodate perhaps between four and six months of data. The full complement of data would then be maintained in the data warehouses’ third normal form structures. Therefore, the initial ODS design was created during the first phase of the project, then a third normal form structured data warehouse would be created as the final database structure. 

Several BI application tools were explored during the commercial off the shelf (COTS) search, however none offered the price-to-benefit ratio. Therefore, the client and I settled on one that was relatively inexpensive with a great maintenance plan, yet flexible enough to sit on top of a dimensional or third normal form database design. When combined with the dimensional ODS structures, the response time of data retrieval was subsecond. The corporate people, although they understood database structures, were ecstatic when they received their first report. The result set from the most complex report query returned hundreds of rows within a minute of execution against the database. The retrieval of data was so quick, the upper management staff initially thought the tool was broken. For most of the reports, the data appeared merely seconds after hitting the enter key.

Because enrollments and memberships are much a part of the business during the interviews of the business and design workshops, we quickly discovered there were four main phases to the enrollment process. At each particular phase, business metrics were identified and defined. As it turned out, the first phase had two distinct parts, so in all actuality there were five parts to the four phases of enrollments.

Verifications serve as measurements in usage at each kiosk in predictive analytics. This verification process was used to properly identify and to verify each person using the kiosk as a member of the program. The business metrics surrounding verifications needed to count the number of members using the system. Through this type of analysis, the kiosk manager and upper management could reasonably predict the optimum time for a kiosk to open for business and how many kiosks and personnel would be needed at each location: in essence, labor management.

In the dimensional world, a D’ORDS is born. There are two main parts of the business: enrollments and membership.

As noted earlier, there are five parts to the enrollment inside four distinct phases.

• Part 1: Consisted of collecting information about the potential member or enrollee.
• Part 2: Consisted of how this potential member or enrollee was to pay for the service.

Advertisement

Page 1 of 2.

1 2 next