Business Intelligence in 2014

As you might be aware we are preparing for our upcoming User Group Meeting in Mississauga April 9th, and I always try to have something new to show existing customers.

So if you’re wondering what is business intelligence in 2014; keep reading.

1)      Interactive, Time-Based Maps

blog 7- map

Power viewer is a new feature in excel 2013, it does require you to ‘build’ the data-model so it can’t be deployed as a product, but it automatically geo-codes your data to bing maps and presents the capability of adding interactive filters and a multiple trend charting.  This graph shows how where and how sales have changed geographically over time.

2)      Pareto Analysis Histograms

blog 7 - histogram

Technically this feature has been around for a while but it’s much faster in Excel 2013 and very useful for businesses asking the 80/20 ‘rule’ question.   In this example for instance, it might be very interesting to know that 80% of all orders have order quantities less than 20, and over 50% just ordering 1 part.  The next analysis questions would be

a)      What are my price points?

b)     Are the customers ordering 1 line or multiple?

c)      What is the order frequency?

d)     What is my setup time or is my setup time and administration time included for processing 1 part?

e)      What is my profitability for orders under 20 pieces vs order?

All of this is possible with BI analytics, you just need to know what questions to ask.

 3)      Animated Timelines

This is another example of the power viewer functionality.   Here it graphically depicts when sales start to increase by the size of the bubbles growing.   When you see it live, you can easily pinpoint that mid to late February there was an order spike in all 3 markets.

blog 7 - bubbles

4)      Key Performance Indicators

blog 7 - kpi

Although the example above looks like conditional formatting it is not.

This functionality is new to power pivot that allows you to create your own KPI’s based on calculated fields.  It’s a bit tricky to implement but the true power comes from being able to click on the slicers and the goals and thresholds start to change dynamically

5)      Multiple Data-sources

blog 7 - overview

This functionality was possible before excel 2013 but only recently did we start to implement it.  This example illustrates how you can combine for instance

a)      Sales history

b)     Order Backlog

c)      Budget/Forecast

d)     Opportunities

Half of the data is coming from CRM, the other have is from Dynamics NAV, but the true value is being able to see the trend of what did happen and now what is planned to happen.  My next iteration of this report will be to predict further into the future using the FORECAST and REGRESSION functions that are available in Excel

6)      RSS Feed

Blog 7 - rss feed

The PowerPivot gives you the ability to connect your data to RSS feeds.  Today I was looking for a WalMart feed, which I think would truly give perspective an insight.   But in general you want to get your customer’s information and either import into your business system or connect to it.

This gives you the ability to

a)      Look at overall buying trends

b)     Prices of equivalent products

c)      Market Share

d)     Inventory Levels

e)      MSRP, etc.

Blog 7 - southmedic

This is an example from one of our customers in which we created new entities in crm to store customer data.  This customer sales history was then used in the bi analytics and dashboards that you see here in crm.

Remember that if this seems interesting, we will be presenting it live at our User Group Meeting in Mississauga April 9th, please go to our website and register.


Mike Fontaine

March 11/2014