Low-rate recruiters – The bane of my existence

There is nothing more annoying to me in my profession than getting calls and emails from recruiters who are looking for senior-level BI people at ridiculously low rates.  A common example: they want someone with 10-years experience in BI to work in NYC for $45/hr, no expenses paid.  Seriously?  I get 3-10 such requests each day.  90% of the time the recruiters are from India.  They will email you as well as call you.  And if you make the mistake of replying to an email of theirs, you can expect they will call you immediately and continue calling until you pickup, no matter what time of day it is (somehow they got my home and cell phone and would call both numerous times).  Many times their English is poor and their phone numbers are disguised to show as coming from the US (New Jersey seems to be the most popular one, where they have “headquarters” there with outsourcing centers in India).  They make telemarketers seem like angels.

Most of the time their email does not tell you what the pay rate is.  If you ask, they usually reply “Rate is negotiable please let me whats the rate you are looking for?”.  If you insist they will tell you, and you can expect the rate to be no better than half of what the average going rate is.

My advise is to immediately delete an email from an Indian firm or hangup if they call (but beware they sometimes use fake English names).  The few times I have expressed interest were complete wastes of time.  They always want your resume (no doubt they have a quota to obtain a certain amount of resumes each week), and always want you to fill out a bunch of useless info (i.e. name, address, visa status, expected hourly rate, etc).  And to top it off, many of the jobs are nowhere even close to my skill set (“You are a perfect fit for the opening we have for a K2 developer”).  They never bothered to actually look at my LinkedIn profile, as you would then know I am nowhere near a “perfect fit”.  Sending out these boilerplate template emails is no different from spam.  Often these emails will have a unsubscribe link that will take you to a webpage that looks just like the graphic below, with a different company name and color, but everything else is the same, leading me to believe they are all working under the same company:

Untitled picture

Unfortunately these recruiters are all over dice.com now, making it that much more difficult to filter through the job listings to find jobs worth applying for.  Many times you will see multiple postings of the same position from different Indian recruiting firms (just the other day I saw 13 postings on dice for the same exact position).

These recruiters are very bottom-level people who make tons of calls a day to try to find someone who expresses interest.  When that person is found, they then hand you off to their “manager” who will try to convince you to allow them to submit you to the client at some crazy low rate (a recent recruiter asked me if I would be interested in taking a contract in Minnesota, no expenses paid, for $40/hr less than I am making now at a local client.  He knew what I was making and that I did not have to travel, yet he still tried to convince me to let him submit me).

These firms from India are offering low rates because there are so many layers of companies taking a piece of the rate.  Here is a typical scenario: A US company wants to hire a consultant/contractor, so they have a US staffing firm try to fill the position. That staffing firm can’t find someone, so they sub-contract out to a large staffing firm in India. That large staffing firm then sub-contracts out to a smaller staffing firms in India, and on-and-on.

If contacted by one of these recruiters, the first question to ask them is if they are working directly for the client.  If not, they are one of many layers and the hourly rate will be low.  I see similar abuse at large consulting firms offering a low salary.  Those firms pay a low salary and then bill you at triple (or more) what you are making.  The result?  Lot’s of junior programmers doing senior level work.  That is why so many projects fail or go way over budget .

I asked a recruiter who has been in the industry a long time about this:

I’ve actually worked for a few Indian firms and your assessment is pretty accurate.  Sometimes there are many layers to a deal and that destroys the margin.  But sometimes they may just be recruiting for a client that doesn’t care about quality talent and only offers poor rates.  To work with those types of clients, some staffing firms take the approach of throwing as much “bleep” against the wall and hoping some of it sticks.  That takes very little recruiting talent and it is more of a numbers game than anything else.  But you get what you pay for, and the clients that provide these low bill rates usually get the lesser talent (or the talent that is sponsored on an H-1B until that person gets their Green Card).  I hate to stereotype, but foreign national firms are usually the ones that are willing to churn through the “low rate” business, that is why they are the ones always contacting you about low margin stuff.

