We’re borrowing from a Product Manager by the name of Steve Little at Jet Reports today to talk about a powerful tool that many are just beginning to understand. “Cubes,” sometimes referred to as “OLAP (or Online Analytical Processing) Cubes” are a handy way to organize, extract and report upon the many dimensions of your company’s stored data. Today, we’ll keep it simple, but we hope illuminating, by following the Little’s comments from the Jet Reports Blog.

(Jet Reports, by the way, is a powerful reporting tool that acts as an extension of Microsoft Excel. It allows users of full-fledged ERP systems like Microsoft Dynamics to build, save and update reports of data that resides inside the databases and tables of their ERP system.)

In his original post (found here) Little starts with a simple hypothetical question one might ask: “How much profit did we make selling Sno-cones to Icelanders last year?” In a Dynamics NAV database you might need to combine data from 6 or more tables (Customer, Region, Item, etc.) to deduce the answer. Then you’d have to “mash up the data and extract the data to get the information that you need.” That can be a slow and resource intensive process, Little points out. What you really want is a fast and easy process – and that’s where Cubes come in.

Cubes make it easy to answer questions like this by reorganizing a copy of the data so that it’s easy and fast to get information out. They are a multi-dimensional way of organizing data – which means they offer a near instant analysis of large amounts of data.

A few term definitions may help, as you ponder the illustration above:

Dimension: a category used by which to slice your data (say, by Company, Salesperson or Item)

Measure: a calculated numeric value (like a sum, count or average)

Level: a grouping within a dimension (like Customers by Salesperson, or Territory)

Hierarchy: a group or organizational level (like date by year, quarter or month)

Our three-dimensional cube illustration would be an example of a way to view sales and profits of products and territories over a period of years, for example.

Cubes such as those utilized by the Jet/Excel combination allow ERP system users to use a tool like Excel to extract data from their ERP databases and thereby improve and maximize their reporting capabilities. If you’re handy with Excel, you can learn to master cubes. In the Jet Report Blog, Little closes by dispelling 3 common myths about cubes:

Myth #1- Cubes require months of planning and implementation before users can get value out of them. As of maybe three years ago, this myth was probably true. It would take months or years to get any value out of them. With Jet Enterprise, it’s typically installed and running in two hours or less for Dynamics customers. With pre-built Cubes, it’s easy to go in and get that done without the months and months of planning.

Myth #2- Customizing cubes is slow and difficult. The Jet Data Manager, which is included with Jet Enterprise, allows you to customize or create Cubes in a simple drag-and-drop interface. You can add dimensions, measures, or create unique measures on the spot and apply these into the Cubes – often in minutes, with no programming.

Myth #3- Cubes are a luxury, applicable only to large companies with BI development teams and large budgets. This is no longer true. Jet Reports has over 600 customers currently running Cubes. You no longer have to have a large company with a BI development team to be able to get value out of the Cubes.

This month’s APICS Magazine (Mar/Apr ’15) presented what we thought was an ideal example of the utility of an ERP system combined with a little Excel expertise in defining the benefits such a powerful combination can bring. In an article titled Steady Supply and Demand, APICS Managing Editor Elizabeth Rennie writes of a bearing manufacturer that was able to get a much better handle on matching order demands with supply capacity.

The company had extended lead times due in part to capacity constraints that could negatively affect sales opportunities and on-time deliveries, as well as causing increased expediting costs. Basically, sales planners needed better visibility over inventory and production, and the shop floor wanted consolidated purchase orders for all sales units in order to identify potential capacity issues.

The solution was a report – drawn from the central repository of data found in the ERP system, matched with an Excel report to parse, filter and report the desired results, and all based on foundational APICS training and knowledge. In other words, APICS taught them how to look, and the report they designed gave them the visibility they were looking for.

The company needed to know “what products were made on what production line, how many machines were on each line, and what capacity was available on each machine.” Once they had that, their team could identify the items and SKUs associated with each product and inventory group code. They then added a field to the ERP system’s item master card where the production line code was assigned to the SKU.

An Excel prototype served as the output format, and with some programming effort (at our firm, we’d think in terms of Jet Reports of course), orders and forecasts were extracted and mapped to a table of production line assignments. After validation, the report was introduced to both sales and factory teams as a “source of reference to make capacity planning and investment decisions at the factory.”

Employees get real time visibility into production line use and can drill down to order details. It enables a high level of responsiveness to issues like constraints, machine downtime, order changes and the like. Their report evolved and grew to display available capacity and backlog development, so planners can recognize potentially damaging trends before the damage is caused.

And of course, because the report is built from the company’s single, trusted silo of uniformly available data – its ERP system – and then tested and validated, all users gain a ‘trusted’ view of orders and current plant capacity, in real time. As the article points out, “this establishes trust among operational units that often have conflicting priorities.” (Sound familiar?)

