Data Warehousing for Microsoft Dynamics AX

In this article, I will go about demystifying the purpose and functionality of data warehousing for Microsoft Dynamics AX customers, so that you can hit the ground running in your search for the best Business Intelligence data storage source for your organization.

Data continues to rule the business world, driving more informed decision-making for better business practices.  Hence, the rise of data warehouses.  You’ve definitely heard of them, but do you know the ins and outs of the prominent data sources?  For Microsoft Dynamics AX users, you might have questions – several even – but don’t know where to start, don’t feel like you have the time to learn about the software, etc.  That’s where this article comes in, answering the most common questions Dynamics AX users might have, so you can understand the product category in order to figure out your own data warehousing goals.

Let’s start by talking about who implements and configures a data warehouse.  The new breed of commercial data warehouse solutions will come “out of the box” completely empty and ready for your AX and any other source data to be loaded into the warehouse.  There are consultants, who are specialists in extracting, transferring, and loading (ETL) of data, that can install the warehouse and automate the ETL process, so your information will load.  From there, a consultant who focuses on acceleration, optimization, and simplification of financial reporting, budgeting, and/or dashboard process will help you in querying data from the data warehouse.  After you deploy the warehouse and train on how to use it, the business end user can manage the warehouse, cutting out the IT middlemen.
Just to clarify, data warehouses are hi-tech databases that are multi-dimensional in nature.  Still not able to (literally) picture a data warehouse?  That’s okay, because they are not tangible; they do not take up any space in your server room.  Data warehouses are databases that offer digital storage, housed on a server, which can be its own platform or on a shared server.  When I first head of data warehouses, I confused them with external hard drives, which offer digital storage for multiple types of files, applications, and/or software.  Warehouses allow you to store diverse kinds of transactional and operational data in one place.  They are like multi-dimensional versions of a spreadsheet, meaning that you organize your data in aggregated, dynamic, and easy ways, while eliminating error with modern database-driven functionality.
When is a good time to start considering a data warehouse?  If you and your team are beginning to tire of manual documentation, errors, and data management and analysis within an application like Excel, it might be the right time to start looking at warehouse solutions.  Additionally, if reporting directly from your AX database is sluggish because multiple users are simultaneously querying substantial data sets, you will achieve stability and high performance with the assistance of a data warehouse.  One more scenario you might be finding yourself in: your managers require not only data for reporting, budgeting or dashboards from Dynamics AX, but also from other data sources, like sales or payroll systems.  In this case, it would be significantly easier to rely on a unified database, like a warehouse, to consolidate your data sources.
Data warehouses used to only be built as a development project, produced particularly for an organization and oftentimes, taking multiple years to complete.  Nowadays, commercialdata warehouses are built as configurable solutions with non-technical interfaces that you can purchase and set up to better manage and analyze your data.  Today’s home-grown warehouses are typically built and maintained through a data source management program, like Microsoft SQL Server Management Studio, while some BI vendors have built commercial data warehouse solutions.  For example, Solver has built their own application, appropriately called the BI360 Data Warehouse Manager, which is a non-technical interface for business end users to manage the data warehouse, positioned within the BI360 suite of BI tools.
Because they serve similar purposes, data warehouses are usually compared to online analytical processing (OLAP) cubes.  Plenty of BI tools suggest or even require OLAP cubes to manage and analyze data.  Cubes are not designed as a transactional SQL server database, so they have to be managed by someone who has OLAP experience and skills, like MDX query language, because of their complexity.  Moreover, they focus on the analytical, as opposed to transactional data.  Data warehouses are organized by subject, and you are able to duplicate diverse types of data for stable, easy analytics.
Designed for the business end user, you can manage today’s commercial data warehouses.  More specifically, you can utilize the intuitive configuration functionality to set up replications of your data to the warehouse one time or set up a routine with a simple click of a button, replicating at any time you want or need.  The best development in today’s business technology world is that software solutions are more often than not built in an effort to avoid IT involvement, so that business end users can access, manage, and analyze their data when and where they want.  And data warehouses have followed that trend.  Commercial warehouses are Microsoft SQL server relational databases, structured around subjects, as in account, product, and sales.  They are naturally good interfaces for financial consolidations and cross module financial reporting.  They stand on their own, usually without any concurrency control features, transactions processing or recovery required.  Data warehouses are powerful tools with native BI capabilities, like currency conversion, dimension maintenance, period logic, tree (hierarchy) design, data cleansing, eliminations, and techniques for data integrations to streamline management and analysis of your data.
Data is not going anywhere.  It will only become bigger in size and significance in terms of decision-making and roadmap planning for companies, especially as enriched reporting, budgeting and data visualizations become more desirable because of consolidations of diverse data types.  If you are shopping for a solution to storing your Microsoft Dynamics AX data in addition to other data source information without much IT involvement, data warehouses can likely offer you exactly what you want.  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 AX experience.