I’m not a fan of working with these types of firms.  There is very little accountability, a lot of turnover, and it gets pretty greasy when everyone is fighting over a nickel.  When I first got out of school and didn’t know any better, I went to work for a firm like this.  I will say this, it was a great way to get experience and I learned a lot during my time there – it helped me get to where I am today, so I can’t bash it too hard.  At this point in my career I just choose not to work that way any longer.  But if you take me as an example of the experience level of people who work in that space (I had none to very little when I worked there) it is a good reflection of the type of people you’re connecting with.  They don’t know very much about the business, the client, the details of the opportunity, or the staffing industry for that matter.  They are basically reading you a job description word-for-word, and that job description was spit out of a vendor management system where the staffing firm has no inside knowledge.  Not much value there – but there is a market for this type of business and so you’ll continue to see it…

More info:

I’ve Reached an Unfortunat​e Conclusion About Indian Recruiters

Indian Recruiters – What Is Wrong With This Country?

Posted in Consulting, SQLServerPedia Syndication | 8 Comments

Presentation Slides for Building an Effective Data Warehouse Architecture

Thanks to everyone who attended my session “Building an Effective Data Warehouse Architecture” for Pragmatic Works.  There were over 500 attendees!  The abstract is below and the recording of the session is available here.  I received a ton of questions and I have attempted to answer most of them below.

Here is the PowerPoint presentation: Building an Effective Data Warehouse Architecture

Building an Effective Data Warehouse Architecture

You’re a DBA and your boss asks you to determine if a data warehouse would help the company.  So many questions pop into your head: Why use a data warehouse?  What is the best methodology to use when creating a data warehouse?  Should I use a normalized or dimensional approach?  What is the difference between the Kimball and Inmon methodologies?  Does the new Tabular model in SQL Server 2012 change things?  What is the difference between a data warehouse and a data mart?  Is there any hardware I can purchase that is optimized for a data warehouse?  What if I have a ton of data? During this session James will help you to answer these questions so your response to your boss will provoke amazement and lead to a big raise.  Or at least help to lead you down the correct path!

Questions from the presentation:

Q: If CDC violates a vendor TOS, would CDC work against a OLTP mirror done implemented with log shipping?

A: No it is not possible to enable CDC at Log Shipping Destination. This is because changing (enabling CDC) to the log shipped database (destination) would require changes to the schema; this would bring the need to take the database online, breaking the log chain and making it inaccessible to the additional logs (During log shipping, the destination/secondary database will be in Standby or No Recovery mode that means it will be available for Read-Only).

Q: How’s the fast tracking dw performance compared with the PDW from Microsoft?

A: Fast Track database can have very high performance (see SQL Performance Tuning Test Results for Data Warehouse Architectures) but once you get to 50TB or more you will reach the limit of the hardware.  That is where PDW comes into play and queries that can take hours on a SMP server can be done in seconds on a MPP server.

Q: For the ELT I notice you put SSIS… Do you recommend SSIS over TSQL for this process? Is there a reason to use one over the other?

A: I have a blog about this: http://www.jamesserra.com/archive/2011/08/when-to-use-t-sql-or-ssis-for-etl/

Q: For the cubes, once they are processed.. If new or updated data is added to the data warehouse, do the cubes have to be re-processed to recognize the new data?

A: Yes, the cubes have to be reprocessed, but you can create daily partitions in the cube so you only have to process one partition and not the whole cube

Q: Does DW typically keep history or is this only done in dimensional models?

A: It is usually only done in data mart dimensional models.  I would only keep history in the EDW if I needed history from day one and I was not planning on creating a data mart for a while

Q: Appliances are good as stand alone so what are the options for blending this into a SAN environment?

A: SAN’s are very slow compared to the direct-storage that is used with an appliance.  I’m afraid you are out of luck if you want to use an appliance with a SAN

Q: What do u mean by indirect end user access of data vs direct

A: Data marts can be directly accessed (a user can query it).  EDW should not allow a user to query it.  Instead, a data mart should be created from a EDW and the user can query that data mart

Q: The diagram showed the Inmon final Data Marts as being in 3NF, was this right or is it a typo?

A: That diagram is correct.  It’s rare, but in some cases you may want to have a data mart in 3NF.  For example, if you are not reporting off of it but rather exporting it to a csv

Q: is tabular model only available in SSAS in sql 2012? And is tabular model better than multi-dimensional model?

A: Tabular model is not available in versions prior to SQL Server 2012.  I have a blog post comparing it to multidimensional: http://www.jamesserra.com/archive/2012/04/sql-server-2012-multidimensional-vs-tabular/

