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?

As I create new How-To guides specific to data warehouse automation, I'll add them here.

Fuzzy Look-ups allow business to match and conform data to a reference set.

Use Cases:

• A government organisation had several databases for employees including temp hires, contractors, and diverse databases for various civil services.  They also identified challenges when employees left an organisation and came back as contractors or new EmployeeID's.  
• An entertainment venue with online marketing systems, retail stores, customer loyalty programs, and web sales to track create a unified view of customers while consolidating duplicate entries across the systems.
• A parcel delivery and courier service had issues with rampant data entry issues for order intake.     Fuzzy logic in conjunction with geocoding addresses  facilitated location-based analytics and faster / more accurate deliveries.

In this video, we use the Jet Data Manager, a data warehouse automation tool, in conjunction with SSIS and Visual Studio to rapidly implement a data quality architecture.

In the last decade, software developers addressed the barriers to comprehensive data programs by developing robust data warehouse automation tools that automated code generation for recurring tasks in data projects. Although the solution had obvious benefits to the developers hacking out code in the basement, the value proposition was unclear for business executives. "You want me to spend 100k on what?"
​
Assumption: To remain competitive organizations must leverage data to augment their product or services offering and/or use analytics to reduce costs or innovate value-added business processes.

Why do I need a governed data warehouse? Can't I stick with my self-service solution?
No, self-service business intelligence (BI) will not replace a data warehouse. Your organization needs:

• A single version of the truth -- Self-service solutions frequently require users to perform their own 'data-munging' (extraction and cleansing) which translates into inconsistencies between reports and departments. In comparison, pre-defined metrics in a data warehouse enforce consistency across the enterprise.
• The ability to keep history -- Operational and transactional systems overwrite and lose critical data history.
• Improved end-user productivity -- In creating structured datastores, organizations democratize data access and facilitate data-driven innovation (with cleansed & consistent data).

We still need self-service tools. Data-driven innovation frequently stems from exploration by individuals or small teams before transitioning into enterprise-wide solutions. The cutting edge of data innovation lies at the intersection of self-service flexibility and agile datastore implementations serviced by an automation tool.

--Update 4/10/2016 --
Onyx Reporting uses and recommends data warehouse automation tools developed by TimeXtender; because while the application does auto-generate code, all parts of the business intelligence development process (extract, transform and load) are accessible and customizable using traditional tools in the Microsoft BI stack.

​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.

By Jae Myong Wilson -- get more articles like this sent to your email.


Last week as part of a Jet Reports Brainteaser, we explored a common report requirement--grouping transactions by an attribute off the vendor card.  The frequently-used solution would yield an incorrect result.
​

"I need help creating a report in Jet Express [report writing software]. I have the "Age" field in the vendor table and I want to show purchases grouped by age bucket: ex. 10 - 20, 20 - 30 , 30 - 40 and the count of vendors .. need help"

Calling all Jet Reports report writers.


I saw this post on Dynamics User Group from a few years ago.
"I need help creating a report in Jet Express [report writing software]. I have the "Age" field in the vendor table and I want to show purchases grouped by age bucket: ex. 10 - 20, 20 - 30 , 30 - 40 and the count of vendors .. need help"

Solution Permutations: 

• Lvl 1 Solution:  Assume using Jet Express & all data originates from Vendor and Vendor Ledger Entry.  May have to 'mangle' the data post execution.
• Lvl 2 Solution:  Assume using Jet Professional & all data originates from Vendor and Vendor Ledger Entry.  Can use Pivot tables or other post aggregation methods, but no mangling data post execution.
• Lvl 3 Solution:  Build solution without using Table Builder and no pivot tables (or similar) for aggregating the data post running the report.
  
  

Don't miss the solution!  Sign up for our bi-monthly mailing list or visit www.OnyxReporting.com/blog to access our archived posts.

Jae Wilson is a Jet Reports Certified Trainer from Data Analytics consultancy Onyx Reporting.

​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.