Data Management – Beginnings
Every reporting project begins with the need for data from the applications presented to the users in the best format possible to enable business decisions. This can be an easy process or very complex depending upon where the data is coming from. Simple reporting can be accomplished when the data is in one system that is easy to understand and query. Complex reporting often requires mash-ups of data from different applications to create a comprehensive view of company data to enable visibility into business processes. The key is to understand how the data from different systems is related. Are their common keys or ways to map data from Source System A to Source System B. No matter what type of reporting you are performing, the key is to have a solid data management foundation.
Data Management – ETL
Getting the data from source systems to your reporting environment is the most critical part of your data management process. It is here that you will know that you have collected and moved all of the required data to enable efficient reporting. There are many tools available that perform ETL processing such as Microsoft’s SQL Server Integration Services (SSIS) or Informatica. Knowing what you are going to do with the data and how you are going to store it is the first step. Often times people design complex processes that are difficult to maintain rather than use simple steps that are easy to troubleshoot 6 months later when a change occurs. I always prefer the solution that is easier to maintain. Another point to consider is you do not always have to do a transformation when pulling data to your reporting system. Often times it is best to pull the data and perform the transformations on the reporting system on dedicated ETL server. You want to touch the source systems as much as necessary and as little as possible.
PowerPivot Excel 2013 offers the add-in of PowerPivot for self service Business Intelligence. To access these features you must activate the Power Pivot add-in.
- Filter data when importing.
- Rename tables and columns as you import data in PowerPivot.
- Manage the model and create relationships using drag and drop in the Diagram View.
- Apply formatting (to be used in Power View and PivotTable reports).
- Define your own calculated fields to use throughout a workbook.
- Define key performance indicators (KPIs) to use in PivotTables.
- Create user-defined hierarchies to use throughout a workbook.
- Define perspectives.
- Author your own calculations by writing advanced formulas using Data Analysis Expressions (DAX) language
For more information on what is new in PowerPivot for Excel 2013 click here
If you have never used PowerPivot before here are the steps to load it from Excel 2013.
The add-in is available in Microsoft Office Professional Plus. It’s built-into Excel 2013 but is not enabled.
- Go to File > Options > Add-Ins.
- In the Manage box, click COM Add-ins> Go.
- Check the Microsoft Office Power Pivot in Microsoft Excel 2013 box, and then click OK. If you have other versions of the Power Pivot add-in installed, those versions are also listed in the COM Add-ins list. Be sure to select the Power Pivot add-in for Excel 2013.
The ribbon now has a Power Pivot tab.
Now open the Power Pivot window
- Click Power Pivot.
- Click Manage.
What are you trying to accomplish in the Enterprise Architecture Process?
When approaching an Enterprise Architecture project there are many questions to ask to gather requirements. First review your projects stated goals and ensure you understand what the expected outcome is of the project. Which of the 4 architectures are you currently focusing on within the Enterprise Architecture process?
Questions to Ask during an Enterprise Architecture Interview
If you are new to Enterprise Architecture I have listed below sample questions that will steer in you in the right direction to better understand your business environment.
- What value does this process provide to the business?
- Who is involved in the workflow?
- What systems/applications are used as inputs, outputs and data flows?
- What happens when exceptions in the process occur?
- Is this process mission critical ?
- Are there known issues in the process that need to be addressed?
- Who is the process owner/stakeholder?
- What data is mission critical and needs to be as accurate as possible?
- Do you believe you existing data and reports?
Enterprise Architecture Vision
I have created a sample Enterprise Architecture Vision article that includes the best practices described in the TOGAF specification. The scenario for this example is a fictitious health care company currently looking to replace their legacy claim processing system to a new system that is quicker and less expensive to maintain for the quickly changing business requirements.
Enterprise Architecture Vision Sample
Problem Description – The current claim processing system is difficult to maintain and requires specialized skills to enhance. The COO and CEO have expressed concerns that the business will not be able to make the necessary modifications quickly enough to adopt to marketplace conditions. The current system requires full regression testing and is costly to deploy updates or enhancements. The following issues will be addressed in this project.
- Determine best option to replace legacy claim processing system
- The new system should integrate into the existing SOA architecture
- Decrease current average transaction completion time from 10 seconds to 2 seconds
- Create a modular system to decrease regression testing time
- Enable quicker modifications to the claim processing by leveraging a business rules engine
Data Architecture Program
The data architecture program within Enterprise Architecture will include many documents about the data processes within your organization. To begin this exercise you must determine what applications are in scope to explore and document. Then start with the data stores for the applications and document the physical data model. This should be the easiest to obtain giving today’s relational database tools. Once completed you need to determine what are the subject areas within the application and what tables exist to support the data for the subject area. To determine the range of valid values in each table I use an open source program such as R and have it explore the data. This is the easiest method I have found to see what is actually stored in each table. This will also enable you to see how dirty your data is in each table and work with business owners to document what fields are critical and should be part of a Data Quality Program. Finally you need to document what data flows into this application and what data this application sends to other applications.
Below are the items to investigate and document as part of the Data Architecture program.
- Physical Data Model
- Logical Data Model
- Subject Areas
- Business Entities
- Valid Value References
- Data Quality Rules
- Data Flow