Data Warehouse and OLAP Cubes for Microsoft Dynamics NAV

Business intelligence data stores can be a foundation for your data management and analysis workflow.  This article will discuss options for Microsoft Dynamics users.

Today’s corporate decision-making is all about data: operations, transactions, and more that a company manages and analyzes to understand the best way to move forward competitively.  Keeping and utilizing data is more than a breadcrumb trail or a narrative of where a company has been – it helps to formulate the future.  But without a proper education on Business Intelligence (BI) data stores, understanding your options can be challenging.  There are just a few options, but they are distinct in functionality, and based on your particular needs within and around your Microsoft Dynamics NAV experience, one data store might be the only option you and your team can manage.  This article will discuss the top two options for data storage for implementation with Dynamics NAV: OLAP Cubes versus Data Warehouse.

Let’s start with definitions.  OLAP is an acronym for online analytical processing and cube refers to a multi-dimensional data set.  Therefore, an OLAP cube is a staging platform for data analysis information and a mechanism that you can query data with for organized and dimensional analysis.  Alternatively, a data warehouse is a database that stores information, managed disparately from an organization’s operational database.  It empowers the storage and organization of data by providing a stable platform for multiple types of data in one place.  These two data storage types have different IT requisites.
Because an OLAP cube is not a standard SQL server data warehouse, you will need a team member with an OLAP technical background to manage the data store.  Should you have someone on payroll with OLAP skills, you are in a good position now, but should this person leave the company, it will be one more personnel requirement to cover.  However, there are plenty of people in the workforce that can manage an OLAP cube as they are implemented by a lot of companies.  Data warehouses used to be a development task that were an expensive undertaking, but now, some vendors provide fully built and customizable products you can buy to store more than one type of data.  Moreover, some data warehouses can be managed by a business end user.  Management is just one of many aspects to consider.
The data storage source decision you make for your Dynamics NAV experience is a big one because it affects your own BI roadmap.  Some companies regularly pull and analyze data from both NAV and their other data sources, so the platform decision could drive their other BI tool purchases, such as reporting, budgeting, and dashboard solutions.  Others might invest in a data store as a response to workflow needs.  Regardless, the product variances are important to understand when deciding your best option.
An OLAP Cube can be thought of as a three-dimensional spreadsheet, providing for more elaborate data analysis.  More specifically, OLAP refers to analytical data instead of transactional, and cube refers to the storage functionality.  Therefore, OLAP cubes are simply databases with multiple dimensions and with pre-aggregated data and pre-calculated key performance indicators.  A lot of BI solutions depend on OLAP cubes for storage and analysis of organizational data, whether it is financial reporting, budgeting, or dashboards.  For example, as a CFO, you might need to produce a financial statement with location, month, or department as dimensions of this cube to structure the data.  Important to note though: OLAP cubes are not SQL server relational databases.
Since OLAP cubes are not open SQL server relational databases, so their management entails a professional with the experience to maintain the data store, as opposed to a SQL server data storage source that can be managed by most IT professionals with regular database training.   There is a potential related cost to an OLAP cube, in regard to personnel requirements.  You might have someone on staff that has to now use some of their workweek to manage the data store – or find someone new to join your staff.  Furthermore, OLAP cubes are usually more limited and rigid in terms of writing reports because their functionality leans more table-like.  If you are building a toolbox of BI solutions, features, functionalities, and even (perhaps especially) aesthetics should be important factors.
https://youtu.be/mcfGnHjxbRM
Data warehouses are structured with the business end user in mind.  Data warehouses structure around subject, so it focuses the interaction through organizing by topic, like sales, customer, and/or product.  Because you can replicate your data from Microsoft Dynamics NAV, a Customer Relationship Management (CRM) program, or any other data storage source, data warehouses are not live, real-time integrations.  Therefore, the data in a data warehouse can be used for routine or scheduled reporting, budgeting, and dashboards for a higher performance data query.  Perhaps one of the more important functionalities: data warehouses can store multiple, diverse data types.
Data warehouse manufacturers build the product so that it can house and integrate multiple distinct data sources for a consolidated database.  “Smart” processes, like data cleaning and data integration techniques specific to data warehouses, produce this hub of consolidation.  Your organization can keep financial transactions, personnel data, and other organizational or operational information in one place, moving beyond cash flow and financial numbers.  Data is replicated and transformed from the operational environment in this accessible data store.  Data warehouses only require two actions to access your data: the replication of the information and accessing it.  Data warehouses stand alone – simply and securely without any recovery, transaction processing, or concurrency control mechanisms.  Because of their SQL server platform, data warehouses epitomize efficiency for the business user.  Additionally, they can be acquired fully built instead of the sometimes staggering cost of developing a homegrown data warehouse by hiring some developers.  And the costs of OLAP cubes and data warehouses could be a blog article topic all its own.
Now at the end of this article, I hope you understand the importance of considering which data store is going to best serve you and your BI workflow.  If you take the time to learn about the strengths and weaknesses of OLAP cubes and data warehouses, your research should help you determine your preference.  Company data – and the related storage of bigger amounts of it, by the year – is essentially the foundation of proper analysis and decision-making.  Solver offers a fully built, configurable 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.