Tod means Fox

Supporting decisions through sound data management

Posts Tagged Compliance

Data Warehousing ETL Checklist

Aug 27

Posted by Tod McKenna in Data Architecture

IT Business Edge writer Michael Stevens recently published a document called the “Data Warehousing ETL Checklist” (link to article with document). To get the file you’ll need to register with ITBusinessEdge (if you don’t already have an account).

The document isn’t a development checklist, but rather “a high-level checklist of the important topics”. Stevens has made a good effort to distill the various data integration topics and issues you’ll need to consider before you start your project.

Highlights and feedback

Mind scope and do not “try to boil the ocean”. Good advice. But I disagree with Stevens on how to approach one aspect of the scoping issue. He suggests that “in some cases, it may be better for the business as a whole to leave certain data sources untouched”. As I wrote here, I don’t think that scoping to the data source is a great approach. But you can decide.

I would also add that instead of looking at “Target System Content”, you should focus specifically on Business Processes — and this is regardless of the type of system your integrating into (MDM, DW, OLAP, etc.).

Data Profiling is a very important topic, and I’m ecstatic to see it on the list. But if it were up to me, I would have made it second or third. Almost every single failed or over budget ETL project can be linked to data source woes (quality, data availability, inadequate documentation, etc.). Most of these woes can be relieved by conducting good data profiles — a process that can begin during the initial requirement gathering phase of the lifecycle.

The data profile will reveal which source systems are useful. There may be redundancy, data quality, and data access issues compelling you to exclude a particular source. This isn’t a scoping issue, though. Go back to the project sponsor and to the board; report your findings. You may have other issues in your organization to resolve first.

In addition to Data profiling, I’m very happy to see that Naming Conventions made the list. Tied in closely with naming is the concept of conformity. I’ve written about this before, so please check here. In general, a label and its data must conform and be consistent throughout the entire system. And as Stevens points out, they must be agreed upon by the users.

For metadata, I also consider a third type: Process Metadata. This is technical data about all the processes (ETL, jobs, automated processes, reports, etc.). This data is then utilized directly by business users and administrators to monitor and audit the data warehouse. Is my report ready? consult the process metadata. Did that job finish? consult the process metadata.

Lastly, don’t forget to think about auditing, compliance, and lineage. If you’re making decisions based on the data retrieved from your data warehouse, you’ll need ways to trace data lineage. You’ll need to know when data was loaded, what version of the ETL system and business logic was used, and even why a particular data item might have been changed in transit.

Conclusion

This is a good high level checklist that you can use before you begin to dig deeper. It gives you a snapshot of some of the high-level decisions you’ll need to make before you get started with your next data warehousing project.

Perhaps Stevens will incorporate some of my feedback into a version 2!

Tags: , , , , , , ,

No Comments

SOX to Go the Way of Gitmo Bay?

May 26

Posted by Tod McKenna in Business & IT Issues

While the Obama administration is making a lot of changes — foreign policy and the economy are taking center stage — the business world is eyeballing the US Supreme Court’s latest decision to review the constitutionality of the Sarbanes-Oxley Act of 2002.

I wrote about this topic last year for Advisor Media, “Auditing Your Warehouse For Sox Compliance” as well as in my post “Formula 409: Private Companies Must Comply with SOX“. In my blog post, I said that innovation would take a series hit due to Section 409′s mandate that companies “must disclose material change events that would impact their financial condition or operations”. These material changes could include failed R&D projects: Not good for a public company looking to experiment. Imagine, for a second, what the technology world would look like if Bell Labs in the middle of the 20th century had these restrictions. Or Apple, or Microsoft, et al.

So will SOX be repealed? Is it unconstitutional? Time will tell, but I’m sure many CEOs are keeping their fingers crossed. Investors still need protection, but SOX just isn’t quite right.

Tags: , , , ,

No Comments

Formula 409: Private Companies Must Comply with SOX

Apr 4

Posted by Tod McKenna in Business & IT Issues

I’ve been doing a lot of research on Sarbanes-Oxley (SOX) compliance lately in part because I am now working in the financial industry and in part because I am preparing an article on the topic for Advisor Media.

SOX compliance is both complex and vague. There is no official compliance checklist, only various guidelines and advice from agencies, accountants, and vendors. Businesses are left to implement control frameworks, introduce new segregation of powers, add auditing and logging to existing systems, and rely on the advice and expertise of consultants and vendors who promise to deliver various solutions.

And if there is a misstep, the CEO could go to jail.

Section 409

One area I don’t hear a lot of discussion about from the IT world is the implications of Section 409. Not to say that there is no discussion, but that the vast majority of IT articles on SOX compliance focus on Sections 302 and 404. The reality is that Section 409 doesn’t easily translate to any specific IT implementation or control structure.

But it certainly has significant implications for a public company’s IT/R&D department. Here is the text of the Sarbanes-Oxley Act, Section 409:

Section 13 of the Securities Exchange Act of 1934 (15 U.S.C. 78m), as amended by this Act, is amended by adding at the end the following:

