Start with data warehouse or datamart?

This discussion on LinkedIn presents many ideas about how to start a Business Intelligence (BI) project when there is no existing data warehouse or data mart.  There aren't many companies that haven't got some sort of BI solution already, so I don't think there will be many times this question will need to be answered.

But, in the case where a brand new BI solution is being developed, here are my preferences:

1. Design the BI solution for the future.
   This means planning ahead so that whatever you start with, you are planning for future enterprise data standards, naming conventions, global uniqueness, data quality, and data governance.  Do not operate in a business silo.
2. ROI is important.
   While it is difficult to prove a return on investment for a non-tangible product such as BI, most business funders of a BI solution have a niche perspective they want results for quickly.  Since building an Enterprise Data Warehouse (EDW) averages about 18 months, focused business data marts (DM) can be completed relatively quickly in six months or less.
3. Build both DW (to become EDW) and DM.
   When starting a new BI solution, don't consider an "either / or" solution.  A DM should get its data from the data warehouse (DW), which should be designed for the future EDW.  So, bring the master and transactional data from the source-of-record (SOR) database system into a small DW first.  This small DW could have only a few subject areas yet still be the beginning of the future EDW.  From the small DW, which should be designed as a normalized model in at least 3NF, create the DM that is designed as star (dimensional) model.  The advantage of this approach is that the SOR systems only need to populate a single DW (to become future EDW) and all DM created will be sourced from the same source of truth.  Thus they will all start with the same data and this will reduce the chances of different metrics being reported from different DM.

One question: in the IT field do you receive a job opportunity because of who you know or what you know? | LinkedIn

One question: in the IT field do you receive a job opportunity because of who you know or what you know? | LinkedIn

My response to this question:

---

Both "who" and "what" are important; it is not an "either or" situation. A person (friend, colleague, network contact, ...) - the "who" - can give you a "heads up" or maybe get your resume to a hiring manager, but it seems that most large companies (where the jobs are) now use recruiting firms to screen applicants. But if you don't meet the job's requirements - the "what" -, that won't get you the job and it will make your "who" look bad. Entry-level jobs in the US are few and far between. It seems most jobs (contract or FTE) are looking for mid-level experience (around 5 years) in specific skill sets. Certifications without work experience will only be better for you if you are competing against someone without either certifications or work experience - you have an advantage for an entry-level job. To get work experience, you can try to do pro bono work for charities, a friend's business, small businesses that need some small help; you can also try temp agencies, but they may require work experienced, too. This pro bono and/or temp work could lead to long-term contract or FTE recommendations. Good luck.

Company Acquisition and Data Integration / Migration for EDW

Scenario

Company A purchases Company B. Company A needs to integrate Company B's operational systems into its Enterprise Data Warehouse and possibly many of A's data marts Company B's EDW will be retired. Company A and B have different application systems that do the same jobs: General Ledger, Order Management, Human Resources, etc.

Enterprise Data Warehouse

This blog will focus on the Enterprise Data Warehouse (EDW). There will be many more data stores that will need to be considered, but we'll start with this.

Planning

What needs to be planned for? What order do tasks need to be done in? What risk factors need to be considered?

1. Understand Company A's source data architecture and metadata.
2. Understand Company B's source data architecture and metadata.

Company B may use a different database system (application software and database management system) than Company B does for the same business function. All businesses need to track their financial picture. Most will have some General Ledger (GL) application to do that. If A and B do not use the same GL application, we need to understand how B's system works and stores data and how that relates to A's system. We need to understand the metadata.

To do this, we will need to work with Company B's subject matter experts (SME). We will need B's source system's entity relationship diagram(s) (ERD) and data dictionaries. We also need to know what data items are brought into the acquisition's EDW (assuming they have one). Just because they bring something into their EDW doesn't tell us why they bring it in. So we will need to understand their business requirements for bringing data into their EDW.

So, the list of documents / information we have to get for the source system is:

1. Database ERD (Physical Data Model (PDM))
2. Data dictionary
3. Source-to-target mappings from source database to their EDW
4. Business rules for data brought to EDW. Why does the business need it?
3. What data from A's source goes into A's EDW
1. Why?
2. How used?
3. Transformations?
4. What data from B's source goes into B's EDW
1. Why?
2. How used?
3. Transformations?
5. Gap analysis
1. What data from A's source to EDW is missing from B's source to EDW?
2. What data from B's source to EDW is missing from A's source to EDW
3. Why does B need "extra" data?
4. Why doesn't B use A's "extra" data?
5. Why doesn't A use B's "extra" data?
6. Data profiling
1. Which tables and columns in B's source will be coming to A's EDW?
2. Which tables and columns in A's source hold the same data as in B's source?
3. Which column's in B's source have different data types, lengths, values, etc. than found in A's source.
7. Data mart analysis (assume data marts are sourced from EDW)
1. What data marts does B have that A doesn't have?
2. What data marts does A have that B doesn't have?
3. Will existing data marts in A support B's needs?
4. What new data marts will A need to support B?
8. Report analysis

Expand on these or add more.

What should you ask a potential client about?

I was recently asked to provide some "initial scoping" questions to be asked when meeting with potential clients.  Perhaps the word clients should be expanded to include both external customers for consultants and internal business units for full-time employees of a company.  Finding out what the client wants requires a consultative approach.  You must bring your expertise to the table and be willing to share and advise.  The client needs to know if their expectations are realistic or not; as do you, of course.

First of all, I want to know if a way of doing what they are asking to be done exists right now; this can be manually or some other computer application.  If the client wants to automate (computerize) a task that they do not do manually now, my advice would be to do it manually first so you have something to check the automated results against.  If it cannot be done manually, it can't be automated / computerized.  If it is not being done in some way now, how will they know if the new system is doing it right?

Sometimes clients say that they have another application that needs to be upgraded / revamped / replaced.  The following questions apply to both existing manual and computerized processes:

• Why do you need to upgrade / revamp / replace the existing method?



• Too slow
• Incorrect results
• Missing needs
• Cannot grow
• Need to provide results to larger audience



• What have you already tried to improve the existing method?



• What has worked?
• What has not worked?



• Are the business rules documented for the current method?



• If not, reverse engineering of the current method can be done to determine what the current algorithms are, but this will not prove that the original business rules are being followed.  I have bult systems that proved the existing system was doing it wrong, but it takes much time, effort, and documentation to prove that to the business.
• If yes, will the same rules apply to the new system?



• What do you like about the existing method?



• Interface
• Reports
• Processing



• What do you dislike about the existing method?



• How can these be improved?



• What is missing from the existing method that you want to add?



• Reports
• Dashboards
• Functionality



• What is included with the existing method (software) that you do not use that can be eliminated from the new solution?



• Get rid of "bells and whistles."



• What is the timeframe?



• Can a phased approach be used?



• What is the budget?
• What are the constraints?



• Must use existing infrastructure
• No internal resources available
• Must use existing in-house developers (and languages), databases, operating systems, etc.



• What phase of this project are you in right now? 



• If not in the intitial Plan and Analyze phase, it may be too late to recommend any changes.



• Do you have dedicated resources who will be working with me?
• How will results be verified / validated / proven?
• Who will take ownership of the completed system?
• Who will support the completed system?

Remember, these are initial questions to help get an understanding of and manage the client's expectations.  There will be many more questions to ask as the cycle progresses.