Cookies help us display personalized product recommendations and ensure you have great shopping experience.

By using this site, you agree to the Privacy Policy and Terms of Use.
Accept
SmartData CollectiveSmartData Collective
  • Analytics
    AnalyticsShow More
    sales and data analytics
    How Data Analytics Improves Lead Management and Sales Results
    9 Min Read
    data analytics and truck accident claims
    How Data Analytics Reduces Truck Accidents and Speeds Up Claims
    7 Min Read
    predictive analytics for interior designers
    Interior Designers Boost Profits with Predictive Analytics
    8 Min Read
    image fx (67)
    Improving LinkedIn Ad Strategies with Data Analytics
    9 Min Read
    big data and remote work
    Data Helps Speech-Language Pathologists Deliver Better Results
    6 Min Read
  • Big Data
  • BI
  • Exclusive
  • IT
  • Marketing
  • Software
Search
© 2008-25 SmartData Collective. All Rights Reserved.
Reading: Getting Started with BI on ERP: an MS Dynamics Example
Share
Notification
Font ResizerAa
SmartData CollectiveSmartData Collective
Font ResizerAa
Search
  • About
  • Help
  • Privacy
Follow US
© 2008-23 SmartData Collective. All Rights Reserved.
SmartData Collective > Business Intelligence > Getting Started with BI on ERP: an MS Dynamics Example
Business Intelligence

Getting Started with BI on ERP: an MS Dynamics Example

Eran Levy
Eran Levy
7 Min Read
SHARE

For any business intelligence solution to be effective, it needs to have demonstrable value for business users and to actually gain some traction within the organization. However, modern BI adoption can be a very complex task, especially when applied on top of numerous and complicated systems in the organization.

Contents
Diving Deep into Microsoft Dynamics Data Structures and TablesA Look into the Complexities of Structuring NAV and AX DataBuilding Your First MS Dynamics Dashboards

For any business intelligence solution to be effective, it needs to have demonstrable value for business users and to actually gain some traction within the organization. However, modern BI adoption can be a very complex task, especially when applied on top of numerous and complicated systems in the organization.

Overly complicated data structures, as can often be found in ERP platforms, can become a critical roadblock to the overall adoption of the BI solution, as they can make it difficult for end users to actually see results after the software has been implemented.

More Read

Socialytics: Social Analytics Earns Its Portmanteau
CRM Paradigm Shift
New RockSolid product site
Technology’s Impact on Accounting and Business
IBM – A 21st century education for 21st century students

In this context, there are two types of systems that might be in place:

  1. Custom-built systems in which the data structure is well known across multiple levels in the organization (custom CRM or statistical applications could be an example)
  2. A purchased commercial CRM or ERP system (e.g. SAP or Microsoft Dynamics)

The first type of system poses less of a threat to BI adoption as its structure can be easily and quickly identified and matched to the data structure in your BI solution, enabling rapid development of ERP dashboards based on these structures. The second type, on the other hand, can become a real challenge in more complicated scenarios. Let’s examine an example of the second scenario, based on the Dynamics ERP platform.

Diving Deep into Microsoft Dynamics Data Structures and Tables

Challenges when deploying BI on top of your ERP system can be numerous and unexpected. Two of the most notable obstacles you might encounter with modern ERP systems are complicated data structures and business processes that do not reside in the data itself but are hidden within the ERP program’s code. These two factors can prove to be a real challenge and slow down initial deployment significantly. Microsoft Dynamics NAV alone, in the default setup, holds almost 1000 tables with over 50 000 different fields. This is a lot of data to browse through in order to get something meaningful.

Experience shows that when it comes to successful implementation of a BI solution, 20% of your success comes from your knowledge of the BI tool itself – and the easier the tool the quicker you can start leveraging its full potential and the learning curve is much less steep. The remaining 80% is the knowledge about your data structure and how to manipulate it to get the desired results. Hence, the more complicated and sophisticated the ERP you’re using, the more vital it is to be deeply familiar with its data structure in order to successfully implement a BI solution on top of it.

