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 his article "Choosing the Right Chart for your Data," Brian Petersen (VP of Professional Services at Jet Reports) writes: "Data is the foundation of effective business.  ... Being able to quickly read and analyze your data enables you ... to understand how a particular set or group of facts contributes to your overall success and steer your decisions proactively."
He then goes on to describe several common charts including:

• Area Charts
• Bar Charts
• Scatter Charts and
• Stacked Column Charts

Read the full article here:

For more seasoned analysts, the challenge is less about finding the right chart so much as laying out a dashboard that effectively communicates a broad scale of both summarized as well as detailed information. As we delve into optimizing dashboards, we move away from technical or domain expertise and transition toward questions of User Experience and User Interface.

For these projects, I'll leverage knowledge gleaned from Stephen Few's guide to dashboarding -- "Information Dashboard Design" which was heavily influenced by Edward Tufte's seminal work "The Visual Display of Quantitative Information".

1) "Brevity is the soul of wit" -- Do not exceed 1 page.
Any CxO will describe the perfect dashboard as an interactive report where they can see all the important information on one page. Translation: Edit. Edit. Then edit again. Examine how much excessive detail or decoration you can pare away without supplying inadequate context.

​Your final product shouldn't require scrolling, changing tabs, or (ideally) a legend.

2) How good is good? -- Provide enrichment and context through comparison.

• Same measure at a point in the past (Last Year or Last Month)
• Target (Budget or Forecast)
• Relationship to future target or prior prediction (% of annual budget or forecasted budget)
• Benchmark for norm (company average or industry standard)
• Separate but related measure

6) Organize information to support interpretation and application

• Organize groups according to departments, entities or use
• Co-locate and subtly delineate related data
• Support meaningful comparison while minimizing meaningless comparisons

My favorite feature of Few's book was his analysis of sample dashboards, wherein he described, not only the flaws in various dashboards but also modeled various alternative ways of presenting the data.  For dashboard developers this analysis would prove invaluable for sharpening our critical eye, but also provide inspiration for what dashboards can (or shouldn't) look like!

Content and images from this blog post were taken from Stephen Few's Information Dashboard Design (buy it on Amazon).

Check out our blog at www.onyxreporting.com/blog or sign up for our weekly newsletter: "The Data Dump".

If you need support knocking out a batch of reports or want to customize your Jet Reports cubes to include some new comparative measures, our services team is amoung the best in the business.

​This two-part article describes a 6-step framework, Vision, Mission, Strategy, Goals, Initiatives, Actions (abbreviated VMSGIA) executives can leverage for refocusing business innovation around strategic goals.

Onyx Reporting uses this methodology during the needs assessment phase of larger business intelligence (BI) and data strategy projects to prioritize, frame, and deliver high-value analytics solutions.  Over the next two posts, we will:

• Examine a common way organizations derail new BI initiatives
• Explore examples of the successful alignment of data initiatives with corporate strategy.

By Jae Myong Wilson - keep abreast with BI strategy from the comfort of your inbox.

​Piecemeal design produces hamstrung data strategy teams.
As organizations evaluate new analytics tools, the question "Do you have any sample dashboards?" invariably arises. Though it seems a reasonable request, in most cases, it derails the data project team; because the process rapidly devolves into piecemeal design and never recovers. "I don't like the layout of this report" "How much would it cost to change this feature?" "How many hours would it take to add a new calculation?"  
 
Instead of proactively designing a comprehensive solution, the data consultancy is relegated to reactively implementing fixes.

​In this article, we'll use Onyx Reporting's workshop, "The Strategist's Journey" as a framework for addressing the question "What can BI do for me?"

Last week I received an email from an IT director and senior financial controller asking, "How can I convince the executive suite to invest in a BI project?"  The email went on to hint that there were limited resources available to allocate to new initiatives. 

Ironically, when analysts describe why BI projects fail, they never mention the not-so-insignificant challenge of convincing management to green-light new BI initiatives.  If my experiences are any indicator, the challenge stems from technologists using technologists' language to champion projects to business people who evaluate based on business merit. ​