“(l) REAL TIME ISSUER DISCLOSURES. – Each issuer reporting under section 13(a) or 15(d) shall disclose to the public on a rapid and current basis such additional information concerning material changes in the financial condition or operations of the issuer, in plain English, which may include trend and qualitative information and graphic presentations, as the Commission determines, by rule, is necessary or useful for the protection of investors and in the public interest.”.

Basically, a public company must disclose material change events that would impact their financial condition or operations. And Big Brother wants pictures!

As an investor, this is great news; for the sake of innovation though, not so much.

Material changes

What is a material change? No clue. Well, I do have some clue, but there is no official definition of a material change in relation to Section 409 compliance. The only requirement seems to be that it is any change that impacts a company’s finances or operations. I suppose outsourcing a project to IBM, laying off a few dozen employees, or significantly cutting supplier costs all apply. Any change in an organization that could change profitability is a candidate. This includes a failed research and development project.

Yes, a failed R&D project.

Innovation takes a hit

The prospect of reporting failure likely makes CEOs a bit weak in the knees. Competitors will sniff the SOX box to find out what their rivals are doing — or not doing, for that matter. This in turn will force public companies to think twice about taking R&D risks. If you like innovation and continuous improvement, this doesn’t bode well.

As a result (directly or indirectly), we’ve seen a flurry of big-time acquisitions. Instead of developing new technologies in-house, companies are more inclined than ever to acquire them from smaller companies. To restate: the prospect of a failed innovative R&D project is forcing large companies to purchase private companies with proven ideas and technologies.

One of many examples

Take Microsoft’s acquisition of Stratature, an MDM vendor, last year. Stratature was recognized as the fastest growing private company in the Southeast in both 2005 and 2006. Microsoft bought them in 2007. Certainly Microsoft could have developed their own MDM solution. Right?

It is my feeling that the purchase had to do in part with Section 409. Microsoft could have started R&D on their own MDM solution. But MDM is complex and evolving. There is no one clear solution. If Microsoft embarked on this path, there would have been a chance they would have failed. Stratature was already a big success. The price was high, but worth it.

Opportunities for the rest of us

It is clear that Section 409 presents an interesting opportunity to small, private companies. If you invent an idea and grow and market it, it is more likely today than ever before that a larger company would seek to acquire you. Larger companies don’t want to take the risk of exposing themselves (and their failed project initiatives) under the “material event” clause of SOX. Besides, larger companies buy up smaller companies anyway: it is good business and often fits their strategic interests. Section 409 merely gives them an additional reason to do so.

Therefore, SOX compliance for all

Now you have a great product, and you have some interest from a larger public company looking to acquire you. But you have no internal control structures in place, no financial audit trail, and your IT department has broad access to all of your data. Because of this, the purchasing company will need to do a lot of work getting your business in shape for public life.

Not only that, but partnering with a public company may force you into compliance as well.

Lastly, your valuation will be higher if you comply with SOX (check out the Aberdeen Group’s “SOX Compliance and Automation: A Benchmark Report”, which can be downloaded from the Compliance Library at ultimate Software). Private companies who comply with SOX — especially sections 302 and 404 — operate better, are trusted, and are more attractive to potential buyers.

…

Unless you have no plans of being acquired or partnering with a public company, then it seems foolish not to start the process of meeting the requirements of SOX: Especially if you are an innovative company doing one or more progressive research projects.

Tags: , , , ,

3 Comments

ETL Subsystem 6: Auditing

Feb 14

Posted by Tod McKenna in Data Architecture

This article is part of a series discussing the Kimball Group’s “34 Subsystems of ETL“. The Subsystems are a group of “Best Practices” for delivering a BI/DW solution. In my articles, I discuss how each Subsystem can be implemented in SSIS or hand coded in Visual FoxPro.

Auditing is the process that tracks the activities of users by recording selected types of events in the security log of a computer (cite). In the context of data warehousing and data integration, auditing is the process of attaching metadata to each fact (and in some scenarios, some dimensions) in a dimensional model. This metadata is then available to various applications that use the model to examine data quality and reliability. The audit record then provides some context for the data and can be instrumental in turning that data into information.

In a dimensional model, the audit table is actually another dimension and adheres to the normal rules for dimensions. This includes the use of surrogate keys, SCD types, and ETL procedures. For more information about these subjects, see my following entries:

 

Why Bother?

Data warehousing is truly an ongoing struggle to provide high value data to business users so that they can make the best decisions possible for their function. The audit dimension helps us provide high quality, accurate, and complete data. In addition, it also helps us with following the lineage of data, which in this context means the ability to trace origin and ownership of a piece of information. Having adequate data lineage is crucial for complying with the government reporting requirements of legislation such as HIPAA and Sarbanes-Oxley, and for answering the tough questions auditors might have for you some day.

With a properly populated audit dimension, you can actually answer questions like: “How did that data get in there?”; “Where did that information come from?”; and “How did you arrive at that number?”.

