Your BI vendor is working With you, not For you

In the past years I’ve had the opportunity to work on both sides of the BI vendor table, as both the consumer and the retailer. I’ve seen many projects succeed, some projects fail, and some projects that seem to surpass all expectations and all parties involved are delighted. The latter group of success stories all have one common factor: TEAMWORK

These successful BI implementations often end with kindred feelings of friendship and it all starts with the attitudes of the two parties involved. When you approach your BI vendor as a team mate it is easier to see that you both have the same goal: a smooth, successful BI implementation.

This means that both sides will need to do their due diligence. Roles are not always clearly defined and a team work attitude will encourage flexibility. Your BI vendor will not always be an expert on the content of your data, so be prepared to source internally for validation stages.

Consider the two options for communicating with your BI vendor below:

Teammate Attitude: Our data doesn’t look correct, let’s investigate to see where the problem might be.
Authoritative attitude: Your data is wrong, you need to fix it so that we can go live, this project is already behind.

The teammate attitude opens up the opportunity for further conversation. As a BI vendor I will be able to request specific examples of inaccuracies and I will feel comfortable asking for help if the data appears to be non-standard.

The authoritative attitude shuts down any potential for creative problem solving right away. The assumption behind this statement is that the BI vendor did something wrong and is aware of how to fix it. What super-techie, number-crunching, data-loving nerd would knowingly choose to display inaccurate data? Ahhh! Sounds like a nightmare for any data guru.

Remember, when you’re shopping for your BI vendor, you are shopping for a partnership, not a dine and dash vendor. The earlier you help cultivate that partnership, the smoother your BI implementation will go (assuming you pick an awesome vendor to work with).


Business Intelligence User Adoption: Baby Steps

In the past I’ve written a few posts that discuss dealing with the challenge of user adoption when introducing a new business intelligence solution.

Recently I have encountered some scenarios that have challenged my ego and my desires to produce solutions that I feel are extensible and efficient. I continue to learn more about the psychology that is involved when users are asked to abandon their old processes and adopt a new (and in my opinion better) way to accomplish their tasks. So here is my dilemma: I can either continue with my stubborn ideas of implementing best practice solutions or I can bend (can you do the limbo?) and design a process that caters to the users’ comfort levels.

Which one to choose? In this scenario you would need a significant amount of power and influence within your organization to override the user preferences for their work tasks (covering many departments throughout your organization) and replace those processes with your own design and plan. In addition, even if you happen to have this type of clout at your organization this iron fist approach will likely just cause resentment and rebellion – resulting in little to no user adoption.

Baby steps…You’ve done your research, you see multiple ways that tedious manual processes can be streamlined, but your users aren’t quite ready to make this leap. If you are like me this may be challenging. You may be thinking “really, you want me to implement a design that I don’t think is the best solution?” and my humble reply is yes, sometimes. I swallow hard as I write this because admittedly I am still having trouble accepting this concept.

Selling your solution to the users in your organization is a psychological process as much as it is a technical process. Consider this scenario: Users currently present KPI trends and results to executives based on manual entry into spreadsheets. They may incorporate some charts and include some complex macros that beef up the technicality of the spreadsheet, but ultimately they are still relying on manual entry. To me this method screams risks of human error, potentially lost data, lack of historical storage, and difficult data retrieval… Ahhhhh!

From my perspective I see the best solution for generating these KPIs in a consistent and reliable manner is to pull the data from the sources into the data warehouse and then generate automated reports or create dashboards for users and executives. (Seems obvious right?) However the reality is that for most end users this would be a dramatic transition, even if it ultimately means less work. They may also be worried about their jobs no longer being necessary. So, it may be going against everything you feel is “right” about a business intelligence design but I recommend taking baby steps here.

First you may want to try a simple solution that utilizes your data warehouse while still allowing the user to remain in complete control of the data that is inserted and reported on. If they are used to using a spreadsheet as their data entry point then a possible solution is to provide a SharePoint list that resembles their current data entry format. Allow the users to continue their manual entry while also becoming familiar with the SharePoint structure (or other collaboration BI tools you may be using). You will be able to extract this data into your data warehouse and generate reports/dashboards while also maintaining history.

The lesson: Don’t take away all of the users’ control at once. Trust is a slow process and ultimately you need your users to trust the data that lives in your data warehouse. When they are able to create useful reports by pulling the data from the data warehouse (that they essentially manually entered) then they will slowly grow an affection for the structure and will hopefully over time begin to ask you how other processes can be streamlined. You may be the BI guru, but ultimately you users will end up being the ones who set the pace.

Exactly what is a Slowly Changing Dimension (SCD)?

Just the sound of the phrase “slowly changing dimension“ can sound quite intimidating when uttered in a technical meeting, but you might be shocked at how simple this concept really is. In fact, it has nothing to do with shifts or alterations in the universe at all. J

If you are involved in data warehousing you should have a firm grasp of SCDs as they will be necessary to structure your data in a meaningful way that takes advantage of the capabilities of a data warehouse; specifically when you wish to store historical data rather than simply overwriting a record. So, let’s break it down:

Slowly Changing Dimensions: Dimensions in your DW that include attributes that will gradually change over a period of time.

There are three types:

Type I – Overwrite old values: The old record is completely over written by the new record. Type I is used when you have no desire to store the historical attributes of the record.

Type II – Create an additional record: The old record will be kept and a new record will be inserted. Type II is used when you wish to store the historical attributes of the record.

