Click here to Skip to main content
14,828,012 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 4 Mar 2021

Stats

2.2K views

Designing Business Intelligence Reporting & Dashboards with Tableau Desktop using Microsoft Dynamics

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
5 Mar 2021CPOL2 min read
Tableau is one of the powerful reporting tool which is on the market for over a decade. Here's a quick tour in designing BI reports with Tableau using Microsoft Dynamics AX 2012 R3/ Microsoft Dynamics 365 Finance and Operations.
In this tip, you will see how to design reports for on-premise Dynamics AX 2012 R3 (Dynamics 365 Finance and Operations) hosted instances utilizing Tableau Desktop and also with other sources of input.

Introduction

Designing the reports for on-premise Dynamics AX 2012 R3 (Dynamics 365 Finance and Operations) hosted instances utilizing Tableau Desktop and also with other sources of input. However there are different products that Tableau offers, but this article focuses on few Tableau Desktop application capabilities.

The version that was used for demonstration here is Tableau 10.1.

The article mainly focuses on the below points utilizing the Tableau Desktop client :

  1. Using SQL Server Connection with the TABLEAU DESKTOP
  2. Creating Datasources Using ODATA Service
  3. TABLEAU WITH SSAS CUBES

1. Using SQL Server Connection with the TABLEAU DESKTOP

Creating datasources using SQL Server, provide the authentication details:

Image 1

Copy the SQL query in the Preview the results in the Custom SQL Query design.

Sample query used for Item Summary demonstration purpose only:

SQL
SELECT UPPER(IT.DataAreaId) AS COMPANY,IT.ItemId,ISNULL((SELECT SUM(SL.QtyOrdered) _
FROM SalesLine SL WHERE SL.ItemId = IT.ItemId _
AND SL.DataAreaID = IT.DataAreaID AND SL.Partition  = IT.Partition), 0)   _
AS QtySold, ISNULL((SELECT SUM(SL.LineAmount) FROM SalesLine SL _
WHERE SL.ItemId = IT.ItemId   AND SL.DataAreaID = IT.DataAreaID  _
AND SL.Partition  = IT.Partition), 0)  AS Sales,  ISNULL((SELECT SUM(PL.QtyOrdered) _
FROM PurchLine PL   WHERE PL.ItemId  = IT.ItemId  AND PL.DataAreaID = IT.DataAreaID _
AND PL.Partition  = IT.Partition), 0)  AS QtyBought, ISNULL((SELECT SUM(PL.LineAmount)  _
FROM PurchLine PL  WHERE PL.ItemId     = IT.ItemId  AND PL.DataAreaID = IT.DataAreaID _
AND PL.Partition  = IT.Partition), 0)   AS Purchased  FROM InventTable IT _
WHERE  IT.Partition = 5637144576 Group By IT.DataAreaId, IT.Partition, IT.ItemId

Image 2

Once the Query is added, you can drag the dimension, measures, SUM fields onto the layout to slice and dice the data columns and rows the way you want.

Image 3

2. Creating Datasources using ODATA Service

Validate OData service is accessible in IE browser before consuming it. (Please prefer using full naming convention instead of localhost.)

/$metadata - will list out all the Entities that are available in the system as demonstrated in the image below:

Image 4

Consuming the OData service entities from the available datasources.

Once the specified entity is set, the user can then drag the dimensions and measures from the available list onto the page layout.

Image 5

3. TABLEAU WITH Dynamics AX - SSAS CUBES

BELOW REPORT WAS DESIGNED USING SSAS OUT OF THE BOX CUBES.

Provide the Server Connection details of Microsoft Analysis Services, it should present you with the following screen displaying available list of cubes.

Image 6

Once the specified cube is set, the user can then drag the dimensions and measures from the list onto the design page layout to slice and dice the data according to the requirement.

Image 7

After designing these pages, you can upload and publish/deploy them to the Tableau Cloud Server and share the content to the users, so they can access through the dashboards. Tableau is now one of the Salesforce product which was recently acquired in 2019. There are lots of customers/clients still using this tool.

Basically, this is to give a quick tour through the Tableau options available in the desktop version which were captured at a point in time, since it was designed some time ago. I had faced some challenges initially and looked for some online help while learning & preparing this demonstration.

Below is the link to the discussion with Dynamics user community:

History

  • 5th March, 2021: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Venkat Chaitanya Kanumukula
Technical Lead
United States United States
Mr. Kanumukula is a IT-Professional and has been in the industry since 1997.
• Around 15+ years of experience in the areas of system design & architecture, application programming, development, testing, deployments, execution with implementations & Roll-outs of business applications in cloud/on-premise.
• Experience with Dynamics 365 for F&O/Dynamics AX, D365 for Business Central/NAV,(Inc -Visual Studio.Net, X++, AL, C#, CSide, SharePoint, SQLServer, SFDC, Power-BI, Power Apps, Dataverse/Common Data Service, CRM, SSIS/SSRS/SSAS, BizTalk, IOT, IIOT, JAVA, AWS, GCP, Azure, API, etc)
• Experience in managing Operations & Data Center maintenance both on-premise & cloud hosting, infrastructure/applications assessments & build systems.
• Experience in ERP upgrades, Mulesoft API's,MS Dynamics 365,Azure administration & hosting, LCS-Life Cycle Services.
• Experience with Commitment to Quality, technical quality assurance(before, during & after development). Create partnership with project manager to give technical assistance regarding important decisions.
• Well-Versed with Agile, SCRUM & CMMI process & methodologies to support rapid iterative Quality software development.

A highly motivated, self-starter & problem solver with multi-tasking skills, Had managed and served through an established process to get the job done in a timely and efficient manner with an eye on every detail during ERP-implementations. Flexible to work under stress and have ability to prioritize workload and stay organized in a fast-paced environment.

Learned & adapted to new technologies & tools at ease, aggressively delivered tasks on-demand at a fast-pace satisfying the needs of product owners, product development, program managers, Vendors, Higher Management, Internal/External Clients.

Have a proactive & positive attitude with a willingness to do what it takes to complete the job. Self-managed work style within a team environment. Extremely detail & Customer service oriented.

Comments and Discussions

 
-- There are no messages in this forum --