Sorry it's been so quiet!  Been super busy.

Here's a quick 'report doctor' tutorial based on a report sent in by an end user Jorge from my Youtube Channel.  He was looking my tutorial on NL(First) and posted in the comments that he was unable to figure out why his report still wasn't working as expected.

After a little poking around, I saw a couple opportunities for improving his report as well as spotted why the report wasn't working as expected.

This video covers:

• Proper setup of Filters
  
• Understanding NL(First) versus NF() functions
  
• Multi Company filtering​

For more information on NL(rows) and the Record Key, check out this blog.

If you like what you saw, shoot me an email or visit our Services page to see a sampling of the services Onyx Reporting offers.

If you're using the [Inventory Turnover] report from the Jet Reports, Report Player you may be drawing the wrong conclusions because THE REPORT HAS AN ERROR IN IT.

If you use the [Location Code] slicer, you may assume that you're looking at the inventory from THAT location; however, due to the design of the report, you're actually looking at inventory across ALL locations.

Easy solution?  Recreate the report in Jet Enterprise.  

At 40+ minutes, the tutorial is a mini-consulting session!  But if you're strapped for time, I've included hyperlinks that function as chapter markers.

Use Excel's GetPivotData function: https://youtu.be/h2Si6xHmWbw?t=262

Show Calculated Fields in Pivot Tables:  https://youtu.be/h2Si6xHmWbw?t=604

The Inventory Turnover Report IS WRONG:  https://youtu.be/h2Si6xHmWbw?t=781

Building KPIs in Jet Data Manager:  https://youtu.be/h2Si6xHmWbw?t=1107

Visualizing your cubes in Excel #PivotTables:  https://youtu.be/h2Si6xHmWbw?t=1712

When Rollups make your KPIs 'wrong':  https://youtu.be/h2Si6xHmWbw?t=1798

Visualizing your data in PowerBI:  https://youtu.be/h2Si6xHmWbw?t=2293

