The need to document an increasingly complex data warehouse implementation led Omaha Public Power District's IT crew to purchase a new set of graphical data modeling tools -- but winning
The public utility -- which provides electricity in much of Nebraska -- decided to purchase ER/Studio XE data modeling software from Embarcadero Technologies Inc., said Bill Harrison, a business intelligence (BI) and data warehouse architect with the company. Harrison said he managed to gain approval for the purchase by ignoring the technical aspects of the situation, focusing on the bottom line, and explaining the consequences of inaction.
"Times are really tough and it's hard to get anything approved," Harrison said. "So, I decided to sell it using a marketing approach and not a technical approach."
Having resolved to leave most of the tech talk out of the conversation, Harrison went to Omaha Power's higher-ups prepared to answer several questions. They included: Why do we need this tool? How is it going to benefit the company? What is the payback? How long will it take to realize value? What happens if we fail to act?
Data models -- which show how data sources and database tables relate to each other and the data warehouse -- were partially documented at Omaha Power in the form of Microsoft Excel spreadsheets. But those documents were difficult for the average business worker to understand. Harrison made it clear to company brass that the state of affairs would eventually lead to costly mistakes.
Harrison explained that business users at the electric company -- who increasingly create their own business intelligence (BI) reports without the help of IT -- needed user-friendly data modeling documentation to help them access the proper information, return the correct results and make the right decisions.
"Various people had tried to push this through as a technical project and I said, ‘That's not going to work. We need to market this,’" he said.
Data modeling software produces results quickly
Omaha Power's IT shop is focused largely on Oracle PeopleSoft Financials and General Ledger, as well as a robust information system that handles data related to its 300,000-plus commercial and residential customers. Harrison said all of the information being continually generated in those applications is eventually cleansed, de-duplicated and fed into a large data warehouse for BI and analytics reporting purposes. The company runs IBM Cognos for BI reporting.
"The customer information system has 15,000 tables and views," Harrison said. "How's that for a lot?"
Omaha Power has only been running Embarcadero software for a few months, but Harrison said he's already pleased with the results. The organization has begun using ER/Studio to generate some of that much-needed data warehouse documentation, and Harrison expects the tool will soon be used to create new data models as additional applications come online.
Harrison said ER/Studio displays data models in two ways. The first is a standard reporting format that basically uses text to describe the relationships between data sources. Users can also access an entity relationship diagram (ERD) feature, which displays a colorful graphical representation of the data sources and how they relate to one another
"For example, the ERD shows the architecture of our budgeting data mart. It shows the tables and how they're tied together," Harrison said. "You can either look at it directly from the workspace or you can print it out on a big sheet of paper."
The electric company has also used ER/Studio to "reverse engineer" data marts that stem from its data warehouse. Harrison's and his team created sub-models of the data marts that are designed to be easier for users to understand. Some other data modeling tools in the market include CA ERwin, Oracle SQL Developer Data Modeler and Toad Data Modeler from Quest Software.
Standing on the brink of a 'big data' explosion
One of the big challenges Omaha Power is experiencing today is the unprecedented rise in the amount of data being created on a daily basis -- the so called 'big data' explosion. With data volumes increasing in terms of size and speed, Harrison says properly documenting data models and overall data warehouse architectures is becoming more important than ever.
"The big change that's happening is we're facing big data -- huge data files of data -- and it's just beginning," Harrison said. "We're talking hundreds of terabytes and maybe even petabytes of data as we get into SmartGrid and some other technologies that will really force us to analyze huge volumes of data that we've never even seen before."
SmartGrid and similar technology programs allow utilities to keep track of highly granular information about consumers, such as how much energy coffee makers or other appliances consume during a given timeframe.
"We need new tools and techniques to look at that kind of data because number one, it's unstructured, and number two, it's huge," Harrison said. "You may not end up documenting every single item, but you should at least have a data model that describes your overall architecture."