Q: In your “Why use a Data Warehouse?” slide…the comment ..”plug holes in source systems”.  In your opinion, where do you draw the line between “plugging holes” and establishing that a fix/re-design of the application and its associated data architecture are the best approach before loading that data in the DW?

A: Plugging a hole in the source system only helps for future data entry, so you will still need to correct prior data issues via tools like SSIS or DQS. I have never had a case where I recommended a redesign of a source system – I just spend more time plugging holes and correcting prior data.  I don’t think a client would like to hear me say they need to rebuild their source system :-)

Q: Could you comment about BI and Microsoft cloud Azure? Does it perform?

A: I have to say I have not tried implementing a DW in Azure or have heard of anyone else doing that except for this case study.  One solution that seems interesting is Amazon Redshift.

Q: What books and/or internet resources you could recommend to start with DW?

A: Go with Kimball: http://www.jamesserra.com/archive/2013/05/ralph-kimball-books/

Q: Does tabular models handle ragged hierarchies?

A: No, but BIDS helper comes to the rescue

Posted in Data warehouse, Presentation, Session, SQLServerPedia Syndication | Leave a comment

Levels in IT consultancy

The Big 5 IT consulting companies (Accenture, Deloitte Consulting, PwC (formerly PricewaterhouseCoopers LLP), KPMG, Ernst & Young) have various titles given to consultants along a career path.  Wikipedia had good descriptions of the titles, and using that and a few other descriptions I came up with these levels (most firms have a subset of these):

Associate Consultant
An associate consultant is at the beginning of their consulting career and will typically do work to support the consultants and senior consultants – data collection and analysis, workshop support, etc.  An Associate Consultant can also refer to a day rate contractor at any level, differentiating them from an employee of the firm (e.g. Associate Managing Consultant).

Consultant
A consultant is ‘learning the trade’ within a specific domain of expertise.  A consultant is developing in most competency dimensions and work in different roles on different projects in a specific domain.

Senior Consultant
A senior consultant has developed a specialisation within a specific domain of expertise.  A senior consultant is capable of working independently as well as in teams.  A senior consultant is often responsible for the completion of a part of a project or activities for which he/she leads a small team.  A senior consultant is more client oriented and explores sales activities.

Associate
Business development, Issue owner, Client team manager, Relationship builder

Senior Associate
Engagement manager, Owner of day-to-day client relationship, Aligned to industry or functional domain

Managing Consultant
A managing consultant has started to excel in some of the competency dimensions.  A managing consultant is known for domain expertise and is capable of generating his or her own work and that of others.  As such the managing consultant is often responsible for business volume, through (add-on) sales and delivering a project.  A managing consultant can act as a team lead or counselor for other team members.

Senior Manager

Principal Consultant
A principal consultant has a strong business impact and is often part of the company’s leadership.  A principal consultant is capable of shaping a piece of business being the leader in a specific domain.  A principal consultant develops high-level business relations and high-impact projects.  A Principal is capable of leading large teams

Vice President
Focus on new business development and management, recruitment and career development of employees, and development of the companies national capabilities.​  Vice Presidents propose projects, navigate them through the sales cycle, and then manage project delivery (especially if the firm does not have a dedicated professional sales staff)

Senior Vice President

Director

Partner

CIO/CTO

More info:

Consulting Company Career Paths – A Comparison

The Future and the Big 5

Grade (consulting)

Posted in Consulting, SQLServerPedia Syndication | Leave a comment

Building an Effective Data Warehouse Architecture Presentation

I will be presenting the session “Building an Effective Data Warehouse Architecture” this Thursday, June 13th at 11:00am EST for Pragmatic Works.  Below is the abstract.  It will be a remote presentation that you can join online.  Details are here.  I hope you can make it!

Building an Effective Data Warehouse Architecture

You’re a DBA and your boss asks you to determine if a data warehouse would help the company.  So many questions pop into your head: Why use a data warehouse?  What is the best methodology to use when creating a data warehouse?  Should I use a normalized or dimensional approach?  What is the difference between the Kimball and Inmon methodologies?  Does the new Tabular model in SQL Server 2012 change things?  What is the difference between a data warehouse and a data mart?  Is there any hardware I can purchase that is optimized for a data warehouse?  What if I have a ton of data? During this session James will help you to answer these questions so your response to your boss will provoke amazement and lead to a big raise.  Or at least help to lead you down the correct path!

