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).

Advertisements

The Value of Doing Nothing (at work)

Today some coworkers and myself were standing around the kurig joking about how you couldn’t actually sit at the table in our office. The chairs (while totally cute, thanks Ikea) are made to be perfectly fitted beneath the tall table, leaving mere inches for thigh comfort.

20150819-170651.jpg

As we explored this table conundrum other questions of logic and functionality arose.

We began to discuss design methodologies and patterns that presented themselves as opportunities for automation. As we each chose to ignore the time briefly (it isn’t easy with 5 clocks on the wall) the conversation evolved into an incredibly valuable brainstorm.

These creative processes are often stifled when we are busy jumping from one meeting to the next. Try scheduling some “do nothing” time this week and see what problems seem to solve themselves.

Time is a created thing. To say “I don’t have time,” is like saying, “I don’t want to.”

~ Lao Tzu

20150819-170917.jpg

Using Leadership Training to Solidify Your Company Culture

When growing your organization you seek greatness, not mediocrity. You hope to recruit ingenuity, not bland worker bees. You desire to empower inspirational leaders, not just autocratic managers.

pic2

In growing companies the natural leaders are often appointed as managers without any official introduction to leadership styles, motivation techniques, or communication methods. This works great when a company is still in its infancy stage, but how do you maintain the culture at your company as growth increases and the need for qualified managers becomes exponential?

As you are probably aware, the attitude of a team is generally a reflection of the attitude of its leaders. Some people naturally gravitate towards fostering optimistic and trusting relationships, but other people, who may be brilliant in a business sense, simply aren’t aware of the concrete benefits of slight personality tweaks. You want these people in management positions due to their in depth knowledge, but you also want to protect the culture at your company, which is invariably a reflection of your attitude and beliefs.

p4

My recommendation is to tackle this scenario in two ways: 1. Formal leadership training conducted by a third party. 2. Internal leadership training designed by current leaders that have a solid understanding of the company culture that you wish to encourage.

Formal leadership training covers the topics that might be acquired in a college level leadership class. This will help your leaders determine their personal leadership style and will aid them in determining the different communication styles that are helpful when managing a varied group of employees.

There are also tried and tested techniques for inspiring and motivating employees, encouraging teamwork, and how to provide feedback in a constructive way. For these areas there is really no need to attempt to reinvent the wheel when experts have been studying these topics for decades.

The second level of training provides the opportunity for you to personalize the employee experience at your company. If there are any “hard lines” that you would not accept as behavior then it should be covered in this personalized training. As an example, I believe that yelling or expressing temper in a work environment is never productive, so I might emphasize that there is never a scenario where this is accepted at my company. There is generally a lot of focus on the customer experience within an organization, but I believe that clearly defining the employee experience holds just as much weight.

p3

It is important to remember that the attitude of your managers reflects your attitude. The attitude of teams reflects their leaders and managers. And the customer experience often reflects the attitude of your employees collectively. The end result is the result of the path taken so there really aren’t any major shortcuts if you wish to protect and foster your company culture.

Thank you for reading and please let me know if you have other ideas that will protect the company culture as it grows.

Build Dynamic Column Groups (and sub groups) in SSRS

I recently needed to create a SSRS report that allowed my client to group his data by choosing the group field from a drop down list. It’s actually quite simple to accomplish this, although not intuitive straight away, so I thought I would share the steps I took.

First create a report with a valid dataset. Include the fields you will be grouping on. I am using the Adventure Works 2012 Data warehouse. If you would like to follow along my query is:

SELECT top 3000
[TotalProductCost]
,[SalesAmount]
,[MonthNumberOfYear]
,[CalendarQuarter]
,[CalendarYear]
FROM [AdventureWorksDW2012].[dbo].[FactInternetSales] fs
JOIN [dbo].[DimDate] dd
ON fs.OrderDateKey=dd.DateKey

Next create a tabular design with your detail fields and a few blank columns.Image

Next Add a group by right clicking on the details group:

Image

Click on the expression builder:

Image

Paste this code into your expression:

=iif (Parameters!GroupBy.Value is Nothing,1,Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”,Parameters!GroupBy.Value)).Value)

Image

This handles the scenario where we don’t want to choose a grouping.

Now we need to actually create the parameter by right clicking Parameters and selecting Add Parameter:

Image

Allow nulls in your parameter and assign available values:

Image

The names in the Value field need to match the names in your dataset.

Image

Go back to your table and delete the extra group columns that were added when you created your groups:

ImageImage

Now right click the field and paste this expression:

=Fields(iif(Parameters!GroupBy.Value is Nothing, “CalendarYear”, Parameters!GroupBy.Value)).Value

Image

Image

Next you will create two identical parameters with the below names:

Image