Type III – Create new fields: This type creates new fields in the record to maintain the history and the current values of the specific attributes in the record.

Below are examples of how to use each type. In this example the price of a product is changing.

Type I: The old values are over written

Product price in 2011

Product price in 2012

Type II: A new record is created.

Product price in 2011

Product price in 2012

As you can see in the above example there are a few problems with the structure of the table. When using a type II SCD you will need to add a couple of basic fields to effectively track the historical records. There are several possibilities, but the idea is to be able to differentiate between the two records. Also you may notice that there are two identical IDs for this record. That is OK. This is still the same product so a common identifier is necessary. We will be able to narrow down our selection criteria by choosing the active record. A more realistic type II record would look like the below.

Product price in 2012

Type III: New fields are inserted

Product price in 2011

Product price in 2012

There are many scenarios in business where each of these SCD types will be useful. The most common are types I and II because type III can only store a limited amount of historical data.

I hope that this explanation has helped to simplify your understanding of how to use slowly changing dimensions in data warehousing.

Data Quality Assurance – Will the Real John Smith Please Stand Up?

As humans we have the ability to scan multiple segments of data and make logical conclusions about the relationships that records have with each other. As an example, if we see the four records below we can easily conclude that all four records are indeed the same person.

Data Quality Assurance

Data quality assurance is the process of cleansing or scrubbing your data as it is extracted from your source systems and before it is inserted into your data warehouse. The ultimate goal is to remove “dirty” data such as duplicate or incomplete records. Multiple tools can and should be used to ensure that your data warehouse is as accurate and clean as possible.

If you data includes addresses you may choose to cross reference the city or province with the zip code or postal code. Many companies sell databases that include updated address information that can be used as a standard comparison for your address data.

Fuzzy logic can be used to merge duplicated records like the ones that appear in the example above. We can see that records one and four are a 100% match, so these can be easily merged. When comparing records one and two we see that two includes a middle initial and has an address discrepancy.

This is where your business rules will come into play. You may decide to disregard middle initials in your data cleansing process, or you may consider that the name field is one letter off and deduct 5% from the data match. Seeing that the address is one number off may also deduct the match score by another 5%. So the data quality score for records one and two comes to 90%. You will need to set your threshold to determine your business rules for merging two records. If your threshold is at 85% then these two records will be merged.

Now the question is to consider which record has the accurate data. This example is relatively simplistic because we do have two identical records. It is probably safe to say that the two 100% matching records have the most valid data. We can also look to see that the same birthday appears three out of four times, so we can safely conclude that the correct birthdate is 11/29/1964.

Data quality assurance is one of the most important things that will be used to construct your data warehouse and you shouldn’t underestimate the importance of investing both time and money into this portion of developing your business intelligence solution.

Whooo Are You? – Roles to Fit Your BI Team’s Personalities

I love to work with a colorful team full of varying personality types. Each individual adds new perspectives, different skills, and diverse experiences to the team. Your challenge, as the Business Intelligence Project Manager, is to learn as much as you can about each team member, and assign them tasks that will highlight their skills and help them to shine professionally.

Your people person: This individual is well suited for tasks that require great communication skills. They will be great at organizing meetings, working with department heads to gather requirements, and communicating with other team members about the project plan. You can task them with gathering information about what reports are currently being delivered to whom, and determining what information is needed from each report. This is a great opportunity to update report data. This person can compile the information needed and report it to your dashboard builders.

Your detailed oriented team members: These team members will likely be thorough when validating your new data warehouse.  Task them with documenting discrepancies and working with contractors to ensure the accuracy of data. They will also excel when building your BI dashboards with reports that are departmental specific. Have them communicate with your requirements gathers to build dashboards that fit the needs of your department heads.

Your influencer/leader: Most likely there will be at least one person on your team who aspires to be a leader and a manager. Don’t be intimidated by this person, but help them to develop their leadership skills by assigning them tasks that are aligned with their goals and aspirations. They will be able to organize many structural areas of the project while also alleviating some of the burden of managing the entire project alone. Have this person organize the meeting agendas and report the progress of the project to you and the rest of the team.

Knowing your project team can provide many advantages when implementing a business intelligence solution. By utilizing the talents of your team you will be able to lead the project from a higher level rather than micro-managing. Your team as a whole will feel proud because each individual has been given the opportunity to show off their individual talents.

Your Business Intelligence Roadmap

Ok, so you’re new business intelligence solution project is coming to an end. The developers have created a great new data warehouse and data marts are being designed as we speak. Your team is discussing training and user adoption strategies and then all of a sudden you have a meeting request from your vendor…”We’d like to meet with you to discuss your business intelligence plans for the future”.

You’re not alone if this type of scenario would catch you off guard. I wasn’t sure how to respond to such a request. We had barely had time to breathe since this project began, much less plan out how and when additional source systems would be brought into the business intelligence application. My team and I it too early to have this type of meeting?

As I pondered, I recalled that just days earlier some stakeholders had inquired about data that was currently inaccessible because it hadn’t been migrated into a data warehouse. I didn’t think much of it at the time, but now I could see that even the users were forward-thinking. They were thinking of the great possibilities of our new business intelligence solution without any prompting and it seemed that they were running this race quicker than me.

So, my final conclusion is this: It is not too early to create a roadmap for your business intelligence solution, in fact, the sooner the better. I know how tempting it is to want to relax after such a major project, but once the users realize how powerful the new data is for them they are only going to want more. Don’t be reactive to such request, but take a proactive approach by planning your business intelligence roadmap early.