Posted in Data warehouse, Presentation, Session, SQLServerPedia Syndication | Leave a comment

Business Intelligence Maturity Assessment

Do you ever wonder how your business intelligence (BI) and data warehousing (DW) environment compares to other companies and what steps you should be taking to progress your analytics platform?

One of the first steps in any BI project is an assessment of your company’s readiness and capabilities for business intelligence.  That is where a Business Intelligence Maturity Assessment comes in.

A Business Intelligence Maturity Assessment determines what is the business need for BI, the ability of business processes and operational systems to support BI, the level of progress of current BI initiatives, future BI plans, and outlines the appropriate steps needed to make BI work.

A Business Intelligence Maturity Assessment helps companies to determine:

  • Business Need – Define what are the top business needs and demands for BI, as well as the benefits a BI program will bring to your company
  • Availability of Information – Whether the company has all the information they need to answer their current business questions and identify and easily support new business opportunities
  • Current Business Intelligence Capability – Assess the effectiveness of your BI organization and governance, identify current and future users of BI, assess the extent of transforming existing data into meaningful information, define standard KPIs and business transformation rules
  • Review of Current BI Systems – Including decision support, ad hoc query and reporting, analysis, forecasting, and data mining
  • Information Latency – How timely and up-to-date current information is and what gap analysis exists between current situation and future needs

The Business Intelligence Maturity Assessment should answer these questions:

  1. Business need for BI and benefits
  2. Review and assessment of existing systems and data
  3. Analysis and recommendation of current BI organization and user groups

A good, free tool to use to determine the maturity of your companies BI/DW initiative is: TDWI BI Maturity Model Assessment Tool.  A pay option is at: Forrester’s Establish Ongoing Processes To Assess Your Business Intelligence Maturity

Here are the three most common Business Intelligence Maturity Models:

The Data Warehousing Institute (TDWI):

TDWI_BIMaturity0609_lettersize

American SAP User Group (ASUG):

Untitled picture

Gartner:

Untitled picture

More info:

VIEW OF BUSINESS INTELLIGENCE MATURITY MODELS

Construct an Enterprise Business Intelligence Maturity Model (EBI2M) Using an Integration Approach: A Conceptual Framework

The Impact of an Effective BI Assessment

BI maturity models

Posted in Business Intelligence, SQLServerPedia Syndication | 2 Comments

IT recruiter’s response

I asked a recruiter to check out my blogs about consulting, and his response is below.  He had some good points on billling rates and what recruiters do behind the scenes:

I really enjoyed reading over your blog you have a lot of really good information in there. I especially enjoyed your articles about billing rates, It’s always interesting to see the topic from a consultants perspective. From what I read everything seems to be fairly spot on. The biggest trouble I run into is most consultants don’t realize that recruiting is a gamble of time and money. A good recruiter will fill 1 or 2 positions for every 10 orders they work on which means that 80% percent of the time we are not getting paid for our efforts so we have to make it up somewhere. An example of this is say my company gets a hot new order and puts 3 recruiters on it being conservative say each recruiter makes about $150 a day in a base salary that is $450 a day or $2250 a week a normal job order can stay open for up to 3 weeks or longer doing the math that is almost seven thousand dollars that my company is betting that we can fill the position with no guarantee that we won’t be beat by another firm or that the candidate won’t decide to go with another position. That number is just off of the base salary of 3 recruiters, it doesn’t calculate our other expenses that enables a recruiter to be effective at his or her job, such as paying for access to the job boards and our accounting and HR departments when all is said and done working on that one job order can cost up to 15k for a small firm with no guarantee of a return.

I also found the subject of if an agency should tell their consultants their billing rate or not to be interesting. I started off working for a firm that did primarily full-time placements where we had a fee of about 25-30% of the first year salary so the more money I negotiated for my candidate the more I would make in a commission. Working short-term placements tends to shake things up a bit, most consultants believe that because we are billing them out at $150 that they are worth $150 an hour which is only half-true. We do everything we can to pay market competitive salaries but there has to be some room to make it worth my time and my company’s investment. The other problem is a lot of the time a recruiter may not know what the billing rate is going to be a lot of the time a staffing company will have account managers on staff who do the majority to the salary negotiations, they will meet with the client negotiate the rate then tell the recruiter to go find a data analyst for whatever the predetermined rate was.

