The value of creativity cannot be overlooked in any field, especially business intelligence.  For many of us BI folks our days consist of continual problem solving. As we’re eating our morning Cherrios we are often contemplating that one silly problem that just hasn’t been solved yet… and suddenly all of those O’s spell Eureka! Solutions often come in ways that our current knowledge doesn’t support. It takes a creative brain to think diagonally and find solutions that don’t yet exist in textbooks, manuals, or blogs.

The Eternal Optimist

When times get tough and there are no solutions in sight you need that person who recognizes that there is ALWAYS a solution, you just haven’t found it yet. A positive thought process allows for creative solutions. There is nothing more stifling than to hear someone say “It can’t be done” or “I don’t think this is possible”. An intelligent optimist will recognize that to every logical problem there is a logical solution. This type of person doesn’t need the proof of specific past solutions, he is willing to take a chance and find an entirely new solution.

The Technical genius (AKA Nerd)

Wishful hoping and wishful thinking never accomplished anything until they met the technical genius that could bring visions into reality. Think of the relationship between an interior designer and an architect here. The decorator has no idea HOW that pool is going to get on the third floor balcony that overlooks the city she just knows it is an amazing idea and that it can be done. The architect will then build all of the required support systems to make the roof hold and he will even go one step farther to design a methodology to migrate all of those heavy building materials onto the balcony. Your technical genius will build the foundation for your BI system and will make sure that your silly creative people use construction materials that are extensible and durable.

The Organizer
Are we on track? Those artsy folks might not care and those nerds might not be aware. For this task you need that A type personality that sets and respects deadlines. A promise is meaningless if you don’t keep your word. When you promise other departments that you will have that new set of reporting capabilities ready by the end of the month your organizer will help you to earn a solid reputation by keeping your team on track.

The Politician

Oh how I wish this wasn’t the case (but I’m just a creative artsy nerd) but yes, you need someone to represent the greatness of your BI team to the masses. This person will know how to present himself to the public and field those not so friendly questions in a politically sensitive way. Your technical staff may inadvertently throw the public into the deep end and your artsy staff may appear too aloof, but your BI politician will exert the confidence and high level thinking that your board members can relate to.

The Communicator

Ah yes, the rare IT communicator. If you get your hands on someone who is efficient in this area I suggest you not let go! This person will translate your geek-speak into understandable English for your business users. She will also be a great resource to gather requirements for your technical staff. Your communicator will be a great lead for informal meetings as she can help to generate excitement and ensure that your staff clearly understands your business users.

Jack of all Some Trades

Many people on your BI team will possess several of these qualities. This is great! A collaborative organization requires that people be flexible and versatile. You shouldn’t attempt to hire one person for each role and place that person in a mold. Let your team experiment and find out their strengths while also engaging and offering development opportunities where they may be lacking. With that being said, it is also important to recognize if your team is missing one of these key strengths and to make an effort to fill the empty hole.

Good luck and happy developing!

1. Understanding What Your Client Values: Put yourself in your client’s shoes. An individual who has learned to tap into his or her emotional intelligence becomes skilled at removing attachment from individual experiences and imagining what it’s like to be someone else. This is an extremely important skill when you are seeking to add value to your client’s business. Your goal is to understand what your client values and to let go of your personal values to try and understand what type of deliverable would truly match the values of your client.

It’s easy for many business people to assume that everyone values a high financial return on investment (ROI), but beware the danger in making the assumption that this is all that matters because that is far from true. Values span far from dollar signs, even in the corporate environment. People may value simplicity, freedom, relationships, image, or practicality. Remember, you are doing business with individual people, not a corporation. Once you understand what your client values you will be able to provide a higher ROI that spans beyond money.

The easiest way to get insight into what your client values is to simply ask. Most people will be able to easily answer this question. It would be wise to ask follow-up questions to clarify the drivers for what your client values. You might ask “What things in life provide you that value?”, or “What actions or experiences don’t give you that value?”.  From these two questions you would learn countless things about your client’s personality and how they have organized their life to nourish the things that they value. A good business intelligence analyst will be able to take this information and apply those characteristics to help strengthen the business relationship and to deliver data in a way that matches the client’s values. If the client values simplicity then a clean data presentation based on charts and graphs is more appropriate than a page cluttered with numbers and raw data.

2. Gathering Feedback and Refining Requirements: When you present a report to your users/clients you are most likely relying on verbal feedback to gauge the accuracy of what you have produced. This type of feedback may range from concerns about data accuracy to specific requests about the color scheme or layout. Some users will be better verbal communicators than others and it is your job to bridge the communication gap so that satisfied clients become thrilled clients.

So how can you understand more about a client than they can verbally tell you? Pay attention to body language and facial expressions. Are their eyes narrowed? Are there lips pursed? Do they appear confused when they view their data? Avoid assuming that you know what all of these little details mean and use these clues to ask more questions in a non-threatening way.

For example, asking “Is the report organized in an easy to understand format?” is probably more effective than saying “You look confused”. The first style of communication will encourage more feedback and the second statement could easily put your client in a defensive stance which will hinder further communication.