The age of big data is here and it's not going away. Do you have the analytic tools to remain competitive?  
At first, traditional bricks and mortar stores were only competing against each other; then, along came digital stores who put on the squeeze by offering lower prices. But that was back in 2001, and today even digital stores are feeling pressure to adopt increasingly data-driven cultures in order to remain competitive against 'the next Uber' or mega-conglomerate.  With the landscape constantly changing, it's hard to know how your company stacks up.
This week I came across Daniel Egger's 20-Item Checklist for Data-Driven Companies as part of my studies for Coursera & Duke University's excellent Business Metrics for Data-Driven Companies course.
The checklist includes:

• Use a model that modifies inventory levels at the store level by region, season, and day of the week to optimize days inventory against opportunity cost.
• Track churn rates and have a program to contact former customers who have "gone quiet" and are potentially lost - and provide incentives for them to return.
• Visitor "conversion" on websites is tracked at two levels - voluntary registration and first sale.
• Special programs to distinguish, reward, and retain the highest-level recurring revenue customers - "Whales" as they are called in the gambling industry.

Even within this subset I saw analytics solutions I've implemented for clients of Onyx Reporting. If you're wondering how cubes and data warehouses play into all this - consider the challenge of analyzing your CRM data (customer conversions) with AR (Dynamics NAV) or trouble tickets (ZenDesk) and Point of Store Sales data (LS Retail or POS).
If you're struggling to usher your company into the era of data-driven analytics, Onyx Reporting is ready to help.

Most companies are not running the current version of Jet Essentials.
In fact, as I write this blog i realize, I'm not on the current version either!

To find out which version you're running, open Excel and navigate to Excel > Jet > Information > About
The current build is:  Jet Essentials 2015 Build 15.0.15063.0

While not all revisions of Jet release new functions, many builds stamp out known bugs or introduce code that optimizes report performance.  If you've got significantly older versions of Jet, it's very likely that you’re missing out on powerful tools that were introduced to make Jet Reports easier to use including the Table Builder, Type to Filter in the Function Wizard and Drag and Drop with the Jet Browser

The Annual Enhancement Program entitles Jet customers to three indispensable services:

Updates to the current release of Jet Essentials:  
http://kb.jetreports.com/article/AA-00981/0

Access to free technical support at support@jetreports.com  
See http://jetreportskb.host4kb.com/article/AA-00909/0/Jet-Essentials-Guidelines-Regarding-Support-Versus-Billable-Work.html for guidance on the difference between Billable services and Technical support.

Updates to your Jet license to change viewers and designers.
 
If a company is current in the Annual Enhancement Program, they are entitled to download and install the most recent build of Jet Essentials.  Period.  Full stop.  If you paid your annual maintenance, there is (almost) no reason to not be on the current release of Jet Essentials.  To stay informed on Jet releases, I subscribe to:  Release Notes for Jet Essentials 2015.

It’s a pretty safe assumption to say that if you’re on NAV with 4.01 executables or higher, you’re compatible with the current version of Jet Essentials.  Check the article on NAV compatibility:  http://kb.jetreports.com/article/AA-00484/0/What-versions-of-Navision-are-compatible-with-Jet-Reports-Essentials.html to be positive, but most companies are in the clear here regardless of what ERP customizations are in place.

The version of Office does not matter! (Usually)  Again, check the article on system requirements to be certain:  http://kb.jetreports.com/article/AA-00446/0/System-Requirements-for-Jet-Reports-Essentials.html but most companies have their users on Office 2007 and higher or Windows 7 and higher, so compatibility is usually not a problem.  If you’re on XP, you can use Jet Essentials 2013.  Please note that Office 365 users currently must use the Desktop version.