In my opinion you can go either way, but what I tell my consultants is that as long as they are happy with their current salary and they don’t feel taken advantage of then they shouldn’t worry about the billing rate. If they feel taken advantage of then they should address the issue and that they are free to seek employment elsewhere. I believe in being as transparent as possible so if asked about the billing rate by a consultant I will tell them, but it’s not something I would fixate about.

Posted in Consulting, SQLServerPedia Syndication | Leave a comment

SQL Server 2014!

Just when you thought you got a handle on SQL Server 2012, here comes the next version!  SQL Server 2014 was announced yesterday during the TechEd North America 2013 Day 1 Keynote.  It is due to be released in early 2014, shortly after the release of Windows Server 2012 R2 (aka Windows Blue Server).  A technology preview download (SQL Server 2014 CTP 1) will be available this month, and already released is the Microsoft® SQL Server® 2014 CTP1 Product Guide.

Unfortunately, this is a database-focused release with no changes to the BI-stack (SSAS, SSRS, SSIS).

So what are some of the cool new database-focused features?  Here are my favorites:

  • Hekaton in-memory OLTP: In-memory database technology that will provide breakthrough performance gains 10 times for existing apps and up to 50 times for new applications optimized for in-memory performance with no additional hardware required.  There will be a diagnostic tool that will suggest which databases and tables are the best candidates to run in-memory.  The “Hekaton” codename will fade and  it will be referred to as the SQL Server In-Memory OLTP Engine.  See In-Memory OLTP White Paper and Edgenet Gain Real-Time Access to Retail Product Data with In-Memory Technology
  • SSD caching: You can indicate that you want to use a SSD to extend memory.  Then SQL Server will automatically cache data in the SSD.   This will increase performance by extending SQL Server in-memory buffer pool to SSDs for faster paging
  • Updatable clustered column store indexes: This was recently introduced in PDW, where you will be able to load and delete data in existing column-store indexes.  They also have faster query speeds and greater data compression,  Yea, a new BI-related feature!
  • Online maintenance additions: You can rebuild a single partition’s index online as well as switch partitions using lock priorities
  • More secondaries for AlwaysOn Availability Groups: Increased from 4 secondaries to 8
  • Improved reliability for AlwaysOn Availability Group secondaries: The secondaries will now stay online if the primary goes offline
  • Use Azure VMs as AlwaysOn Availability Group replicas:  When adding a replica you can specify an Azure VM
  • Smart Backup to Azure: SQL Server determines whether to do a full or differential backup and how often to backup the transaction log to Azure
  • Have data/log files in Azure storage: You can have SQL Server on-premise but specify that the data and/or log files should be on Azure
  • SSMS Migration Wizard for Windows Azure Infrastructure Services: Easily migrate an on-premises SQL Server database to a Windows Azure Virtual machine with a point and click experience in SSMS.  The newly deployed database application can be managed through SSMS or System Center 2012 R2.
  • Enhanced Query Processing: Speeds all SQL Server queries regardless of workload
  • Windows Server 2012 R2: Combining with the new OS provides Increased scale, Network Virtualization, and Storage Virtualization with Storage Spaces
  • Enhanced Resource Governor: New capabilities allow you to manage IO
  • Enhanced Separation of Duties: Achieve greater compliance with new capabilities for creating role and sub-roles. For example, a database administrator can now manage the data without seeing sensitive data or personally identifiable information
  • Statistics improvements: Statistics are now maintained on the partition level

Click here to signup to be notified when the trial is ready for evaluation.

More info:

SQL Server 2014: A Closer Look

SQL Server 2014

SQL Server 2014 Datasheet

SQL Server 2014 Announced

Microsoft’s ‘Blue’ servers: What’s coming when

SQL Server 2014: Unlocking Real-Time Insights

TechEd North America 2013 Editor’s Choice: SQL Server

(Almost) Everything You Need to Know About the Next Version of SQL Server

SQL Server 2014 due out in late 2013, in-memory OLTP a big feature

Video Microsoft SQL Server High Availability and Disaster Recovery on Windows Azure