3. Facilitate Meetings by Managing Emotions: A productive meeting always begins with a goal in mind. As attendee sizes increase so does the potential for topic derailment. This could render the meeting pointless if the goal of the meeting is missed all together. As the meeting facilitator and the business intelligence expert it is your job to manage the emotions of yourself and others in a way that aligns the productivity of the meeting with your goal.

Your goal might be to define a set of business rules that will be used for a report design. State your goal at the beginning of the meeting so that everyone is clear on your intentions. Often people will show up to a meeting with a need to vocalize their grievances. This may seem unproductive to you, but it is important that you first allow others to speak their minds while gently re-aligning the tone of the meeting with the meeting goal. On a psychological level people are not receptive to your ideas or opinions until they feel they have been heard and listened to. You can’t fake this! Control your own emotions of potential frustration and pay attention to the speaker 100%.

Most often it is appropriate to take note of the speaker’s concerns or view points and set a date and time to address them if action is required. Show respect to your clients by following through with the plan to address the concerns. This builds trust and respect and ultimately accomplishes the goal of managing the emotions of others. When you consistently take this approach you will find that follow up meeting run more smoothly due to the trust and respect that has accumulated between you and the client.

I hope this has been helpful. Thanks for reading, have a great day!

Navigate to the library section of your library tools and select “Open with Explorer”. This will open an explorer window that includes all of the files in your selected library.

Now navigate to the location that you want to upload the files to and mimic the same steps. Now you will have two explorer windows open, one that contains the source documents, and another that points to the destination.

From here you can simply drag and drop the files into the destination folder. If you have hundreds of items keep in mind that this could take several minutes to complete.

Thanks for reading and have a great day!

First you will navigate to the report location and select the check box next to the title.

Now select “Download a Copy” from your documents task ribbon.

When asked if you would like to save the file select “Save”. 

Name your file and save as type “BISM File” then select “Save”.

Now navigate to your production site where you want to upload the file. Under your Power View library select “Add document”.

Now browse to the location where you saved your bism file and select “OK”.

If the process worked correctly you will see a message displayed that indicates your document was uploaded successfully. Name your file and select “Save”.

Now you will see your uploaded file appear in your Power View library! Notice that the file Name is displayed for selection, not the file Title that you may have typed in the properties window.

I hope this has been helpful. Please let me know if there are any topics that you think would be beneficial to our BI community.

Thanks for reading and have a great day!

I personally feel that it is more admirable to be able to speak intelligently about your profession in a manner that anyone can understand, regardless of your industry. When preparing for an upcoming board presentation I came across a 40 page presentation designed to explain BI to non-technical people. Ahhhhh!

Remember…It Doesn’t Have to Be Complex to Be Cool! What is BI? Well, if you search this question you will find hundreds of complex explanations, however, don’t forget to remember your audience. Don’t make the mistake of boring them with long drawn out ramblings of ETL processes, data cleansing, and server maintenance plans.

Really, BI can be explained very simply; it’s the process of gathering data to present a single version of the truth so that data driven decisions can be made more easily.

Simple, right? Here are a few more: Data warehouse – a place to store summarized data from several end user systems. KPIs – a way to measure the progress of company goals. Scorecard – a collection of KPIs. I know, it may sound silly to simplify concepts to this degree, but it often accomplishes what is really needed in business conversations – solid communication.

It’s still easier said than done, so here are some things to consider before presenting your technical joys to non-technical people.

1. An intelligent co-worker of mine suggested considering how you might explain the technicality to a child. This is not with the intent of sounding condescending, but more to make us more aware of what we are saying and how we are saying it.
2. Remember, don’t try to impress others by using technical lingo, instead, impress them by demonstrating that you can live in their world as well as yours. This means being mindful and considerate of your audience.
3. Practice! After a long time of fumbling through my explanation of “what I do for a living” I finally have my elevator speech nearly perfected – ha-ha, but not really. Sometimes I find that I still accidently insult people by speaking too elementary or I frustrate people by speaking too technically. The important thing is that I learn more each time about how to gauge people and the approach I should take when speaking about my nerdy life.

One last thought to consider…From my experience “less is more”. My first goal in meetings is to accomplish solid communication. I do this by presenting condensed and non-wordy explanations. I have found that if the other party wants to know more they generally won’t hesitate to ask, especially when I have successfully presented the topic in a non-threatening manner.

Your communication success can be judged by paying attention to your audience. Are they staring at you blankly with little to no interaction? You may be speaking a foreign language to them. Are they interacting and actively engaged with the conversation? If so, congratulations! You have empowered them and potentially created a strong business ally.

• Power View
• Performance Point
• Reporting Services

If you are familiar with MS Office Suite’s Power Point and Excel’s Power Pivot add-on then you can begin to see the redundancy in Microsoft’s naming conventions. Can you say Power View,  Power Pivot, Performance Point, Power Point five times fast?  