If this is all greek to you and you want someone to review your existing Jet Enterprise implementation (there's a new update to Jet Data Manager) , give me a holler at jae@onyxreporting.com or shoot us a message through the website.  Our team is available for training, development services and support remotely as well as onsite!

The new update to Jet Data Manager is finally here and Harry L., the lead KB article writer over at Jet Reports, www.jetreports.com, has been super prolific in documenting the updates!  Side bar: the Change Log hasn't been updated yet, but I'll post about it as soon as it does.

If you're on an older build of JDM (anything less than 17.5) today is the day to update because the features and performance improvements are HUGE.

There are a grip of new KB articles but I'll just share the ones I think most relevant to me and my Jet Enterprise clients:

• Improvements in Conditional Lookups (the red fields).  Side bar:  they FINALLY removed the manual index generation option.  (not sure why that ever existed in the first place!)   Moving forward indexes will auto-generate to support conditional lookup performance.  That said, it never hurts to use tools like the Database Engine Tuning Advisor to see if there are other indexes you can create to optimize custom views and transformations.
• Better Handling of Null-value Type II Fields.  Slowly Changing Dimensions in JDM got a much needed overhaul to improve usability and legibility.  If you don't know what SCDs are, read the following articles straight from the grand-daddy's of dimensional modeling Ralph Kimball and Margy Ross.  (SCD 1,  SCD 2 and 3, and SCD 0, 4, 5, 6
  
• More Options for Delete Handling (related to SCDs)
• Type 0 Fields on History Enabled Tables (related to SCDs)
• Using SQL Azure.  As more organizations are moving their infrastructure into the cloud, JDM being limited to on prem databases was a challenge, BUT NO MORE!

If most of these articles are greek to you, that's fine, give me a holler at Jae@OnyxReporting.com, or open a chat window and I'd be more than happy to give a free assessment as to whether the upgrade to JDM 17.5 is appropriate for you!

One of the selling points of Jet Enterprise / TimeXtender is how easy it is to create cubes that allow you to quickly and flexibly analyze data that rolls up by hierarchies (think [Sales] rolled up by [Date YQMD] or by [Customer] > [State] > [Region].

Traditional Business Intelligence developers and SQL DBAs know and recognize this as 'User-Defined Hierarchies' traditionally implemented in Visual Studio or SQL Data Tools.  Furthermore, your developer probably knows a handful of ways to optimize SSAS performance which can / should be duplicated in Jet Enterprise.

This tutorial shows how to analyze your cube for user-hierarchy optimization opportunities and then how to implement them in JDM via attribute relations.

For an in-depth introduction to attribute relations in SSAS multidimensional, check out, MS SQL Tips.

After my last video and blog post on 5 ways to sort Jet Reports, Mario from Jet Reports Spain asked for a tutorial on the "sort on sum" technique.  So I made one!

Full disclosure.  This is not an easy technique!  It requires a little knowledge about using double quotes, ", in Excel strings, as well as adding calculated fields inside of Jet replicators using NF functions.  It may seem a little confusing, but if you give me 40 minutes of your attention, hopefully it'll all make sense.

Fair Warning:  Only use calculated fields in your report if you absolutely need it (because it reduces report performance).  

Like what you saw?  
Have reports that require construction?

Give us a holler.

If your Jet Report has ever suddenly stopped working ("it was fine and all of a sudden...") many times it can be attributed to behaviors that can cause spreadsheet corruption.  The most common culprit? Changing the structure of the spread sheet ex.

• Inserting Columns or Rows 
• Moving Columns or Rows around OR
• CHANGING THE SORT ORDER OF YOUR REPORT!

In case you missed the original post, I've also included the video of the mentoring session I did for the original Jet Professional Report.

In the tutorial I review using the Company= argument for multi-company reporting, and include information about report optimization using Data Dumps / Record Keys instead of a slew of NL("First") functions.

Hope you enjoy!

This tutorial highlights 3 ways of visualizing data using the Jet Reports product suite.

• Jet Professional (the Excel add-on) pointed directly at NAV
• Pivot Table pointed at a cube built using Jet Enterprise
• PowerBI pointed at Jet Enterprise cube using the Direct Connector.

This tutorial will show you:

• How to add custom Calculated Measures (like [Revenue Amount] and [Amount as a Percent of Revenue]) to your Jet Enterprise cubes
  
• How to visualize different time periods in the same pivot table (Month and YTD).
• How to use interactions and slicers in PowerBI to simultaneously filter multiple visualizations in the same dashboard.

This year I, Jae Wilson, had the pleasure of representing Onyx Reporting at CollisionConf 2017, the largest growing tech conference in the United States.  While this blog is usually reserved for business intelligence related topics, I did want to share some tweet-able things I heard at Collision.

"What is the half life of data?"  How rapidly does the value of your data decay, and what systems are you putting in place to capture and analyze it?  

IOT-ready platforms (internet of things) and real-time analytics may not be high priority concerns for your organization, but all companies regardless of industry or size should be examining how they acquire, analyze and act on data.  

• Could you convert or retain more customers by automating business processes?  
• Could you reduce loss or waste with better analytics?
• Would you make different staffing or scheduling decisions with more regularly updated reporting?  

​Jet Reports, PowerBI, and TimeXtender are platforms that can accelerate data acquisition, integration, and analysis.  If you need support with implementation, Onyx Reporting has a summer sale for services, training and upgrades.

"Allow your audience to see themselves in your shoes." 
"Keep asking Who The F$@K cares about your service."

These two nuggets of wisdom jumped out at me because I regularly ask my clients "why do your customers stay with you?" and I'm equally regularly surprised by the answers.

"We offer high-quality and personalized customer service which our loyal customers love," my client told me.  Curiously, as far as I know, they haven't implemented any customer satisfaction metrics, nor do they analyze why customers churn.

• What assumptions are you making about your customers?
• Is there data that can validate your assumptions? 

Solutions:  

• Set a default budget for Budget Amount
• Invert the sign of Income Statement Accounts.

Why this blog series exists:
When you buy an out of the box data warehouse & cube solution like Jet Enterprise from Jet Reports, www.jetreports.com or ZapBI, it usually meets the 80/20 rule (the stock solution satisfies 80% of your reporting requirements, and you'll need further customizations to meet your remaining analytics requirements).  
​Client requests from customers like you inspire this blog series.

Problem:  Some measures require filtering by a default value to show reasonable results.  

Consider the example of the company that creates and revises a new budget each quarter.  While it's perfectly reasonable to show the [Budget Amount] for March 2017 from the 'Initial 2017 Budget' or 'Q1 2017 Amended', it would be misleading to show the sum of both budgets for March 2017.  Similarly, if your organization uses a forecasting tool to calculate expected item consumption or sales, you may have multiple forecasts numbers for the same item and month. 

Solution:  To minimize the risk of 'double counting', we'll modify [Budget Amount] to always filter by a default [Budget Name].

In this solution, we hard-coded a budget into the Default Member as part of the dimension definition.  For improved maintenance, we could add an [IsDefaultBudget] attribute to the [Budget] dimension in our cube and data warehouse.  Then reference the new column when defining the Default Member.  Ideally our source ERP system can identify the current budget, or we can implement business processes and SQL code to auto-identify the correct budget.

Note:  Because we can have multiple concurrent budgets, but shouldn't sum [Budget Amount] across different budgets, the measure is a semi-additive fact -- in Business Intelligence nomenclature semi-additive facts cannot be summed across all dimensions.  Other common examples include balance, profit percent, average unit price, company consolidation, as well as most cost-allocation schemes.  When customizing our data warehouses and cubes, we must be careful where we implement the calculation to avoid reporting errors.

Non-additive facts cannot be summed (ex. [First Invoice Date]).  Fully-additive facts are 'normal' facts which can be summed across all dimensions.

Problem:  Sometimes the dimension used affects how we want to calculate a measure.

In many ERP systems the [Amount] or [Quantity] column have counter-intuitive signs.  Consider G/L Entries which have negative signs for revenue and positive signs for expenses or inventory transactions which show a negative sign for item sales and shipments and positive signs for item purchases.  While the data makes sense to accountants and analysts, business users frequently appreciate seeing signs that intuitively make sense.

In cube-based reports, we'd like to invert the sign on [Amount] for all Income Statement accounts but not Balance Sheet accounts.  Note, it's arguably 'wrong' to invert the sign in the data warehouse because that would make our [Amount] column semi-additive, and potentially cause problems for our auditors!

Solution:  In the cube, use a SCOPE statements to assign a different calculation to [Amount] based on the [GL Account] dimension.

 Note:  in SSAS cube nomenclature we typically deal with two out of three measure types:  Standard and Calculated (avoid Derived measures when possible).

A Standard measure is typically a SUM or COUNT of a column that exists on the fact table in the data warehouse:  ex. sum(Amount) or sum(Quantity).

A Calculated measure is an expression that typically uses standard measures in conjunction with MDX expressions and functions.  Typical examples include division (profit margin percent or avg. daily sales) as well as all business functions (Year To Date, Balance, or  Previous Period). 

A Derived measure is a calculation based on fields from the same fact table -- ex. Profit Margin ([Sales Amount] - [Cost Amount])   When possible, avoid derived measures and use standard measures instead-- just add the calculation as a new column in the data warehouse.  Using standard instead of derived measures has two major advantages:  

1. Users reporting directly against the data warehouse can use the predefined column.
2. The cubes will process faster during their overnight load.

Remember, out of the box solutions by definition are designed to work for ALL customers.  Further customization is expected, even required!  Onyx Reporting is available for development services, training and mentoring as you implement minor (if not major) customizations to your Jet Enterprise implementation.

For clients not using Jet Enterprise, please contact us to find out how data warehouse automation can cut your business intelligence implementation time from weeks and months into days.