A Look into the Complexities of Structuring NAV and AX Data

Creating the ERP data model
An example of a data model when working with Dynamics data

Working with NAV’s data structure is quite problematic, as it is fairly complicated and also contains several hidden features within the data that might be hard to extract. In terms of data preparation, one will face problems with Option Fields (there is a similar mechanism in Dynamics AX called enums), that are stored as meaningless numbers, with their explanation hidden within NAV code. Taking care of most important ones, like Document Type allows you to work with NAV data with greater precision and accuracy.

Another difficulty with NAV is the structure of document and transaction tables. These are spread across over 15 different tables and even simple sales analysis might be not that simple from a NAV database perspective. In AX you have a quite powerful functionality called Financial Dimension. Although these are primarily used as General Ledger dimensions, they can be actually found in any transaction within AX. To get them to work with all transactions you need to perform quite complicated data transformation. There are many ‘hidden’ things within NAV and AX data structure that is hard to understand without a deep knowledge of the system itself.

Building Your First MS Dynamics Dashboards

Dashboard with MS Dynamics data

The next decision you need to make is which areas to represent first in your dashboards, and how to do it. Should I present sales as a timeline only, or as a general indicator as well? I need to analyze Sales, Purchases, Finances and stock, how to approach data so I could get those areas working as quick as possible? Those are the types of challenges you will encounter when starting with any ERP system from scratch. Some systems are easy to handle, and some less so – both NAV and AX are the latter. Once you get your data structure ready for a particular business need you are can go on to build a dashboard.

The way you structured your data will enable you to build dashboard reporting for specific business needs such as sales, purchasing, inventory, finance, receivables, etc. You can immediately start using these dashboards and adjust them to your specific needs, which is quicker and less error prone than when working directly with MS Dynamics.

The last thing one has to take into consideration is how the data structure can influence the way certain measures are calculated on dashboard level. A good example of this would be Amounts for General Ledger transactions in NAV which has Closing Entries at the end of each year, making analysis across multiple years a little more difficult.

Having these basic areas already covered at day one enables you to get a head start on your organization’s adoption of business analysis tool, and it is much easier to adjust an already existing structure to match your business specific needs than it is to start from scratch.

Share This Article
Facebook Pinterest LinkedIn
Share

Follow us on Facebook

Latest News

sales and data analytics
How Data Analytics Improves Lead Management and Sales Results
Analytics Big Data Exclusive
ai in marketing
How AI and Smart Platforms Improve Email Marketing
Artificial Intelligence Exclusive Marketing
AI Document Verification for Legal Firms: Importance & Top Tools
AI Document Verification for Legal Firms: Importance & Top Tools
Artificial Intelligence Exclusive
AI supply chain
AI Tools Are Strengthening Global Supply Chains
Artificial Intelligence Exclusive

Stay Connected

1.2kFollowersLike
33.7kFollowersFollow
222FollowersPin

You Might also Like

Collaborative BI – What Women and Men Want

5 Min Read
artificial intelligence AI
Artificial IntelligenceBest PracticesBusiness IntelligenceExclusiveFeatured

How Artificial Intelligence is Transforming the Way eCommerce Businesses Perform

7 Min Read

SaaS economics

3 Min Read

The language of Statistics

1 Min Read

SmartData Collective is one of the largest & trusted community covering technical content about Big Data, BI, Cloud, Analytics, Artificial Intelligence, IoT & more.

ai chatbot
The Art of Conversation: Enhancing Chatbots with Advanced AI Prompts
Chatbots
data-driven web design
5 Great Tips for Using Data Analytics for Website UX
Big Data

Quick Link

  • About
  • Contact
  • Privacy
Follow US
© 2008-25 SmartData Collective. All Rights Reserved.
Go to mobile version
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?