Acme Corp is a national distributor of electronics and hardware to small and medium-sized companies.  Because size and cashflow prevent Acme Corps' SMB customers from carrying large inventories, they frequently stockout or request expensive last-minute air freight deliveries.  

Acme Corp recognized that by combining customer sell-out with product shipment data, they could apply forecasting models that would automate and optimize product replenishment for their SMBs.  This would deliver a better customer experience, while preventing stockout and creating new opportunities for optimizing product distribution logistics.

Unfortunately, although SMBs were happy to send till data to Acme Corp, the Domo implementation team did not have access to reliable starting inventories at the customer locations, so they approached me to design a process for deriving a starting inventory.

In this tutorial, we'll:

• build a dataflow that derives starting inventory
• learn why certain ETL (blocking functions) tiles have such a drastic impact on query performance and how you can optimize around them.

While this blog post is more developer-focused, it's important to recognise that these types of data normalization activities are the bridge step to building forecasting models and implementing workflow automation.

• Ex.  If we build a forecasting model around customer sales but don't consider available inventory, it's impossible to gauge whether the lack of sales is 'normal customer behaviour' or attributable to stock out.
• In a similar vein, before 'advanced analytics' can begin, we must carefully evaluate whether the available data represents reality.  If we don't apply domain expertise to the problem, we might dismiss rows with negative inventory as invalid or outlier measurements and therefore exclude them from our forecasting analysis.

With any project like this, validation is almost more important than the solution designed.  Make sure to identify and confirm assumptions with the domain experts before proceeding to the next step.

​In any case... on to the tutorial!

New data initatives and BI projects are a fickle thing.  You only get one shot at making a good first impression with end-users and senior stakeholders, and the last thing you want them saying is, "I don't trust these numbers."

• IF your data pipeline is held together with 'scotch tape and bubble gum,'
• OR you can't go on holiday because the world / your pipeline might go up in flames
• OR you're having data trust issues in your user community

 

• THEN it might be time to review your data pipeline and make sure it meets these 9 resiliency checkpoints.

If you have stake in project adoption but most of the checkpoints read like technical jargon, give me a call.  I'd be happy to sit with your developer team and co-review your data pipeline.

In an effort to escape 'Excel-anarchy' many organizations transition away from error-prone spreadsheet-driven reporting toward governed enterprise data warehouses which are expected to provide a 'single-version-of-the-truth'  for all reporting and analytics endeavours.   To that end, during the implementation process, bespoke business rules and constraints are applied to the data warehouse to enforce the consistency and validity of reported data.  ​

Although data warehouse applications (like Jet Report's Jet Data Manager) simplifies the process of applying business rules to data sets, the majority of implementations Onyx Reporting encounters do not report & monitor outlier records that fail data validation constraints.   Translation: you may be truncating data and not know it.  Double Translation:  You may be making decisions based on incomplete data!  That's not to say that supporting tools within the product ecosystem don't exist!  They just frequently go under utilized.

Every Data Warehouse is built on (un)declared Business Rules
The expectation that business keys (like Customer No or G/L Account No) will uniquely identify one member of a dimension is virtually universal.  Though a semi-obvious requirement, particularly when organizations are integrating data from disparate sources (multiple companies, a legacy system, web-based or unstructured data sources), conflicts between the expected versus actual data reality can arise.  Additionally, there can be a disconnect between expected values (as laid out by standard operating procedures) and the actual values recorded in the ERP systems (ex. every sales event should be attributed to a Salesperson or Geographic Region).

To close the loop, and prevent your organization from making strategic decisions based on incomplete information, system implementors must add measures and controls for monitoring and correcting records that fail data validation.

​Go forth!
Join our mailing list to be informed when we publish new articles.

Author Jae Wilson, lead data strategist at Onyx Reporting, partners with co-author Joel Conarton, director at executive and management consultancy Catalystis, to provide strategic solutions for data-driven organizations.

Are you ready to partner with us?

I was corresponding with a former colleague at Jet Reports US, Steve Little, and he shared this fantastic report.  The general gist of the report is to Print Mailing labels on Avery 5160 paper for Christmas cards!

If you ask any Jet Reports consultant, they'll tell you, writing the report is never the problem.  Formatting it for printing especially multipage printing is a pain!  

Steve uses a classic technique described in summary below:  

Count the number of qualifying records do display and identify how many records to fit per page.
Identify how many Pages to print, then replicate the report with NL("sheets")
On each page, use 'Excel Number Magic' to calculate which record to start on.
Then use a series of NL("nth") functions to show the 1st, 2nd, 5th, nth Record.  

It's really quite clever! 

If you're interested in honing your skills for the Jet Certification exams take a look at the use of:
 
NL("Filter") to create an Array of customers
     note the use of  the Filters= keyword to quickly select an array of Filter criteria


NP("Integers") to replicate numbers in the NL("Sheets") function.
    note the use of named cell references 

Steve was so excited about the report, he encouraged me to share it with my customers and fellow bloggers, so feel free to distribute it!

But, be advised, the report would be suitable for printing on one specific type of paper and label size.  

I personally am too lazy to make a version for A4 and A5, so I thought, why not find a dynamic way where the user can identify how many rows they want in their report (as opposed to a fixed 30 records per page).

At the end of it all, it's really just the same report with some fancy Excel math and expanded replication regions which we learned from grouping reports -- NL("rows=6").

DISCLAIMER:  I am not the originator of this report.  Steve Little and his team at Jet Reports US originally developed this report.  I'm blogging about it to provide a learning opportunity for up and coming consultants and report writers.

If you have any questions about the techniques used, please don't be a stranger.  Email me at Jae@OnyxReporting.com