As data warehouses continue to become the “system of record” in various organizations, it is critical that every fact is traceable from source to presentation.

Making it Work

Although I will reserve complete details on implementing an audit dimension for another posting (I already have a problem of being too long-winded in my entries!), here are the basics:

  1. At the very least, the audit dimension should contain the following attributes:
    • primary surrogate key
    • date loaded into the warehouse
    • version of the data warehouse when added
    • name, location, and version of the source system that the fact came from
    • information about the process that loaded the data (ex: SSIS execution ID, computer name, user, etc.)
    • information about missing data
    • information about how the fact was altered in transit
    • optionally (but very helpful), a quality identifier or score for the fact
    • optionally (and equally helpful), statistical information about the fact (deviations from the mean, outlier indications, etc.)
  2. Generate audit information as part of fact table generation, which normally occurs after dimensions have been processed. This would include gathering all of the necessary quality indicators, statistics, and lineage information.
  3. Create the audit record just before inserting into the fact table. This makes available the audit key, which you will need to add into the fact record.
  4. To query audit information later on, simply include attributes from the audit dimension in your query that cuts across the fact table.

It is reasonable to create an audit outrigger which would contain even more details about the quality and lineage of the fact. The approach I outline here uses a one-to-one relationship between the fact and the audit dimension; the outrigger would allow one-to-many relationships from a single fact to multiple audit conditions. I’ll reserve this design for a future posting as well.

SQL Server 2005 Integration Services (SSIS)

SSIS can handle auditing quite easily. In fact, there is a special Audit Transformation designed to add important lineage, package, and environment information to the workflow. The Derived Column transformation also exposes a significant amount of system and package information. The Aggregate and Row Count transforms will also prove quite helpful as they can be used to provide statistical information about sets of data.

If you need to include auditing information from dimensions into your fact audit, you might need to work with intermediate staging tables. When processing dimensions (like Customer, Product, Employee, etc.), you can build a staging table (preferably, a RAW formatted file) with information about the dimension (statistics, date loaded, etc.). Then tap into this staging table later on when processing individual facts.

You can check out the webcast “MSDN Architecture Webcast: Using SQL Server 2005 Integration Services to Populate a Kimball Method Data Warehouse” at microsoft.com for more information about adding auditing using SSIS.

Hand Coding with Visual FoxPro (VFP9)

In VFP, handling audit information is also easy, but requires some additional planning and coding to be comprehensive. In SSIS, many environmental variables (package name, user information, etc.) are available automatically. Also, in SSIS, it is trivial to add these variables to the data being moved from source to target. I’m not trying to suggest that SSIS gives you a comprehensive auditing system that requires little planning, but I am saying that it is comparably easier to add ad hoc auditing information using what SSIS provides out of the box.

In VFP, you will have to make this audit information available yourself. You can obtain this information directly from metadata you create (your process name, ETL version, etc) or from any number of VFP commands that can provide insight into the quality and lineage of the data you are processing. These commands can include those such as: CALCULATE; RECCOUNT; SYS(0) (network machine information); SYS(16) (executing program file); DATETIME; and many others.

If you have taken my advice and created a table-driven ETL system (see my Fox Forward presentation materials), then you’ll find that processing audit information is quite easy. Take a look at the information provided in those files and get back to me with any questions.

From here

In the end, both SSIS and FoxPro can handle the requirements of this subsystem. Auditing in this case is not really limited by the technology, but by the design, constraints, and requirements of the project.

Auditing, logging and other instrumentation are all important for compliance and data lineage. These topics will be discussed in detail in future posts on subsystems 29 “Lineage and Dependency” and 33 “Compliance Manager”. At the rate I’m going, these should be ready some time this spring!

In the next article, I’ll discuss removing duplicates and how VFP and SSIS can deal with the various challenges associated.

Tags: , , , , , , , , ,

4 Comments

HIPAA, PHI and the Patriot Act

Mar 20

Posted by Tod McKenna in Business & IT Issues

Although this isn’t new news, I thought I would bring it up. We had what is called a “lunch-and-learn” session today at work, where the company pays for some pizza and during the lunch hour we al sit around and learn something. Today’s topic was about personal health information (or, PHI). Basically, only certain people under certain circumstances are allowed access to this information. To give you an idea of how strict this is: A husband cannot see his wife’s PHI without her consent. This protection falls under HIPAA.

I asked a question about how the Patriot Act (and Homeland Security) Act changes our rights of privacy in regards to HIPAA. Normally, the most restrictive rules apply. This is the case, for example, with states laws verses federal laws — the more restrictive/protective law takes precedence. But these acts are different.

Therefore, the department of Homeland Security has the authority to seek and obtain your PHI:

“This authority can be interpreted to include requests for PHI of any type without the expressed authorization of the patient or legal guardian. ”

You can read more about this here. Of course the government denies that it will misuse the power, I doubt it. At least this current regime.

Tags: , , , ,

1 Comment