A Balanced Scorecard, Business Intelligence & Six Sigma Blog by Laura Edell Gibbons a.k.a Sassy Data Chic

Laura Edell's Profile

How often do you think about optimizing your operational reporting processes with your internal ticketing system / IT CRMs? Probably not as often as you would like -

Being a Lean Six Sigma Black Belt, I can’t help but think about these things.

In the process of promoting a report between a test environment and a production environment often involves customer communication in the form of an email – Why not standardize and automate that process?

First, you should have an inventory of your reports with an ID or CUID. This can be extracted from the BusinessObjects or BI provider auditor universe/logs respectively; in a worst case, start reporting off of your SQL data source instances or event logging tables.

Here is an example of C# code that automates the promotion process and generates a nice user friendly output which is standardized, and calls out the folder class where the report lives, provides a login link or if using SSO, a pass through token to log the user in auto-magically. Anything in maroon are comments for you , dear blog reader; anything in navy is a part of the actual email content.

“Your report has been created and placed on the TEST system for testing.

Please login (by clicking the link below) and test the report.
http://<servername>:<port>//InfoViewApp/logon.jsp

Your report can be found in: \\" target=_blank>\\<LOB folder>\<Department folder>\<Department subfolder>

The report name is: <ReportName>

Hide the CUID, Display the mapped report name only to end users like you see above.

Link using OpenDocument and append to C# code: http://<servername>:<port>/OpenDocument/opendoc/<platformSpecific><parameter1>&<parameter2>&...&<parameterN>

OR, you can go directly and get updated or new report here  :
You will have to login manually if you use this link. Remember to change the default Authentication (on the login window) to "Windows AD" or your respective authentication method (“Enterprise” or “LDAP” are your other choices).
Once the report has been tested please let me know by re-opening the ticket so I can move it to the production system.

Untested reports are purged every 30 days. Should you want to make any further changes to an existing report outside of data quality corrections, please open a new ticket but reference it to the old one for tracking purposes. Thank you for your kind consideration and adherence to the BI team report process.”

Note:
To obtain the document ID, navigate to the document within the Central Management Console (CMC). The properties page for the document contains the document ID and the CUID. Use this value for the iDocID parameter.

Your Horoscope - This Week (April 26 – May 2, 2009)

Don’t start or decide on anything of matter on Monday or Tuesday - Moon and Mars are in your ruling house where the Hermit is deriving a joyous solitude that may surprise you dear Libra, if you choose to listen. Your key word is independent, so break the chains of codependency now. Your love life looks hotter than ever. You can't escape the demands and desires of your lover. The presence of Mars in your relationship zone indicates it's time to clear the air. If there are any issues that have been pushed under the carpet, they're about to be exposed. You may find your partner a lot more argumentative than usual. Talking over difficulties will help the energy in the relationship to move instead of stagnate. There may be some turbulence, but you'll also feel a lot better for having shared your feelings.

Your Horoscope - April 2009

You'll be torn between work and personal matters at home and need to find balance on April 1 and 2. Don't let others push you into making decisions quickly. You'll have the chance to establish warm and affectionate bonds and enjoy life on the weekend of April 4. Catch up on a work project on the evening of April 5. Take your obligations very seriously on April 6 and 7. The Moon in your sign on April 8 and 9 will relax you and help you take it easy for a couple of days. You'll have to be proactive and assertive in some situations, though, and not wait for developments. Relationships may be strained on the weekend of April 11 if you don't deal with issues immediately. April 13 and 14 would be a good time to take a day trip if you feel the need for a change of scene. Burn off some excess energy by hitting the gym. Work will be intense on April 15 and 16 and you may need to put in some extra hours to get things finished. You may want to get involved in a humanitarian cause or at least be of help to someone in need on the weekend of April 18. A burst of energy on April 22 and 23 may be short lived but will motivate you to start new projects. Do some reading or call a friend on the evening of April 26. You'll feel frustrated by a critical person in authority on April 27 and 28.

Date: May 14, 2009

Time: 5:30–8:00PM, with billiards and networking to be held at the Parlor immediately following event

Location: Lincoln Square, 700 Bellevue Way NE, Bellevue, WA 98004 (see map below)

Lincoln Square

Customer Speaker: Vincent Ippolito, Washington Dental Services’ Director of BI

Topic: How to Deploy BI Programs in Time of Economic Hardship

Registration is free. Food is free to attendees. And best of all (unlike those other Data Organizations), you DONT have to be a member to attend, nor pay to attend even if you ARE NOT currently a member.

Space is extremely limited and advanced registration is recommended