Video Microsoft SQL Server Future and Features

What’s New for BI in SQL Server 2014?

My 2 cents on SQL Server 2014

SQL Server 2014 announced

SQL SERVER 2014: WHAT’S VNEW FOR VNEXT

SQL Server 2014 CTP 1 Business Intelligence Summary

Posted in SQL Server 2014, SQLServerPedia Syndication | 1 Comment

Power View for Multidimensional Models Released

As a followup to my blog Power View for Multidimensional Models – Preview Available, the final version is now available via SQL Server 2012 Service Pack 1 Cumulative Update 4.

So you can now create Power View reports against multidimensional models (the initial release only worked against tabular models).  This is achieved through native support for Data Analysis Expressions (DAX) in Analysis Services multidimensional models.  Note that you will need to upgrade your Analysis Services instance and the Reporting Services add-in for Microsoft SharePoint Server (2010 or 2013) with this Cumulative Update to create Power View reports against your existing multidimensional cubes.

Also note that Power View in Excel (on-premise or in the cloud) is not able to connect to multidimensional models, but that ability is high on Microsoft’s priority list.

More info:

Power View connectivity for Multidimensional Models released

Power View for Multidimensional Models Released!

Configuring Power View Connectivity to Multidimensional

Analysis Services Multidimensional Now Works With Power View–And Why That’s Important

Posted in Power View/Project Crescent, SQLServerPedia Syndication | Leave a comment

Ralph Kimball Books

If you are involved in Business Intelligence, then the books by Ralph Kimball are required reading:

The Data Warehouse Lifecycle Toolkit: Practical Techniques for Building Data Warehouse and Business Intelligence Systems, Second Edition, 2008, 636 pages (Amazon).  Subject: Implementation guide.  Primary Audience: Good overview for all project participants; key tool for project managers, business analysts, and data modelers.  See Tools and Utilities.  Walks you through the detailed steps of designing, developing, and deploying a DW/BI system: Introduces the Kimball Lifecycle, managing the project, collecting the requirements, introducing the technical architecture, creating the architecture plan and selecting products, introducing and designing the dimensional model, designing the physical database and planning for performance, introducing and designing/developing ETL, introducing and designing/developing BI applications, deploying/supporting/expanding the DW/BI system

The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling, Second Edition, 2002, 436 pages (Amazon).  The third edition is due July 1, 2013 (Amazon).  Subject: Dimensional data modeling.  Primary Audience: Data modelers, business analysts, DBAs, ETL  developers.  Complete library of dimensional modeling techniques.  Starts with fundamental design recommendations and progresses through increasingly complex scenarios..  Has guidelines for designing dimensional models using real-world data warehouse case studies from retail, accounting, inventory, CRM, HR, financial, etc

The Data Warehouse ETL Toolkit: Practical Techniques for Extracting, Cleaning, Conforming, and Delivering Data, First Edition, 2004, 491 pages (Amazon).  Subject: ETL system architecture.  Primary Audience: ETL architects and developers.  Shows you how to plan and design your ETL system, choose the appropriate architecture from the many possible options, build the development/test/production suite of ETL processes, build a comprehensive data cleaning subsystem, and tune the overall ETL process for optimum performance

The Kimball Group Reader: Relentlessly Practical Tools for Data Warehousing and Business Intelligence, First Edition, 2010, 744 pages (Amazon).  Subject: DW/BI system design and development.  Primary Audience: A topical reference book for all project participants. A collection of recommended guidelines for data warehousing and business intelligence. Compiles the best articles written by Ralph Kimball as his team of colleagues. Covers the complete lifecycle—including project planning, requirements gathering, dimensional modeling, ETL, and business intelligence (BI) and analytics

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset, Second Edition, 2011, 696 pages (Amazon).  See Tools and Utilities.  The “bible” if you are using the Microsoft BI stack.  Think of it as a combination of the four previous books, taking parts of each, but making it specific to the Microsoft BI tool-set, where the other books are tool independent. The goal of the book is to guide the reader down the best path toward designing and building a successful business intelligence system and its underlying data warehouse databases using the Microsoft SQL Server product set