Just one more fine example of how ERP and a little clever modification and reporting can yield improved business performance and customer responsiveness.

In our prior post we looked at what the folks at Jet Reports had to say in a white paper about building a Business Intelligence repository. The authors contend that you want to build it first, and then work on the accuracy of your data, for some rather counter-intuitive reasons. Read that post first, then see today what 4 steps they recommend to ensure that when it comes to implementing Data Governance, you are the “Data Governor.”

Step 1: Identify and Establish the Vision. Make clear what you’re trying to accomplish with your reporting and data, then break down what you’ll do to accomplish that. For example, the vision might be “Be the easiest company to buy from by offering one-step ordering and 100% on-time delivery with competitive prices.” From that, goals can be established, like

“to increase customer satisfaction and encourage repeat business by streamlining the order process”

“to increase loyalty by instilling trust” and

“to increase quality of revenue through corporate retention”

Each of these goals can then have specific action items that pertain to how your BI system works and how you interact with the customer.

Step 2: Appoint Your ‘House of Representatives’. The point here is to make sure that the way you collect, handle, store and process data is efficient and proper. Major roles would include:

An Executive Sponsor – a senior person passionate about the task at hand

Data Stewards – Your IT and subject matter experts

Data Governance Driver – The coordinator for all tasks

Step 3: Build the Process. When bad data finally surfaces in your data warehouse, it’s a good thing. When numbers don’t make sense, you drill down, identify, locate and correct. Jet’s people talk about three process stages:

Upstream Processes – What processes capture, create, import, transform or update and introduce data into your company’s system? Too often the collectors of the data have little knowledge of (or sometimes, concern for) those who will use it. That’s why an Executive Sponsor can evangelize and enforce proper methods.

Stewardship Processes – Managing the rules, policies and standards that govern your data in order to ensure it’s clean, well-managed and properly escalated when necessary.

Downstream Processes – The operations and analytics that consume, protect and store the data. This is the user interface: dashboards, reports, drilldowns, etc. This is where the ROI happens.

Step 4: Policing the State. Your forms and policies govern everything from the state of your CRM system to the integrity of your data. It governs the data input process. Be sure your data governance policies then include:

Data accountability and ownership

Organizational roles and responsibilities

Data capture & validation standards

Data access and usage

The Jet Reports white paper’s authors make some critical points about how you collect, manage and utilize your data. Their suggestions on building the repository and then managing and improving the data as you go also fit perfectly into today’s ‘agile’ environment, and are worth a read.

For a copy of their white paper, leave a comment on this blog and we’ll send it out.

In a recent white paper the folks at Jet Reports, a leading reporting engine for the ERP industry that’s extremely popular with users of systems including Microsoft Dynamics, authors Jonathan Oesch and Tara Grant make some very smart – and counter-intuitive — suggestions about how to go about building business intelligence, even when the data isn’t quite right.

They start from the premise that, when you think about it, you don’t know that data is bad in a business intelligence world (i.e., reporting and analysis) when you’re putting the data in – you only know when you’re getting the data out.

So if you have an urgent need to manage, say, three or four inventory-related KPIs (Key Performance Indicators), the time it may take to understand whether your inventory data is actually accurate can be, or seem, like a very long time.

Their prescription: build the business intelligence first. If you build your reports and dashboards early, your key stakeholders are very likely to know when the system is spitting out bad data. It will, most of the time, be obvious to your subject matter experts. And that’s precisely when you can start drilling down into the data to determine just where the mistakes are occurring.

In other words, your BI (Business Intelligence) environment itself will make your numbers accessible, and the errors will usually reveal themselves. That’s when you can start to get down to the root causes behind those errors, on the way to building a better data repository in the long run — and most importantly then, rendering better business decisions.

The Jet White paper (available free, just ask us in the Comments section for a PDF copy) then goes into some impressive depth explaining data governance, and teaching readers how to become their owner “data Governor.”

We’ll take a look at some of their key ideas in our next post. Stay tuned…

As a Microsoft Dynamics NAV reseller, our team deals almost daily with the reporting needs of our clients, and the tool we recommend to help them is one of a family of products collectively known as Jet Reports.

So today we thought it might be a good idea to define for our NAV readers and users just what this Jet Reports family is all about. Here’s a quick-shot, high-level view of what, in reality, are three different levels of reporting and BI (Business Intelligence) solutions.

At its core, the family of Jet products (Jet is headquartered in Portland, OR) are powerful tools for integrating Microsoft Excel with Microsoft Dynamics. In short, if you run NAV and you need reporting, then you need Jet. If you know Excel, then you’ll love Jet!

