<p style="font-size:125%"><em>This post was written by Michael Becker of <a href="https://www.qbeeq.pl/">QBeeQ</a>, which is a Sisense partner offering easy-to-use data acquisition systems for healthcare. Do you have some best practices you&#8217;d like to share with the world? Drop us a line at <a href="mailto:info@sisense.com">info@sisense.com</a>.</em></p>
<p>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, <a href="//pages.sisense.com/WB-Increasing-Adoption-of-BI-within-your-Organization_Webiste2.html">modern BI adoption</a> can be a very complex task, especially when applied on top of numerous and complicated systems in the organisation.</p>
<p>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.</p>
<p>In this context, there are two types of systems that might be in place:</p>
<ol>
<li>Custom-built systems in which the data structure is well known across multiple levels in the organisation (custom CRM, or statistical applications could be an example)</li>
<li>A purchased commercial <a href="https://www.sisense.com/dashboard-examples/customer-service/crm-tracker/">CRM </a>or ERP system (e.g. SAP or Microsoft Dynamics)</li>
</ol>
<p>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 <a href="/connectors/sisense-sap/">ERP dashboards</a> 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.</p>
<h2>Diving Deep into Microsoft Dynamics Data Structures and Tables</h2>
<p>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 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.</p>
<p>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 &#8211;  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.</p>
<h3>A Look into the Complexities of Structuring NAV and AX Data</h3>
<div class="aligncenter" style="font-size:80%">
<p>
<img loading="lazy" decoding="async" src="https://cdn.sisense.com/wp-content/uploads/nav-erp-data-model.png" alt="Creating the ERP data model" width="750" height="406"><br />
An example of a data model when working with Dynamics data</p>
</div>
<p>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.</p>
<p>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 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 are hard to understand without a deep knowledge of the system itself.</p>
<h2>Building Your First MS Dynamics Dashboards</h2>
<p><img loading="lazy" decoding="async" src="https://cdn.sisense.com/wp-content/uploads/erp-dashboard.jpg" alt="Dashboard with MS Dynamics data" width="750" height="422" class="aligncenter"></p>
<p>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 general indicator as well? I need to analyse 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 &#8211;  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.</p>
<p>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.<br />
You can immediately start using these dashboards and adjust them to your specific needs, which is quicker and less error prone then when working directly with MS Dynamics.</p>
<p>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.</p>
<p>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.</p>
<p><em>From our experience at QBeeQ, Sisense’s ease of use and quick deployment enables you to address this first step of BI adoption very easily. We can help with the second step, which would be creating your first data structures, reports and dashboards &#8211; if you&#8217;re interested, check out our <a href="https://qbeeq.pl/en/sisense-2/">package for Dynamics AX and NAV systems</a>. Those packages are easy to roll out and setup and they will allow you to start analyzing your data properly in very first hours of your journey with BI.</em></p>
<p>[line]<br />
<img loading="lazy" decoding="async" src="https://cdn.sisense.com/wp-content/uploads/michael-becker-profile-pic.png" alt="Michael Becker, QbeeQ" width="150" height="150" class="alignleft"></p>
<h3>About the Author</h3>
<p>Michael Becker is the owner of QBeeQ and a successful BI designer with multiple years of experience in BI, NAV and AX. Michael has extensive experience with many successful implementations of Sisense around world. To learn more, visit his <a href="https://qbeeq.pl">website</a>.</p>


Getting Started with BI on ERP: an MS Dynamics Example

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article