Data Warehouse

Departmental Data Warehouse Options

by

Are you a data chaser?  Are you spending hours of your time grabbing data from various data sources, throwing the data in Excel, massaging the data to clean it up and correlate location or client numbers so you can see the total picture and finally analyze the data?  Do you love data chasing?  I didn’t think so.

There are solutions you can implement that will allow you to focus on analyzing your information for better decision making instead of data chasing.  Your options include:

  • Enterprise Data Warehouse (EDW)
  • Data Mart
  • Data Aggregation Cube
  • Continue Data Chasing and working harder not smarter

Let’s take a moment to explore your options:

Enterprise Data Warehouse

An EDW is typically owned and managed by IT.  The EDW is a staging area where data from multiple sources has been brought together and cleansed to make sure all data has integrity.  A properly built EDW will be a collection of Data Marts that have been well planned to work together to seamlessly tie the information from multiple sources together for the benefit of the business.

Data Mart

A Data Mart is a purposely built repository that typically serves a specific business area.  Often, clients will have a number of separate data marts that were built in silos, but it serves the particular business unit (like a financial data mart).

Data Aggregation Cube

For clients that do not have IT resources and their Finance departments aren’t knowledgeable in building Data Marts, they may decide to leverage tools to build a Data Aggregation Cube in a solution that uses cube technology like IBM Cognos TM1.  A TM1 cube is like an excel pivot table on steroids.   A TM1 cube can have up to 256 dimensions and handle large amounts of data.  A TM1 cube leverages the Turbo Integrator to pull both structure (like the Chart of Accounts) and data from source systems.  This allows Finance to build a staging cube that can aggregate data from multiple sources so the data can be leveraged for reporting, budgeting, forecasting and what if scenarios.

Advantages of an EDW

A properly built EDW will provide rapid access to data, providing one version of the truth, and can adjust to meet the changing business needs.

Disadvantages of an EDW

Building an EDW can be costly and time consuming.  If IT is responsible for the EDW, they must work closely with the business to understand the requirements or the result will not be successful.  To get funding for an EDW, you must be able to clearly articulate the business use and ROI.  To be successful, the business must communicate their requirements and uses and partner with IT.  The EDW must be designed and built to maximize performance and be flexible enough to meet the changing needs of the business.

Advantages of a Data Mart

A data mart can be a stepping-stone to realizing the benefits of an EDW; however, the various marts must be built considering the needs and the data of other data marts.  A proper plan for integrating multiple marts together must be established early on in the development phase, with constant communications between the builders of the various marts.  Data Marts can be built fairly rapidly, allowing the business to achieve quick results.

Disadvantages of a Data Mart

A data mart will typically still require IT resources to build the mart.  If not designed considering additional marts, an organization can end up with too many copies of the data, as multiple marts could contain the same data.  There is a risk of multiple data marts displaying different results for the same data if updated at different times.

Advantages of a Data Aggregation Cube

A data aggregation cube built in a cube-based technology like IBM Cognos can empower Finance to maintain their own cleansed copy of the data and leverage it within Finance.  They can update the data with ODBC connections to the data sources and have the flexibility to work with the data in a multi-dimensional structure for better analysis and what if modeling.  Data aggregation cubes can be built rapidly to meet the immediate needs of Finance.  Typically, data aggregation cubes are significantly less expensive to build than a Data Mart or EDW.

Disadvantages of a Data Aggregation Cube

Data aggregation cubes are not as scalable as a data mart or data warehouse to meet the needs of the business beyond the Finance organization.   The cubes will need to be maintained by Finance and typically do not contain as much drillable information as a mart or EDW.

So depending on your resources’ haves, needs and pains, you have a number of options that can help you escape the data chasing world often seen in Finance departments.  So, isn’t it time to get your life back and enjoy analyzing data instead of chasing it?  If so, Lodestar Solutions would be happy to discuss your goals and options to help create a plan to realize the benefits of better data, leading to better decision making.

If you would like to learn more, contact Lodestar Solutions 813-254-2040.

One Version of the Truth is Not Attainable

by

One Version of the Truth is Not Attainable

I recently met with a financial executive of a large distribution company and he expressed to me his frustration.  The company had invested in a Data Warehouse and deployed a leading BI solution and yet, they still had issues with “one version of the truth” (OVOT).  They spent significant hours trying to validate which numbers were correct.