Your reports will work in the new version of Jet Essentials.  The Jet Reports developers aim to keep Jet functionality consistent so that reports are forward compatible as users upgrade versions.  There was a huge overhaul to the Jet code in version 7 to version 9 to improve performance, and then again in the releases between 2012 and 2013, but at the end of the day there were 3 things that changed for end users that might cause a report to stop functioning.  

 *If you can demonstrate that your reports stopped working due to a change in functionality from Jet Essentials v7 or older to the current version of Jet 2015, Onyx Reporting will help you get your reports functional for free.  

** To stay informed about Jet releases or have articles like this delivered to your email twice a month sign up for our blog updates at:  http://eepurl.com/bblABH

Before you pay for consulting services, please ask if they are using Jet 2015 Certified Specialists or Jet 2015 Certified Professionals, because it’s currently the only unbiased way to benchmark a consultant’s knowledge of Jet products.  If you’re paying for training, find out if the trainer has a current Jet Certified Trainer (JCT) endorsement from Jet Reports headquarters.  JCT’s have been vetted and sponsored by Jet Reports as having mastery of the Jet products as well as demonstrated proficiency as trainers.


***For more information on the certification process, End users, Partners and resellers should visit http://jetreports.com/customers/certifications.php.  The JCS exam tests for basic proficiency with report writing, while the JCP certification expands to include mastery of the Jet product as well as competency with issues around installation, troubleshooting and configuration.

Some last questions or thoughts to consider
If you’re using on-premise NAV 2013 or 2015 has your support technician approached you about using a Direct-To-SQL connectors or are you still reliant on slower web-services connectors?

If you have many designers, multiple datasources, or are using Jet Enterprise, have you evaluated using the Jet Administration Console to enforce uniformity in datasource settings throughout your organization to ease report distribution between users?

If you’re evaluating upgrading versions of your ERP, there’s no reason to wait to upgrade Jet Essentials.  They are totally unrelated unless the new version of Jet doesn’t support your old ERP.  



I am a former employee of Jet Reports in Portland, OR, and my consulting company, Onyx Reporting, was set up to provide report writing, consulting and training services for Jet Reports customer and partners worldwide.  I have achieved current JCS, JCP and JCT certifications and would be happy to help your team with their Jet Reports journey.  


Please reach out to me at jae@onyxreporting.com if you have any questions.


------ copied from http://kb.jetreports.com/article/AA-00981/0 -----


Jet Essentials 2015 Build 15.0.15063.0

Changes since 15.0.15019.0

Fixed issue where the Jet Service Tier may crash if an older client application connected to it.

Fixed issue where Essentials or the Jet Administration Console may only be able to connect to the Jet Service Tier using the IP Address but not the host name.

Fixed issue where SQL Server-based data sources did not correctly filter Unicode characters in a Latin collation.

Fixed issue where a report may not generate correctly when "Enable batch function execution" is enabled in the Application Settings and very large text values are retrieved.

Fixed issue with the Dynamics NAV SQL data source could return incorrect data when certain FlowFields were used in the same report across multiple companies.

Fixed issue with the Dynamics NAV SQL data source where Option field values (as opposed to captions) would be returned for link fields.

Fixed issue with the Dynamics NAV SQL data source where the tables and fields may not load correctly when using SQL Server 2012.

Fixed issue with the Dynamics NAV SQL data source where tables and fields may not load correctly if a failure is encountered when loading Advanced Dimensions.

Fixed issue with the Dynamics NAV SQL data source where a sort-by-sum would not sort correctly when returning record keys on a table with a multi-field primary key.


**** IMPORTANT NOTICE ****

If upgrading to any version 15.0.14329 or higher from a version *PRIOR TO* version 15.0.14329...

ALL previously-installed Service Tier components (Jet Administration Console, Jet Dashboard Builder, Jet Web Service, Jet Service Tier)

must be MANUALLY UNINSTALLED (using the "Jet Setup.exe" file included with that specific release) before running the new installer.

 

 

Welcome back from the holidays!

I wish I could say I put all work behind me, but instead I spent some time working on a few projects I've had on the burner, including exploring how Power Pivot and Power View might affect our work with Jet Enterprise and 'formal' BI projects.