Link to Register: http://1105media.inquisiteasp.com/cgi-bin/qwebcorporate.dll?P5RVKQ

TDWI NW Page: http://www.twdi.org/northwest

Simplistically speaking, the BSCOL (Balanced Scorecard Collaborative) defines the cascaded model approach for linking corporate values with individual's performance review goals/objectives. Starting at the bottom and looking at what each individual's personal goals are, and flowing up from there to the departmental goals, the division goals and finally the executive tier strategy/vision/goals/objectives, will help you to see where you have gaps in your values to what your employees are driving your company towards, versus where you have alignment.

Restructuring those values either at the top (harder) or at the individual contributor level at the bottom (easier) to ensure alignment will both drive better performance from your people because of the visibility offered to them by demonstrating how what they are tasked to complete in a year are contributing factors to helping the company achieve its organizational values. If your start with existing values, and then add what the existing objectives are as a starting point, see if you can map those together. 9x out of 10, they will NOT be aligned and that is a big AH-HA for many leaders to see on paper.

Then start to cascade from there to the division leader tier, the department management tier and lastly, to the individual contributors. That is the vertical alignment process from top to bottom, if that is your preference. Once you have these vertical lines mapped, look to see overlap or conflicting values between divisions, departments and people and find affinity areas that can be mapped logically back to the values where you started (in a top down approach). BSCOL.ORG and my blog (shameless plug) are both great resources offering excellent templates to assist in this process, like strategy maps. (See graphic provided by the TDWI BI Journal below) with a twist = Instead of using the 4 perspectives or in conjunction with (as that is very valuable in and of itself), use your organizational hierarchy instead. Financial becomes CEO's Established Organizational values/objectives, Customer becomes the divisions that report into the CEO where the circles become that divisions values / goals/objectives, Internal becomes the departments and Learning and Growth becomes the Individual Contributors objectives that their manager lists out in those pesky annual performance reviews

(sorry to those big believers but until true performance management like what I have outlined is institutional in all companies, the PR system is a bell curved sham where some of the best employees get the short end of the bell curve stick because how could one department have all highest performers even if they are a crackerjack team of employees. One day...A girl can dream right?...)

-Laura Edell Gibbons

 Having fueled a social networking surge, TDWI has started embracing what is super exciting in my eyes: social networking and BI. TWITTER: http://www.twitter.com/lauragibbons (had to shamelessly self promote)

TDWI NW CHAPTER: http://www.twitter.com/TDWI_NW_CHAPTER

TDWI on TWITTER: http://www.twitter.com/TDWI

LINKEDIN: http://www.linkedin.com/in/lauragibbons

TDWI NW CHAPTER on LINKEDIN: http://www.linkedin.com/groups?about=&gid=820537&trk=anet_ug_grppro

Enjoy! Laura Edell Gibbons, TDWI NW Chapter Board Officer & Chapter Secretary

In the past I have seen the following problem a couple of times: a customer creates a backup of a content database on one server farm (e.g. using STSADM -o backup or a DB backup in SQL) and restores the backup on a different farm and attaches the content database to a web application.

After this operation is done several operations (like variations and content deployment) fail to work with the following exception:

System.ArgumentException. Value does not fall within the expected range.    
at Microsoft.SharePoint.Library.SPRequestInternalClass.GetMetadataForUrl(String bstrUrl, Int32 METADATAFLAGS, Guid& pgListId, Int32& plItemId, Int32& plType, Object& pvarFileOrFolder)     
at Microsoft.SharePoint.Library.SPRequest.GetMetadataForUrl(String bstrUrl, Int32 METADATAFLAGS, Guid& pgListId, Int32& plItemId, Int32& plType, Object& pvarFileOrFolder)     
at Microsoft.SharePoint.SPWeb.GetMetadataForUrl(String relUrl, Int32 mondoProcHint, Guid& listId, Int32& itemId, Int32& typeOfObject, Object& fileOrFolder)     
at Microsoft.SharePoint.SPWeb.GetFileOrFolderObject(String strUrl)     
at Microsoft.SharePoint.Publishing.CommonUtilities.GetFileFromUrl(String url, SPWeb web)
...

The reason for this problem is that backup/restore does not adjust the references from the publishing page objects in the Pages library to their Page Layouts. These URLs are sometimes stored as absolute URLs including the server name. And this server name is the server name of the old server farm which cannot be resolved on the new farm.

Be aware that backup/restore of MOSS content databases between server farms are not fully supported! Official documentation of this support limitation is currently in the works. The supported way to transfer content between server farms is to use STSADM -o export/import or content deployment. Backup/restore is only supported for the same server farm.