With a slight smile I said, “One Version of the Truth is Not Attainable.”  He looked at me in shock, because many highly paid consultants had told him he just needed to invest in a data warehouse and master data management (MDM) and then put a BI tool on top.  I continued to explain that for most organizations; especially those growing through acquisitions, one version of the truth would not be a reality.  However, there are definite steps and considerations that can get you closer to “One Version of the Truth”.

1. Leadership needs to come from the Business Executives. Guiding your organization to realize the benefits of OVOT requires strict discipline. The Executive Sponsor needs to plan out their strategy for implementing the solutions, while looking at both the short term and long term goals.
a. Putting the right resource in place. Including knowledgeable subject matter experts in the core team may require pulling them away from their regular jobs and having others backfill, but the benefits will be tremendous.
b. Providing appropriate funding. By setting short and long term goals, you can prove incremental ROI’s and justify future expenditures.
 c. Setting priorities. The leadership team needs to set the priorities and communicate them to the entire organization.  This should include establishing incremental steps to measure the ROI along the way.  Often times, other organizational priorities will be in conflict with the OVOT initiative, so executives must set the priorities.
d. Identifying and communicating risks of bad data. The business is really the owner and creator of data.  IT protects it.  The executives need to communicate that.  To be successful, the business must take ownership and understand the risks of bad or incomplete data.  When this message comes from the top, people listen.

2. Master Data Management (MDM) is not an IT project.  The business must be involved in MDM or cleaning and managing the data.  We recommend that someone with in-depth knowledge of the business be assigned to the team, as they can understand and articulate the impact of poor data quality on the business.
3. Strict change management. Once the data warehouse and MDM foundation have been established, strict change management rules must be established and enforced.  Various subsystems and business units should not be operating in silos where their changes can impact other areas of the business.
4. Silos of data in an acquisition. The reality is that if you are in acquisition mode, you will always have data sources that are not fully migrated to the corporate structures and systems.  You must plan to have short term and long term solutions to deliver reporting and analysis that includes data that has not come on board. I refer to this as the MacGyver approach, as you have to make it work with what you have on hand.
5. Eliminate “duct tape and super glue” reporting. Many talented BI report writers have been instructed to just make the reports work, so despite challenges with the data, they write queries and code into the reports to make them work.  I call this the “duct tape and super glue” method.  The report writers need to work with and communicate the data challenges to the data team so they can correct issues at the source. This issue is magnified as the organization pushes out self-service to the end users.  Duct tape and super glue will not withstand the pounding of end users.

If you would like to learn more about how to navigate data and realize an increased ROI on your Business Intelligence investment, contact Lodestar Solutions about our new Executive Coaching for Business Analytics at 813-254-2040 or email us at Sales@LodestarSolutions.com.

When to Use Cognos Event Studio

by

IBM Cognos Event Studio is often an underutilized Studio but has its place. I think it would be better to explain how you can use this Studio in your everyday duties instead of the traditional white paper approach.  I will use a couple of examples that I think highlight how a business group (departments) can use Event Studio and how a database modeler can use Event Studio.

Scenario A – Process Support

• Sales Order Table holds sales order detail and is important to the production management process. When a sales order is created, a sales order number is automatically created but an order category must be manually entered. The order category is needed by production to prioritize inbound orders. The company cannot change their ERP because of cost and time, so customer service is responsible to enter the category.

The production department expects a report with all orders categorized.  If the orders are not categorized, they might get missed. Clearly we don’t want to risk missing an order, so how do we ensure all orders are categorized?

We create an event that looks at all open orders and find any which do not have a category. When we find one, we send an email to customer service with the order number so they can correct the order. We can schedule the event to run every five minutes or as often as required to make sure the production report is correct and no order is missed.

Scenario B – Data Warehouse

• You have a data warehouse which you use for reporting. Before your data is brought into the warehouse, it is staged for cleaning (transformation). Data that doesn’t pass certain business rules and will require human review are placed in a quarantined area within staging. The clean data is then loaded into the warehouse.  This means there will be a difference between the warehouse and the data source.

To settle the discrepancy, we can create an event that monitors the quarantined area to send an email with an attached report.  This report shows all of the records which did not make it into the warehouse along with the reason why.

This will allow the business to review and correct the data without having to ask IT why it data did not make it into the warehouse.

Cognos Event Studio can help you maintain clean data. Give it a try it might be just what your company needs! Feel free to contact us at Sales@LodestarSolutions.com for more information.

X