Now right click on your first group, select add group, select child group:

Image

The child group settings will be identical to the parent one except you will update the code to read your second parameter:

Image

You can add multiple sub groups this way to really empower your users!

Next I will show you why we deleted the group column and kept the regular tablix column. We want to be able to hide the entire column if the user doesn’t choose it to group on.

A “grouped column” doesn’t have the visibility property available:

Image

But you can right click on your regular column and select Column Visibility and then click on the expression builder:

Image

In your expression paste the below code:

=iif (Parameters!GroupBy.Value is Nothing,True,False)

This will effectively hide your column if you don’t choose it to group by.

Now right click on your blank field and choose expression:

Image

This expression will be:

=iif (Parameters!GroupBySub2.Value is Nothing,1,Fields(iif(Parameters!GroupBySub2.Value is Nothing, “CalendarYear”,Parameters!GroupBySub2.Value)).Value)

Image

Your column heading will be the parameter label (not the parameter value):Image

And now the fun part. 🙂 You get to preview your report:

No Group Parameters Selected:

Image

One Group parameter selected:

Image

Two Group Parameters selected:

Image

All three Group Parameters selected:

Image

And that’s it! Now I just need to clean up the formatting to make everything pretty.

I hope this is helpful. Please let me know if you find an easier way to accomplish this, or any feedback you might have.

Thanks for reading and have a great day!

From Data Mart to Data Warehouse

Bottom-Up Data Warehousing

Have you been tasked with building a new data warehouse for you company? Is the existence of the data warehouse your only requirement? Are you having nightmares about the enormity of this task and the resources that will be needed? You may want to consider a bottom-up approach to building your data warehouse.

From Data Mart to Data Warehouse

Rather than spending months gathering all of your requirements and then proceeding to build a massive solution (using standard waterfall development) a bottom up approach will begin with basic data marts designed for functional areas of your business.

Develop Functional Data Marts

Data marts can be designed and developed in a relatively short amount of time. This means that your business users can start benefiting from reporting solutions in a matter of weeks rather than months. You will receive multiple benefits that are expected when using an agile development style.

Document Exposed Requirements

One of these benefits includes gathering data warehouse requirements through exploration. Users will be able to explore your reporting solution and they will be excited to browse the data using self-serve data tools. Be sure to pay close attention to the questions that your users begin asking. These questions are sure to become requirements for your data warehouse solution.

Once users are comfortable I recommend that you encourage forward thinking by helping to prompt “what if” questions. As an example, “What if you could view sales trends on the same chart as product enhancement releases? Would that data be useful?” You may even choose to accomplish this in official requirements meetings or just have short conversations as you deliver new reports.

Create Conformed Facts and Dimensions

This step will not happen until well after users have successfully adopted your reporting technologies. Quality documentation is key to accomplish this task. Each existing data mart will need to be cross referenced to identify commonalities between your measures and dimensions in your various data marts.

Conformed dimensions and facts will be used to create consistency for the foundation of your data warehouse. You will ensure that measures have identical definitions in every data mart that they exist in.

Data Warehouse Development

Armed with your detailed requirements documents and data mart documentation you are now ready to establish an official data warehouse. You have already done all of the heavy lifting with your data mart ETLs. Now you will just build the structure that merges data from multiple functional areas in your business. The provides the much sought after “One version of the truth” that so many companies are seeking.

 

Thanks for reading!

 

How to Build an Amazing BI Team

The Creative Genius

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!

 

 

 

 

How to Easily Move Multiple Files Across SharePoint Sites

If you have multiple files that you need to move in SharePoint don’t worry, you won’t be forced to select and download each one individually to accomplish this task.

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!

How to move Power View Reports (or other content) Across SharePoint Sites

If you have a development SharePoint site and you need to transfer a file to your production site this is a relatively easy task, however, you will follow different steps if you need to move multiple items at the same time. Please read my post “How to Easily Move Multiple Files Across SharePoint Sites”.

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.

7

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!

How to Translate Your Geek Speak (and why you should try)

I’m going to make a broad assumption that if you are a fellow nerd (who grins after coding for eight hours straight) you may not be the type of person who works on your own car. Have you ever taken your car to the garage and experienced the mechanic speaking a hundred miles an hour about A-Brackets,  A-Pillars, Alternators, and Ammeters? Yes, No? Well, I have, and sometimes I wish they would consider that I might not speak their language. I really won’t be offended if you simply tell me there’s a hose that has a small leak in it and it should be replaced.

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.

Reporting Options for SharePoint 2010 Dashboards

The Microsoft marketing team must have some sort of obsession with the letter “P” and the words “Point” and “Power” because I have found myself tounge tied multiple times when attempting to recite the three main reporting options for SharePoint dashboards. They are:

  • 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!