Through blogs and interactions with customers and partners, we're hearing murmurs questioning whether data warehouses can be replaced by Excel's Power BI family.  In this blog I demonstrate that since Power Pivot can recreate a lot of the functionality that Jet Enterprise's dimensional projects leverage, instead of fearing or demonizing the disruptive potential of Power Pivot, we can welcome it into our workflow.  This tool can empower business analysts to prototype the cube customizations they desire, as well as act as a stop gap for analysis and reporting while cube customizations are implemented.  

For anyone who's seen a Jet Enterprise Demo, you've probably seen something like this screenshot above.  It's Excel.  Pivot tables and charts connected to slicers which will -- as if by magic -- tell you what your Profitability was across XYZ set of dimensions.

Yes.  Ultimately that's what we're going to get you to.  But for your Tech Support team, DBA's or analysts responsible for implementation, there's more to it than Excel.  

Excel, Pivot Tables and / or Jet Essentials are really just the Reporting Tools (it's what we use to get the data out!)

Jet Enterprise is the Business Intelligence offering from Jet Reports.  Once implemented you'll have a SQL DataWarehouse and Cube that are optimized for reporting (as opposed to your transactional ERP - Dynamics NAV, GP, etc.) which is optimized for recording the daily transactions in your company.

While the concept of BI is nothing new, Jet Enterprise's value proposition is two fold.

The Jet Data Manager (JDM) application is effectively a no-code GUI which leverages the MS SQL stack to create a SQL Database and Cube in SQL Analysis Services.  It does all the Extraction, Transformation, and Loading (ETL)!

The real advantage of the software is that it allows BI professionals to focus on what they do best - model and design the best Data Warehouse and Cube solution without having to worry about writing perfect SQL code.  In fact entire cubes can be developed from start to finish with virtually no code!  

In short, the JDM product can be condensed down to two words:  Speed and Flexibility.
Instead of extended periods of development where the DBA's have little to no interaction with end-users and stakeholders because they're busy coding your latest set of requirements, you could have same day results where minor or even major changes to the structure of your Data Warehouse and Cubes can be reviewed.  

Actually, depending on the project, my preferred way of delivering BI customizations on smaller projects is with you right there with me on the phone, giving me the feedback I need to make sure you get the results you're looking for!

The second piece of Jet Reports' BI offering is the Cube Store - a repository of prebuilt projects which when pointed at your ERP will produce a Data Warehouse and set of Cubes specific to AR, AP, Sales, Inventory etc.  

Having considered all the Enterprise customers I've worked with, depending on your degree of customization, I'd estimate that roughly 75 to 90% of your analysis needs can be met with Jet Enterprise installed off-the shelf. 

A typical Jet Enterprise install takes 2 to 4 hours.  During that process we'll install the Jet Data Manager Application, download the Cubestore project that matches your ERP and then hit "Deploy and Execute".  Half an hour later -- depending on how much data you have -- you'll have a finished cube ready for analysis in the front end tool of your choice: Jet Essentials, Pivot Tables, SSRS etc.

If you were to recreate one of our projects from scratch without the JDM ... let's just say it could take hundreds of hours and would probably require a team of people with multiple specializations because 

1) you need someone who knows your ERP's data structure inside and out
2) you need a specialized DBA who can build a Staging Database and Data Warehouse as well as write all the SSIS packages to perform all the ETL from start to finish
3) you need a resource who can build a cubes incl. measures, dimensions, and hierarchies

With Jet Enterprise you still need 3 people, but the needs are vastly different.
1) we need someone who knows the business questions you're trying to answer
2) we need someone who understands how measures (like CoGS, or DSO, or Profit Margin) are calculated
3) you need Onyx Reporting to either provide training to your DBAs on how to use the JDM product, or (arguably the most expeditious use of your time) engage Onyx Reporting to do the customizations for you.

I hope you have a better understanding of what the Jet Enterprise is all about.  If you purchased it but haven't installed it yet send me a message!  

I will do your install at a discounted hourly rate of 40GBP per hour +VAT if you mention this blog post.