Episode 3 - Digging

Where to start..

Here's a typical reporting journey and for those who like parcour we're keeping it simple and going agile with a user story.

"I want a report that shows me how many activities my staff carry out alongside their turnover on sales so that I can see if there is commonality between the activities and the volume of sales"

Sounds reasonable and then you start thinking about where the data is held and try not to throw yourself out of the second storey window to make a quick getaway:

Calls - well they're on the PBX so the only way we'll get at those is through the system logs

Chat Messages - you've got no chance! Whatsapp, Teams, Skype (if you still use that), Signal, Facebook groups.... Let's not look at that yet because it will make my eyes burn

Emails - oh thank god for that, everyone has to use MS email because someone set a corporate standard, but it's all in o365 so I can't get at it without someone setting privacy standards before we open up the permissions in the cloud.

Meetings - Could be zoom or teams or webex because there are a number of third party providers who each have their own preferred platform.

Sales - We have a standard ERP across the entire operation so at least I can get that far.

So if you started loading this into Excel, Power Bi, Sisense, Tableau etc. it would take probably less than 2 days for you to reach the point of filling a bucket of water and pouring it over the head of the manager who suggested this project, but fear not there is a "proper" way to do this and so let's shine a light to help - like a ray of hope.

Despite the modernisation of terms to make things sound different the basic architecture of a good, enterprise-level reporting system consists of four layers.

  • Some Source Data - any system you want to connect to so you can pull data out

  • A Data Repository - Data Lake or Data Warehouse or both or a hybrid with parts from each is a place where you store the extracted data and turn it into a meaningful dataset. This is where the Kimball stuff happens (Extract, Transform and Load).

  • A Modelling Layer - After you've built your reporting data this is where you define relationships, security, calculations and all the other bumf that you don't want the users to change and misreport.

  • A Presentation Layer - This is the pretty bit that makes all the executives say "ooh" and "aah" as they smile at your timeline based trends and outlier analysis.

Watch out for the next session where we'll be talking about getting data from source and the importance of staging strategy.

1 view0 comments

Recent Posts

See All

So this week I've worked on the Azure domain and cracked adding a VNET ( virtual network ) and managed to get my laptop connected over VPN. Having never been a network engineer, I'm really pleased and

So we've built this site in Wix. We've also integrated it with our Microsoft Azure environment and over time will be using this to show you what we can really do and give you content to play with. Why