Quench your thirst for knowledge

Like many analysts I’ve always had it in my mind that if I only had some new data to fill in a ‘data jigsaw’ then I’d be able to add much more value to the business.

Frequently this information isn’t held in the Cubes.  This situation is not down to BI failures, moreover, business operates at a faster cadence to typical BI projects.

The ‘If I only had this’ questions arise some months after the last iteration of BI.  The last thing the business wants to do at this point is start another project.

What do we do?  Historically the answer has been to log a change request that will be picked up in 12 months.

With the advent of self-service BI we can answer this question differently.

Using Power Pivot we can consume data held in the cubes and mash it up with disparate data that has not yet been fully augmented with the existing BI model.  This small capability addresses the ‘information thirst’ that we see in dynamic businesses.

I would even go so far as to say some types of requirements can be addressed more easily.  For instance, ask your Database specialist to give you a count of the number of new customers and ask them for the Sales that these customers have made.   This is a common business requirement and happens to be one of the most expensive database queries.  We can answer this question with ease with the modern in-memory tools.

I’ll be honest with you.   BI Self Service isn’t as easy as some people are making out.

It’s probable you’ll have to build your skills in the following areas :

Data Modelling
Learning a new query language

I recognised this trend 3 years and decided back then to start the journey as the potential value was too great to ignore.

With these skills I created this new information in less than 30 minutes.

newc2

I created the data model below from the Adventure Works sample database.

Model

And used this expression :

=CALCULATE(COUNTROWS(DimCustomer),
FILTER(DimCustomer,
CALCULATE(COUNTROWS(Sales))>0 &&
CALCULATE(COUNTROWS(Sales),
Filter(All(DimDate),DimDate[FullDateAlternateKey] < MIN(DimDate[FullDateAlternateKey])))))

It looks complicated but all that it’s doing is counting the customers and filtering out those customers who have made a sale in the past, thereby, giving us the new customers for whatever context is active.

It can be hard to produce these queries as you have to think about things differently.  It takes time and practice but I can confidently tell anyone who is interested “If you’re capable of writing a VLOOKUP function in Excel then you have the aptitude to learn this new world of analytics.

I’m providing a training day for the people at the start of this journey on the 17th September at Media City near Manchester.

You can find out about the training day at leehbi.eventbrite.co.uk

Come and join me to discover the new way of tapping into data to produce amazing insights.

About Lee Hawthorn

Data Professional
This entry was posted in Analytics, Power Pivot and tagged , . Bookmark the permalink.

Leave a Reply