In case that you have run into the above problem you have two options:

1. Throw away the database and transfer it correctly using STSADM -o export/import or content deployment
2. Fix the incorrect links manually using the following steps
   1. Open the web in SharePoint Designer
   2. On the “task panes” window, pick hyperlinks.
   3. For the “hyperlink” heading, click the arrow and pick (custom…)
   4. In the dialog, ask to show rows where the hyperlink begins with a URLs which are not valid on the current server farm
   5. For each of the files, right click and say “Edit hyperlink…” and Replace hyperlink with the hyper link that is valid on the current server farm.

Ideas for business intelligence practitioners to forge ahead with their BI initiatives in times of economic turmoil - To pursue best-in-class business intelligence and data management without incurring the wrath of the monolithic centralized platforms built when times were marked by economic growth in most revenue bearing verticals - Can this same race hold its pace with its same velocity and momentum when the economy shifts winds against the runners? In this, larger than life uber-BI applications race, marked in the last 13 - 24 months by a high rate of BI mergers and acquisitions, one has to wonder what will happen when the dust settles and the acquired folks realize they are no more a part of the organization that was the little guy you bought out way back when. Imagine how ProClarity felt when giant Microsoft came a callin' - Or, when SAP acquired BusinessObjects; did it become: B-I-C ERP meet B-I-C, well, BI. What about the true R & D exploratory labs like Google Labs, who churns out some interesting advancements in the technology space, offering APIs and SDKs for free to the bold and daring willing to take them up on their offer (oh, and that's no wilting flower of a number folks...Google had a cap of 10000 invitations when their App Store went live in the Spring of 2008. Some of the cool new data warehousing appliances or the process changes that came about with Master Data Management came about from the die harder open source fans who wanted to bring some structure to the masses without the cost of enterprise platforms with their clunky deployment paths and costly upgrades. Let's not forget, the Adobe flash-frenzied dashboard users now introduced to the presentation layer worthy interactive dashboard gauges that made mouths salivate the first through third time viewing it... As was expected, vendors tried to update their applications to mirror such interactivity and integration with the MS Office stacks, though Xcelsius still corners the presentation layer market by far. (Open source has some cool contenders especially when it comes to data visualizations) as the race to the dashboard 2.0 space moves into the collaborative world of social networks. No, there really is a Santa Claus, and I, too, am still smitten with PerformancePoint Planning !! PPS truly rocks the product placement in this arena, much harder to appeal to that broad category of stuffy Financial Budgeting and Planning CFOs, and the likes. So I ask, with the downturn in the economy, can such advancements be made in, as clearly demonstrated, a capitalized business intelligence software industry , whose recent growth spurts marked a growing sense of entitlement yet with subpar execution and results upon implementation, where services and solutions costs drove positive spikes in software sales, and vice versa? In fact, an interdisciplinary and while highly debated, interdependency exists between the BI, social network, collaboration and portals with custom embedded BI apps, web services and more all geared with one goal in mind: to optimize in a cost effective manner in an effort to drive better, more data driven decision making ? Or is this another blue skies and apple pie dream manifested by one girl's love for business intelligence

Try this simplified toolkit approach based on standards defined by the Federal Enterprise Architecture (FEA) board, along with NASCIO:

Performance Reference Model (PRM) •Inputs, outputs, and outcomes •Uniquely tailored performance indicators  

In this category, you should immediately think Scorecard (Balanced Scorecard and otherwise),:

--Each scorecard have 4-6 perspectives which are logical / categorical groupings of key indicators, or what I like to call 'affinitized' KPIs.