Jet users can be “designers” (they have the rights and ability to create reports) or “viewers” (those who can view or review, but not create, reports.) Jet will even create PDF or XLS reports that you can push downstream to other users in your organization who are not even Dynamics NAV users!

At the basic level, there’s Jet Express. It’s free, so the price is certainly right. With Jet Express [quoting from the folks at Jet] “you can select the fields you want from any of the tables or views in your database and build reports quickly using a simple interface inside Excel. Take advantage of Excel’s tremendous capabilities to add charts, Pivot Tables, and custom formatting to build stunning reports.” You can download dozens of pre-built Excel reports and in at least some cases, refresh your data with the click of a button.

Jet Express is your point of entry for Jet/Excel reporting. Most companies will quickly tire of some its limitations (like limited report formats, lack of sub-groupings and very little ability to edit or “tweak” a table report and then re-save it). Still, what do you want?… it’s free. Those needing to take the next step up will likely move up to the next level of Jet…

Jet Essentials sells for about five grand and opens up Jet to greater reporting capabilities. It permits more advanced reporting and group collaboration. You can connect to all your business systems from inside Excel. You can automatically pull in data to create the reports you need. You can refresh report data – in real time — at the click of a button. You can also schedule reports for automatic distribution and use “proactive alerts” to push critical information out to others. Jet Essentials also features improved drill-downs to underlying documents. In short, it provides the reporting backbone, through Excel, for your entire organization, and greatly expands the convenience and capabilities of reporting with Jet.

Finally, at the top end of the Jet food chain, and priced around ten grand, is the full-blown BI (Business Intelligence) and reporting tool called Jet Enterprise. It allows you to quickly analyze issues from different perspectives within your business to discover underlying trends. Once again, you’re using the familiar tool of Microsoft Excel. It features a drag-and-drop data manager for adding tables and fields to the cubes to access the data warehouse. You can learn to manipulate data to see the information you want. You can implement Jet Enterprise and begin to gain insights into your business pretty quickly. And it’s a business collaboration tool – without the need for highly skilled technical resources.

We’ve found that most businesses will happily start with Jet Express. When they’re tired of bumping into its limitations, they’ll make the investment of about $5K to step up to Jet Essentials – and often that’s where most will stay. True data-mavens and larger organizations (with the supporting budgets) may feel compelled to move up to Jet Enterprise, but for most we’ve found that the mid-level Jet Essentials hits the ‘sweet spot.’

In our prior post we discussed the two most common ways companies move their data from an old, legacy business management software system (i.e., ERP) to a new one. We noted that most companies “port” their old master (sometime called static) data – like customers, vendors and items – but only in some carefully considered cases do they move the more complex transactional items – things like invoice line items, quantities shipped, item lot numbers or discrete payables invoices – from their old databases to the new. The complexity of doing so has cost implications that, as we noted in that post, aren’t always worth the investment.

Sometimes, we opined, it’s simply easier and more cost effective to keep access to the old system available to users for those times when the old data is needed, but not to bother moving all that old transaction data over to the new system.

But for those companies who have a well-considered need for moving both static and transactional data – and keeping it for an extended period of time — one possible data migration strategy to consider is called ETL. ETL stands for Extract, Transform and Load, and the tools to employ it are becoming known today.

Basically, ETL employs what’s called a “data warehouse,” into which you can bring transactional data for reporting purposes. You first do a traditional mapping and porting of your “master” data into your new ERP system. The data warehouse then exists outside the actual ERP system, but it’s accessible. It allows you to use legacy data in conjunction with the new ERP system, but without the cost and difficulty of actually porting the old transaction data into the new ERP system.

ETL tools are available to help with the warehousing task. For example, the folks at Jet Reports – a powerful reporting tool that allows users of systems like Microsoft Dynamics to automatically extract data from their ERP system into Excel for subsequent manipulation and analysis – have engineered a solution that enables the old data to be extracted out of an existing system, transforms its structure to match the desired new data conventions, and then transforms the data itself to be, as they put it, “optimized for reporting and analysis.” The final step is to load the data into an environment where it can later be easily retrieved – the data warehouse.

ETL vendors claim that their advantages include speed, simplicity, preservation of data, time savings, money savings, and “fewer heated conversations.”

The better tools work with today’s increasing ubiquitous SQL database structures in an automated fashion. Jet claims that “it can read the database schema automatically and then lay it out in a graphical format so that tables and fields can be chosen for inclusion.” From there, the data can be cleaned and scrubbed, and users can create verification and business rules to ensure that history is accurate and valid. Testing is still required, but an ETL’s automated approach is said to increase its chances for success.

The end results can be access to many years’ of historical data safely ensconced within the data warehouse that does not corrupt the new system, but maintains historical accuracy for users of the new system when business need, or regulation, calls for it. It’s just one more tool in today’s burgeoning Business Intelligence (BI) landscape.