Each of these reporting options have varying degrees of flexibility and limitations and are therefore appropriate for different uses. I can’t honestly say that I have a strong preference for any one of these options. I enjoy the higher degree of control and advanced capabilities that are available in reporting services but I also appreciate the great ease of use that Power View provides. I will avoid a long sales pitch that list all of the capabilities of each reporting tool ( you can read that on the MS website) and instead I will focus on the major contrasting features that I’ve noticed so far.

Power View Pros

• This tool is extremely easy to use and intuitive.
• The tool incorporates automatic formatting such as the addition of a scroll bar when the data extends beyond the page.
• Power View reports are visually appealing making presentations captivating and impressive.
• There is the ability to export to Power Point, enhancing the presentation usability of this tool.

Power View Cons

• There are no Excel export capabilities – It’s a sad day for Excel lovers. This tool will probably prove to be too limiting for users who would like to manipulate the data on other platforms.
• No Pie chart capabilities exist. This may seem like a minor offense…that is until you really need a pie chart.
• No drill down capabilities exist

Performance Point Pros

• Dual Y axis functionality exists in Performance Point. You may chart three different variables on your graphs using a left and right axis measure along with the horizontal axis measure.
• Decomposition tree functionality allows the user drill down to the details of the data numerous times.
• KPI capabilities for score cards.
• Pie chart capabilities.

Performance Point Cons

• Dual Y axis functionality is limited to percentage. Often it is beneficial to include three measures on a graph. You may want a count on the left axis, time on the horizontal axis, and a different count on the right axis. Unfortunately you will be limited to having a percentage on the right axis because Performance Point cannot aggregate a non-percentage number on this axis.
• Bar chart reports may extend to a second page. The user can’t easily tell that there is a second half of the report on a hidden page and if they do click to the second page the bar chart will auto-magically re-scale to fit the new set of data which is visually misleading.
• The layout is more limited than a Reporting Services report.

Reporting Services Pros

• Can deliver the report through SharePoint dashboards, as automated email attachments or links, or as an alert based subscription.
• Greater flexibility and less design constraints than other reporting options.
• Multiple data sources are easily combined into one report.

Reporting Services Cons

• Drill down functionality is not automated like it is in Performance Point.
• Not as easy to use as other reporting tools and dashboard creation is more complex.

As you can see from this assessment, there is no clear “winner” here. You will have to analyze your user base to determine their needs and decipher which reporting tool is the best fit. And remember, you don’t have to choose just one. A user’s SharePoint based dashboard can easily accommodate reports from all three tools.

Please feel free to comment. Happy programming!

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.

This budget data will display as an identical table and structure if no transformations are made during the import. As you can see, this is not an extensible design and it definitely doesn’t belong in a data warehouse without some alterations. As the budget years are added the ETL process will need to be changed to handle the new columns. This will also grow the table to an unmanageable size over a period of many years.

An easy solution to this dilemma is to use the UNPIVOT function to restructure the data and organize it in a manner that can be used in your data warehouse. After you have extracted the data in this budget Excel sheet into a staging table you will be able to reorganize it. Using our basic table example the UNPIVOT function would be:

The PIVOT function will take the rows in a table and pivot them into columns. So intuitively, the UNPIVOT function will do the opposite; it takes the columns in a table and pivots them into rows. After running the above UNPIVOT function your data set will become:

This seems easy enough, but remember each column that is to be pivoted into a row must have matching data types and sizes. When your data types and sizes don’t match exactly you must first convert all of your columns in your select statement to have a matching data type before running the UNPIVOT function. Mismatched data types could be the result of the source data being incorrectly assigned inconsistent data types. The easiest way that I have found to accomplish this is to first use a common table expression (CTE) to convert the data and then use your CTE as the source for your selection in your UNPIVOT function. 

Using the above example let’s assume that the 2012 data is a varchar(25) and the other yearly data is a float data type. The new UNPIVOT script would look like:

This method is easy to read and understand, so for coding clarity I like to use it. I’m sure there are multiple ways to solve this type of issue so please feel free to comment and share any other ways that you have found that work well for you.

I hope this has been helpful, have a great day!

Creativity is encouraged and rewarded

We get the opportunity to make other people’s jobs easier

We can work from home…or Hawaii even

We are responsible for understanding the engine, not just driving the car

For most of us working often feels like playing (because we are geeks at heart)

We get to embrace our inner nerd

We have our own secret language; Geek Speak

We are never bored; our environment is constantly changing

We are needed

Our skills are valuable

And as a lover of tennis shoes (and a hater of high heels) my personal favorite: Every day is casual Friday

In a previous post I discussed how to create and use SSIS templates. One issue that can arise when doing this is that duplicated GUIDs will be assigned to each copied package. This creates identification issues if you utilize SSIS logging.

There are three simple solutions to solve this problem:

1. Manually select the generate new GUID option in the ID drop down box in your package properties.

2. Right click the package name and choose the option to reset all GUIDs within the project. This requires the BIDS Helper add on.

3. Rename your package and a new GUID will be automatically generated.