--Each perspective has < 6-7 KPIs (Key Performance Indicators) (if you receive pushback, and you will, as people would define a KPI for the percent of time the Express Grocery queue contains purchasers with more than the specified limit of 12 – 15 items, doll it up for the BBB as a complaint, and deliver it with such ferver one almost winces when they realize said complaint is recycled faster than their next trip to the grocery store

    --Remember the 3 keys to success with defining KPIs: are they actionable, are they measureable (not in a future state, but today can you measure it) and will they drive a performance based behavior change (think incentives, as they represent a perfect example of a performance based behavior changer).

– So, ask the question now… "That's a long list, John or Jane Exec…First, what are you going to do with that information [the so-what question distinguishing actionable from interesting]…can you really drive performance improvements in your business with more than 5 indicators in the case where all 5 go red at the same time or if you don't want to be direct, you can ask "how do those KPIs link to your performance objectives personally – Any leader worth their title will take the time to align the activities and work tasks that they personally, or through delegation, list in their performance reviews.

--This process can be started at any layer in an organizational hierarchy and is called Goals / Objectives Alignment or Cascaded KPIs

--Most leaders you ask have no more than a few KPIs that they ACTUALLY care about – just over the hurt feelings now, or feeling that you have wasted x number of hours measuring and reporting on metrics that top leadership doesn't care about; if you have been in BI long enough, you will have experienced this at least once in your career.

Business Reference Model (SRM) • Lines of Business (functions and sub-functions) • Agencies, customers, partners

How will the performance KPIs cascade down to the individual contributor from the CEO's goals? Easy – take this example:

CEO sets a goal of wanting to increase revenue by growing the Sales line of business, specifically new customer sales. He sets a goal of 35% growth of new sales revenue, which the VP of Sales is tasked to drive. They , in turn, assign the goal to the account managers in charge of new customer accounts who then add the same goal to their salesforce in the field. The KPI becomes New Sales Growth >= 35%, frequency is set to weekly with hierarchical rollups to monthly and quarterly aggregations.

--Now, you may ask yourself, what about the Operations department where Customer Sales and Service (aka Telesales) lives, and bingo! You're getting it now…Even though it was tasked to the VP of Sales, they, or that same CEO, should have realized that the Telesales department can also generate revenue from new sales, just those that come through a different channel. Instead of the typical route of calculating in field sales and measuring the sales department for a goal of this nature, the Operations VP should have the same goal on their performance review as the VP of Sales, which they, in turn, delegate to their Telesales Center Managers, who delegate to the Supervisors who delegate it to the agents on the phone – While it is an implicit delegation as one who is hired to man a Telesales line understands their job is to answer the phone and make sales (thus, encouraging sales growth), it is still an action that is mandated by a supervisor, who received the mandate from their manager, who likely received the goal from the corporate office VP of Operations or person responsible for the Telesales center.

--It flows from top or bottom (vertically) as well as horizontally since in this example, it covers two horizontal business units (Sales and Operations).

--This is why starting with objectives or goals makes this process, that is, cascading KPIs, that much easier because you have a definitive starting point and end point which is that same objective/goal.

New Sales Growth >= 35% is the same whether you start with the call center agent whose awesome sales performance on the phones contributes to making her supervisor meet their goal which was to grow sales by 35% who enabled their manager who enabled the VP of Operations and the VP of Sales objectives who then met or exceeded their CEO's original mandate.

Service Component Reference Model (SRM) • Service domains, service types • Business and service components

--A service component is defined as "a self contained business process or service with predetermined functionality that may be exposed through a business or technology interface.

Data Reference Model (DRM) • Business-focused data standardization • Cross-agency information exchanges

Technical Reference Model (TRM) • Service component interfaces, interoperability • Technologies, recommendations

As David Besemer wrote in his May 2007 article for DMReview, 'SOA for BI', "It took Michelangelo nearly five years to complete his famous works at the Sistine Chapel. Your transition to SOA for BI can go much faster if you start with data services."
What are data services? According to Wikipedia, wait, there isnt an existing definition on Wikipedia. First, a definition with I share with the Internet users of the world vis-a-vie WikiPedia:
"A Data Services Oriented Architecture or 'DSOA' framework consists of a combination of schemas, classes and libraries that facilitate and provide the ability to create and consume data services for the web. DSOA reveals the consumer data underlying architecture, exposed using Data Services' Entity Data Model, and provides reusability of your service when developed correctly," Laura Edell-Gibbons, Mantis Technology Group Inc.
portalnavbar
And by correctly, I would highly recommend not getting bogged down by the concept of plug and chug, dubbed by my colleague Tip, or making your code reusable. It is all a balance, remember, young BI padawan.
Select best-in-class data services middleware to help you model, develop and implement your back-end BI services. PowerDesigner is a pretty rocking modeling tool, which covers everything from data element impact analysis to facilitating requirements gathering. Simplistically speaking, I am a big fan of the simplicity of SQL Server Integration Services and the new Data Services, both Microsoft products, though this opinion is certainly one that doesnt necessarily represent the populas vote. I am a big fan of Infomatica and Data Integrator (now called Data services, funnily enough under the SAP/BusinessObjects brand).
During the first and second projects, be sure to track all productive working hours to deliver each phase of your solution and costs savings for the efficiencies I expect you designed your system with the unescapable expectation of being the ROI-generator, a widely accepted expectation that all BI systems have high ROI, and many due. Start small, grow enterprise once the concept has been leaned out and efficiencies expected and beyond are gained. Then, as you expand your deployment from project to enterprise, you can easily self-fund additional licenses and other required resources with the savings or other benefits gained on those 1st two, somewhat painful, 'initiation' projects. We all have to go through the process and while painful at times, the learning experiences offered outweigh any of the difficulties while learning.

It is better to build the new services project by project, always making the predecessor available to other projects in a unified data services tier as you go. You and your team can then choose whether to rhttp://scorecardstreet.spaces.live.com/mmm2008-11-07_18.20/#euse a data service, extend an existing service, buy or to build something from scratch, my least favorite BTW.
Over time, these will change and I suspect 'reuse' will become the greatest portion of the proverbial pie, whereas today, I believe the paradigm shifts more in the direction of 'build from scratch.'
Starting to plan front-end BI services up front, even while deploying your backend BI services, will enable you to make small but meaningful steps without much noticable downtime to the organization, something especially important for those of us working with a '4 x 9s' uptime SLA for our data centers. Plus, remember, if you build these on a powerful data services foundation, you will reduce your time to market, and your TCO over time. By providing the business with their much anticipated and needed operational reports, tactical and strategic dashboards and performance management analytics while infusing the lot into your SOA, one will reep rewards greater than my words could ever portray, dear reader...Til then, remember 'what will come sooner than you think is no more when than how'.

References:

While many companies feel they have strong BI programs, most, in my experience, have operational reporting systems and sometimes, if you are lucky, they also have strategies that go with those systems or even better, are driving those systems (fueled by requirements gleaned from business needs and actual usage scenarios vs. the "way it has always been done/reported on").

As you can see in figure 1, that level merely tells you the 'WHAT' - it doesn't answer the 'WHY' it happened (root cause), or predict the 'HOW' it might affect you in future, nor the 'WHEN' in terms of monitoring if it is happening currently or just a past event.

Your BI roadmap should have a similar long term plan. If you want to provide increasing value to your organization, one must get out of the business of operational reporting and move towards the real meat and value of BI, which lay in the analysis, monitoring and predicting in terms of how the business views their needs from BI, not how BI believes they can deliver information.

The techniques that exist within the organization for collecting, presenting, and validating metrics must be evaluated in preparation for automating selected repeatable processes.

Cataloging existing measurements and qualifying their relevance helps to filter out processes that do not provide business value as well as reducing potential duplication of effort in measuring and monitoring of critical data quality metrics. Surviving

measurements of relevant metrics are to be collected and presented in a hierarchical manner within a scorecard, reflecting the ways that individual metrics roll up into higher level characterizations of compliance with expectations while allowing for drill-down to isolate the source of specific issues.

As is shown in Figure 1, collecting the measurements for a data quality scorecard would incorporate:

1. Standardizing business processes for automatically populating selected metrics into a common repository

2. Collecting requirements for an appropriate level of design for a data model for capturing data quality metrics

3. Standardizing a reporting template for reporting and presenting data quality metrics

4. Automating the extraction of metric data from the repository

5. Automating the population of the reporting and presentation template, or a data quality scorecard

Figure 1:

As a data architect, I am often amazed at how many with the same title really do not understand the core differences between dimensional model structures in my industry.

In fact, it is a rarity to find the data architect willing to be challenged without personalities coming into the mix. As as we all know, when you fight the person and not the problem, you end up with hurt feelings and resentments in the workplace. For an EDW, this can lead to people resigned and not willing to stand up for what they think, which leads to the 'sheep'-like syndrome called 'Group Think', thus resulting in an EDW that is built off of emotion and not educated beliefs or best practices.

I thought I would take the time to explain some of the differences between dimensions to enable you, reader, to stand up for the correct approach when you are faced with a contended data model designed by you.

Remember, they are not attacking you, just the model. Look at it as a chance to grow and learn from others. Maybe, just maybe, having 4 or 6 eyes is better than just your 2 and your model, already attributed to you from a recognition perspective, will be that much better.

Here we go:

Slowly Changing Dimensions (SCD - of the Type II variety is where I become a Type II gal)

• What in the world is a SCD anyway?
• Ralph Kimball defined this his 1996 book as the following:

(Kimball, 1996), a slowly changing dimension is a dimension table in which a new row is created each time the underlying component entity changes some important characteristic. Its purpose is to record the state of the dimension entity at the time each transaction took place.

This concept is hard for for those who have primarily dealt with changes as handled in operational systems: no matter how a customer changes, we want to ensure that we have only one customer row in the customer table.

Thus each row in a slowly changing dimension does not correspond to a different entity but a different “state” of that entity—a “snapshot” of the entity at a point in time.

To create a slowly changing dimension table, the following design steps are required:

• Define which attributes of the dimension entity need to be tracked over time. This defines the conditions for creating each new dimensional instance.
• Generalize the key of the dimensional table to enable tracking of state changes. Usually this involves adding a version number to the original key of the dimension table.

Apart from the generalized key, the structure of the slowly changing dimension is the same as the original dimension. However, insertion and update processes for the table will need to be modified significantly.

Splitting Dimensions: “Tiny-Dimensions”
In practice, dimension tables often consist of millions of rows, making them unmanageable for browsing purposes. To address this issue, the most heavily used attributes (e.g., demographic fields for customer dimensions) may be separated into a mini-dimension table. This can improve performance significantly for the most common queries. The mini-dimension should contain a subset of attributes that can be efficiently browsed. As a rule of thumb, there should be fewer than 100,000 combinations of attribute values in a mini-dimension (i.e., fewer than 100,000 rows) to facilitate efficient browsing (Kimball, 1996). The number of attribute value combinations in a tiny dimension can be limited by:

• Including attributes in the mini-dimension that have discrete values (i.e., whose underlying domains consist of a fixed set of values).
• Grouping continuously valued attributes into “bands.” For example, age could be converted to a set of discrete ranges such as child (0-17), young (18-29), adult (30-45), mature (45-64), and senior (65+).

 Figure 4. Mini-Dimensional Table (Detailed Level Design)

Figure 4 shows how customer demographics in the Order Item star could be stratified out into a mini-dimension table. Some of the attributes in the original table have been transformed to reduce the number of rows in the mini-dimension table: 

Think Star Schema is the only way to go with your EDW? Think again...StarFlake is a Best in Breed Approach that Might Make BI on top of the EDW that much more capable and robust.  Read on... I will cover the following concepts:

SUMMARY: In my consulting practice, I recommend an incremental, 'bottom-up' implementation methodology, similar to that advocated by Ralph Kimball. This has proven to be a successful deployment approach to ensure a short-term return on investment with minimal project risk, while still delivering a data warehousing architecture that provides a standardized, enterprise wide view of information. This column describes a typical project plan based on a bottom-up implementation methodology.

* REQUIREMENT FOR BOTTOM-UP DEVELOPMENT

I receive numerous e-mails from both technical and business managers who have become extremely frustrated by the inordinate amount of time and effort required to build data warehouses using traditional, top-down development techniques. Due to the large amount of effort required up front to perform user interviews and enterprise data modeling, organizations using top-down techniques often wait 12 to 14 months to get any return on investment. In some cases, managers complain that they have worked with a systems integrator on a top-down development project for over two years without obtaining any solution to their business problem.

As described in previous articles, a bottom-up development approach directly addresses the need for a rapid solution of the business problem, at low cost and low risk. A typical requirement is to develop an operational data mart for a specific business area in 90 days, and develop subsequent data marts in 60 to 90 days each. The bottom-up approach meets these requirements without compromising the technical integrity of the data warehousing solution. Data marts are constructed within a long-term enterprise data warehousing architecture, and the development effort is strictly controlled through the use of logical data modeling techniques and integration of all components of the architecture with central meta data.

* PROJECT PLAN

The representative project plan described below is based on an incremental, 'bottom-up' implementation methodology. In my experience, this has been the most successful deployment approach to ensure a short-term return on investment with minimal project risk, while still delivering a data warehousing architecture that provides a standardized, enterprise-wide view of information.

The project plan breaks down into three major phases, as follows:

1. Data Warehouse Requirements and Architecture - gather user requirements through a series of requirements interviews, assess the current IT architecture and infrastructure, along with current and long-range reporting requirements, and develop an enterprise data warehousing framework that will provide maximum flexibility, generality and responsiveness to change. Define functional requirements for the initial data mart within the enterprise architecture. Optionally, conduct a Proof-of-Concept demonstration to select enterprise-class ETL and/or BI tools.

2. Implement Initial Data Mart under the New Architecture - prove the new architecture works by implementing a fully operational data mart for a selected subject area within a 90-day time box.

3. Develop Additional Data Marts under the New Architecture - on a phased basis, develop additional architected data marts within the new framework.

Timeboxes are placed around each phase of the project plan to strictly limit the duration of the development effort. Phase 1 (specification of architecture and functional requirements) is limited to four weeks. If proof-of-concept testing of ETL and BI tool is conducted, these tests occur outside the timebox for Phase 1, due to the uncertain timing of scheduling vendors to perform the tests. Phase 2 (development of initial data mart) is limited to 90 days. Phase 3 (development of additional data marts) is limited to 60 to 90 days for each subsequent data mart.

* TASKS AND DELIVERABLES BY PHASE

The list below summarizes representative tasks, timeframe, and deliverables for each of these three phases.

**Phase / Task
1. Requirements and Architecture
---Conduct workshop to review/define strategic business drivers, review current application architecture, and define strategic data warehousing architecture.
---Elapsed Week :1
---Deliverables: strategic requirements, document specifying the recommended long-term enterprise data warehousing architecture

---Conduct requirements interviews with personnel from multiple subject areas and document the results of the interviews.
---Elapsed Week: 1
---Deliverables: Interview summaries, requirements inventory

---Conduct workshop to define functional specifications of initial data mart,
---Elapsed Weeks: 2
---Deliverables: scope statement, reporting and analysis specifications

---Develop high-level dimensional data model for initial data mart
---Elapsed Weeks: 2
---Deliverables: logical data model

---Conduct workshop to prepare for a Proof-of-Concept test of leading ETL tool(s)
---Elapsed Weeks: 3
---Deliverables: specifications for the ETL POC

---Conduct workshop to prepare for a Proof-of-Concept test of leading BI tool(s)
---Elapsed Weeks: 3
---Deliverables: Specifications for the BI POC

---Conduct workshop to develop Phase 2 project plan
---Elapsed Weeks: 4
---Deliverables: Phase 2 project plan

**Phase / Task
2. Implement Initial Data Mart in 90-Day Time-Box,
---Elapsed Weeks: 12
---Deliverables: live architected data mart, live OLAP cubes and reports, central meta data repository, local meta data repository, reusable ETL objects and conformed dimensions

**Phase / Task
3. Implement Additional Data Marts
---Elapsed Weeks: 8-12 each
---Deliverables: Additional architected data marts

* PHASE 1 IMPLEMENTATION

The initial task in Phase 1 is to conduct two on-site workshops, limited to 1 to 2 days each. The function of the first workshop is to bring all members of the development team up-to-speed on alternative enterprise data warehousing architectures and 'best practices' in data warehousing. The second workshop is used to achieve consensus on the specification of an enterprise data warehousing architecture capable of meeting the long-term business requirements of the organization.

Interviews with personnel from multiple subject areas are held to define high-level functional requirements for each subject area. Subject areas often correlate with proposed data marts, in areas such as finance, sales, marketing, HR, supply-chain management, customer touchpoints, etc. As described in a previous column, the interviews are kept deliberately short (one day or less per subject area). The deliverables from each interview include a short, concise requirement specification for the subject area and a top-level dimensional data model representing the data sources, source-to-target mappings, target database, and reports required for a specific subject area. The top-level data models from all subject areas are then synthesized to identify common data sources, conformed dimensions and facts, common transformations and aggregates, etc.

Following synthesis of functional requirements from all subject areas, a workshop is held to define the functional requirements for the initial data mart, lay out the project plan for the development of the initial data mart, identify required skill sets, and personnel assignments to the project.

The next task is to define a high-level dimensional data model for the initial data mart, representing an expansion of the model prepared as part of the user interview process.

If the organization has decided to conduct a proof-of-concept test of competitive ETL tools and BI tools, the next two tasks represent preparation of functional specifications for the tests. Proof-of-concept testing may be required if multiple ETL or BI tools are being evaluated and it is politically expedient to conduct rigorous testing of competitive products prior to making a selection. I have found that an intensive two-day workshop is sufficient to specify the functionality of the tests to be conducted for a proof of concept for either an ETL or BI tool.

The final task in Phase 1 is to specify a detailed project plan for the implementation of the initial data mart.

* PHASE 2 IMPLEMENTATION

In the recommended bottom-up development methodology, the process of implementing the initial data mart is limited to 90 calendar days. Although 90 days is arbitrary, it fits the needs of business managers for a rapid solution of the business problem and meets the needs of CFOs for a 90-day Return-On-Investment. The 90-day timebox starts on the day that the ETL tool, the target DBMS, and the BI tool are successfully installed. To meet the challenge of a 90-day implementation process, utilization of an ETL tool, rather than hand-coding the extractions and transformations, is strongly recommended.

Implementation of the initial and subsequent data marts is ideally performed by a small team of data warehousing professionals, typically consisting of a data modeling specialist, an ETL specialist, and a BI tool specialist. In my own organization, I emphasize cross-training of personnel, which minimizes the number of personnel that need to be assigned to a project and maximizes their efficiency.

The first task for the development team is to design the target data base for the initial data mart. Modeling of the target data base for the initial data mart proceeds through three steps: design of an entity-relationship diagram, then a logical dimensional model, and finally a physical model of the target database schema. Although the E-R diagram is not required for the initial data mart, it is required for subsequent data marts to ensure that all physical data models for multiple data marts are derived from a common logical specification.

The next major task is to specify and implement data mapping, extraction, transformation, and data cleansing rules. The data mapping and transformation rules are defined first in natural language, and then implemented using only the transformation objects supplied with the ETL tool. The objective is to avoid coding any ETL processes. It is hard to predict how long this task will take. For many applications, specification and implementation of the transformation rules should not take more than 3 to 4 weeks. However, some applications may require many months to specify and implement complex transformations. These applications are not likely to fit within a 90-day implementation timebox.

In parallel with implementation of the transformation logic, developers build aggregation, summarization, partition, and distribution functions. The ETL tool may be used to compute aggregates in one pass of the source data, using incremental aggregation techniques. Pre-computed aggregates are recommended for most data warehousing applications to provide rapid response to predictable queries, reports, and analysis.

The final task in Phase 2 is delivery of a fully operational, architected data mart for the initial subject area, using an exact subset of the enterprise data warehousing architecture. Ideally, the development team delivers all of the functionality that was specified at the beginning of the 90-day timebox. However, the team is permitted to defer low priority functions in order to make the 90-day timebox. The team is self-managed and organizes its resources to deliver as much functionality as possible within the 90-day development window.

* PHASE 3 IMPLEMENTATION

The objective of Phase 3 is to build additional architected data marts. Additional data marts are built by the primary development team using common templates and components, such as conformed dimensions and facts, common transformation objects, data models, central meta data definitions, etc.

In the bottom-up methodology, a central data warehouse, an operational data store, and a persistent staging file are optional. Data marts are typically developed using a data warehousing backplane schema design, as described by Ralph Kimball in his book 'The Data Warehouse Toolkit, Second Edition.' There may be good technical reasons to incorporate a central data warehouse and an operational data store in the enterprise data warehousing architecture. However, in the bottom-up methodology, development of the central data warehouse and ODS are deferred until they are clearly required. A central data warehouse is often required and when detailed, atomic data from multiple data marts must be accessed to generate cross-business reports, and when there is a low percentage of conformed dimensions across the data marts.

Maintenance and administration of the data warehousing application is an ongoing function. A secondary team may be used to enhance and maintain completed data marts. The primary team transfers transformation templates, data models, conformed dimensions, conformed facts, meta data, etc. to the secondary team to simplify the enhancement and administration of completed data marts.

My organization has used this methodology successfully for many clients and has a good deal of experience with it. However, successful implementation of the methodology depends on several critical success factors:
-a dedicated implementation team;
-consulting help from an experienced organization at the beginning of the project;
-backing of a business manager who is hungry for a solution to a painful business problem; and
-integration of all components of the architecture with central meta data.

Figure 1: Basic star schema for typical point of sale data-mart

In the current design, the database does not enforce the integrity of data with respect to the definition of the fact tables. The basic fact table should contain rows only for daily sales for a store for all the products sold in a day. If we try to insert an aggregated record in the basic fact table (for week, district and brand), the database would not reject the rows. If we like to separate the fact tables according to the grain, then we would have to rely on the ETL process to enforce the granularity of the fact table. Enforcing rules through ETL process has following drawbacks:

• ETL processes need to persist the metadata for populating the fact table in the first figure and the aggregated fact from the 2nd figure. 
• Multiple ETL processes may be responsible for populating the fact table. In such cases, we would need to duplicate the rules to each process. By maintaining rules (business logic) in the database layer, we can minimize maintenance headaches because the logic is now centrally located making change aforethought and less prone to mistakes.
• ETL processes may not be efficient to enforce rules if the process relies on SQL to enforce the rules. Database integrity constraints are more efficient than SQL.
• If the ETL process relies on a programming language, then it would be very difficult to maintain the meta data in the ETL process.

Let us see how the design would change when we partition the dimension table to enforce the granularity of the fact and aggregate table. If we partition the dimension table based on the level of the data in the hierarchy, then we can use these partitioned dimension tables to join with the appropriate fact table. Figure 2 illustrates how the data model will look in the partitioned dimension table approach.

Figure 2: Constraint Enforced Point of Sale Mart

In the new design, the basic fact table joins with the basic dimension tables and the aggregate fact table joins with the higher-level dimension tables. In this design, the granularity of the fact table is enforced by the relational constraint defined in the database. We can use SQL to create a view over all partitioned dimension tables using SQL union all to create a logical design that is similar to the basic POS data mart as described in Figure 1. We can also create views over the basic fact table and aggregated fact table to have one fact view with multiple grain, but this depends on how the data modeler wants to present the logical model.