Kimball’s Data Warehouse Toolkit Classics: The Data Warehouse Toolkit, 2nd Edition; The Data Warehouse Lifecycle Toolkit, 2nd Edition; The Data Warehouse ETL Toolkit, 2009, 1628 pages (Amazon).  This is just a way to save money: instead of buying the three books separately you can make one purchase at a cost savings

Posted in Business Intelligence, SQLServerPedia Syndication | 2 Comments

Business Intelligence Requirements Gathering

There are three fundamental steps in building a data warehouse for a BI solution (see Why you need Business Intelligence):

  1. Identify the data the users interact with, or would like to interact with, via interviews.  Make sure to determine the granularity of the data that is needed, if Type 2 SCD are needed, and how often the data in the warehouse needs to be updated (i.e. real-time, daily, weekly)
  2. Develop a data model to provide the data at the appropriate granularity
  3. Determine how the data will be sourced to this data model

For the user interviews mentioned in step #1, a list of questions should be asked.  Here are the questions that I like to use:

  • How many users will be using the BI solution?
  • What types of users do you have? (Static report users, analysts, etc.)
  • How will they interact with the system? (i.e. interactive data exploration vs. static report consumption)
  • Where will the content be delivered? (Web, email, mobile, portal, etc.)
  • Is there a minimum response time?
  • How many users will be accessing the system concurrently?
  • Who are the key and/or influential users? (Their acceptance is very helpful in attaining overall acceptance.)
  • What are the goals of the users in implementing BI?  Why are those goals important to the business?
  • What is their tolerance for error?  Some groups, such as finance, generally have zero tolerance for error. Others may be willing to tolerate small inaccuracies in the numbers.  Data Quality is an issue but depending on the tolerance for error, may be delayed to some degree
  • What is the group’s willingness to work with BI?  Are they excited or skeptical?  Do they view it as a help or a threat?
  • What tools will be used?  Is training required or are users already familiar with the tool(s)?
  • What infrastructure is in place and what is required?  Infrastructure includes two components, physical and human.  Do you have the servers and hardware necessary?  Do you have the people and processes in place to support the solution?
  • Who will be doing the development?  Will it be done in-house by resident IT staff or will it be outsourced?
  • Do you have a BI Roadmap which outlines short-term vs. long-term needs and provides a guideline for all BI related activities?
  • What are your business objectives?
  • How would you interpret data set results?
  • How should the data you work with be organized?  Should it be organized by customer, product, geography and time?  Should it be organized by account, salesperson, distribution channel and month?
  • What are the hierarchies, rollups or aggregations used with these dimensions?  Do customers roll up to geographies that roll up to total?  Do products roll up to product groups?  Do salespeople roll up to districts that roll up to regions?  What types of summary reports do you work with?
  • What are the measures or facts you work with (e.g., revenues, expenses, balances, variances, percent growth, percent of total)?  How are they defined?
  • Do you “filter” the data?  Do you need data only at the top and bottom accounts?  Do you review the performance of only certain types of products?  Do you segment the data based on demographics?
  • How often do you obtain refreshes of the data?  Do you obtain them daily, weekly, monthly or quarterly?  Do you need it this often?
  • Is the data clean?
  • Do you receive the data in a timely fashion?
  • Do the tools you use support your requirements?
  • What types of things would you like to do that you can’t do today?
  • What is your data availability?
  • Do you spend most of your time on analysis or preparation for analysis?

The initial requirements are identified through interviews, with a representative set of end users.  In preparation for the interviews, it’s often useful for the end users to collect a sample of the reports they work with in reporting and analyzing their data as well as screen shots of any tools they use.

In making the transition from the results of the interviews to the draft data model, it might be helpful to work through a bus matrix.  In essence, it’s a systematic way to organize the users’ data requests using the columns of a grid to identify the facts the users interact with and the rows of a grid to identify the dimensions or qualifiers.

Once the business requirements document has been written up, the impact of this business intelligence initiative on the hardware infrastructure should be investigated.  Will additional servers be needed?  Will PCs need to be upgraded?  How will the network be impacted?  At this point, initial cost estimates and plans can be developed to deliver the desired end-user functionality.

More info:

Right-Sizing Business Intelligence

Data Warehousing Requirements Analysis, Part 1

BI Requirements Checklist

Six Steps to get good Business Intelligence Requirements

Kimball – Defining Business Requirements

Posted in Business Intelligence, SQLServerPedia Syndication | 2 Comments