Retail Data Warehousing for Microsoft Dynamics NAV

In this article, data warehousing will be discussed, both generally and specifically, for retail organizations who are looking to expand their Microsoft Dynamics NAV experience.

In the fast lane that is business today, maybe especially for retail companies, data is vital.  Since data drives decisions, retail organizations are making plans to stay on track and on budget with their customers, stores, vendors, management, etc. – and data warehousing can be really effective in organizing and empowering stronger Business Intelligence (BI) analytics.  Like most software, if you aren’t familiar with it, you likely have questions.  In this article, I’ll answer frequently asked questions about data warehouse solutions for retail companies using Microsoft Dynamics NAV.

Define it.  Let’s begin with the definition: data warehouses are multi-dimensional databases.  If that doesn’t clarify the solution, I could also describe a data warehouse as a virtual storage space or a server-staged database, whether that server is stand-alone or shared.  In comparison, an external hard drive offers space where you can store multiple types of files and programs while a modern data warehouse provides you a place to house diverse operational and transactional data.  Also, if you imagine three-dimensionalizing an Excel spreadsheet, you could structure your transactional and operational information in aggregated, streamlined, and effective ways, which is precisely what data warehousing does, eliminating errors with business user friendly software.  Today’s commercial data warehouses are maintained with an administrative database management program, like Microsoft’s SQL Server Visual Studio, or a software like BI360 Data Warehouse Manager, which is Solver’s easy-to-use product that is positioned within the BI360 Suite.

Implementation.  Regarding installation and automation, modern commercial data warehouses usually come “out of the box,” which essentially translates to an easy implementation.  Next, you will work with a consultant who has expertise in extraction, transformation, and loading (ETL) of your data to install the data warehouse and replicate your Dynamics NAV and other source information.  The consultant will then automate the ETL process from your data sources.  It makes the most sense to install a data warehouse that has a pre-built integration to the Dynamics NAV Cloud or on-site installation if you want a seamless experience.  You’ll next work with a consultant who will simplify and streamline your BI analytics, as well as train your users to query data from the warehouse for the financial statements, budgeting, and data visualizations you depend on for decision-making about the future of your retail company.  After configuration and training is complete, business end users at all levels of the company can engage with the data warehouse without IT department involvement.

How come?  Not every retail corporation will want or need a data warehouse, and there’s no exact threshold to cross in order to begin shopping for one.  But if you consider the diversity of your data types, whether product-focused, consumer behavior, sales by regional management or store, or additional metrics, you would easily see how challenging it can be to manage all of your different information separately, especially in terms of consolidated analyses for a better understanding of the overall health of your company.  Some vendor solutions also offer a data warehouse that lets you combine your sales forecasting and budgeting needs with your reporting and dashboard needs.  Plenty of organizations are regularly consolidating information in software like Excel, but a data warehouse can get rid of errors, wasted money and time, as well as tedious manual documentation.  Furthermore, if the Dynamics NAV database is sluggish due to significant and sometimes simultaneous report queries, a data warehouse provides the high performance and the stability without slowing down the ERP system or operational database server.  In other words, the primary benefit of a data warehouse investment involves producing richer reports, budgets, and dashboards since you can include information from diverse sources, like CRM, Point of Sales, marketing, inventory, etc.

What about OLAP cubes?  Regarding storage of diverse data types, a lot of BI tools suggest or even require an online analytical processing (OLAP) cube, so you could be asking yourself, why not an OLAP cube instead?  OLAP cubes are not transactional SQL server databases, so you have to have staff with OLAP-specific experience and skills, like MDX query language fluency, to manage the software because of how technically complex they tend to be.  Moreover, most cubes are for analytical data as opposed to transactional information.  A data warehouse is ordered by subject, and you can stage multiple kinds of data within the easy-to-use, modern interface for flexibility and accessibility in data management processes with your BI tools.

Managing the technology.  Business end users across the corporation can manage a modern commercial data warehouse.  Configuration and automation of your retail information is easy, whether you’re pulling data one time or more routinely.  You can query data with one click of a button, and since most data warehouses are Microsoft SQL Server relational databases, organized by topic, like customers, sales, vendors, products, and GL data, you don’t need IT to be involved.  Data warehouses organically offer cross module analytical reporting and financial consolidation, in addition to drill-down functionality, by storing diverse data from separate sources in one place.  Concurrency control mechanisms, transactional processing, and recovery are not typically requisite, except when backing up your database, since they are stand-alone tools.  Data warehouses usually come with built-in attributes, dimension trees, and adjustment functions, like currency conversions, eliminations, data cleansing, and integration methods for a more efficient process.  You can also utilize a data warehouse to streamline and speed up migration from an older ERP system to Microsoft Dynamics NAV by housing historical information from the old accounting or Point of Sale system in the warehouse, produce a historical analysis there, and then only replicate the most recent period’s closing balance over to Microsoft Dynamics NAV.

Data will continue to grow rapidly in terms of size and importance to organizational decision-making, maybe especially for retail companies.  Thus, data warehouses will also become more popular, mainly since you can consolidate multiple types of data into one space to upgrade your financial and operational reports, budgets, and dashboards.  If you’d prefer to rely on just one device to consolidate all of your data into one high performing space without needing IT to manage the software, data warehouses can offer you exactly that.  Solver offers a fully built, configurable Microsoft SQL Server-based data warehouse stand-alone and as part of the comprehensive suite of BI modules and would be happy to answer questions and generally review BI360’s easy-to-use Data Warehouse solution that enables collaborative, streamlined decision-making capabilities for your Microsoft Dynamics NAV experience as a retail organization.