Open Forum

Like what you see? Discover the benefits of the D365UG/AXUG Community. Learn More

Topic: Power BI - AX 2012 R2

1.  Power BI - AX 2012 R2

Posted 30 days ago
Hello All,

I've just started testing Power BI with AX 2012 R2.  I'm able to download the tables fine, but Power BI only seems to let you create a relationship on one field.  For example, I would like to join CUSTINVOICETRANS and CUSTINVOICEJOURNAL but those require more than one join so I can't seem to get them joined together.

I'm sure there is a way but I'm really new to it and will require a HINT.  :-)

Ideas?

Also, where can I find resources on using Power BI with AX?

Thanks for your help as always.

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------


2.  RE: Power BI - AX 2012 R2

Posted 27 days ago
Sam,

You will need to use Power BI Desktop to create a new column in the CustInvoiceJournal and the CustInvoiceTrans tables that concatenates the columns in the relationship between the two tables and then use the created columns to join the two tables together.  In the screenshot below, I used SalesId and DataAreaId to create a column named SalesKey.


In Power BI Desktop you will need to create a column for both tables that concatenates the columns in the relationship in the screenshot below and then use the created columns for your join.  If you have more than one company, you may want to use the DataAreaId field in addition to the other fields.


I hope this helps.

Regards,




















------------------------------
Paul Laubenthal
Computer Programmer
Beltservice Corp.
Earth City MO
------------------------------



3.  RE: Power BI - AX 2012 R2

Posted 24 days ago
@Paul Laubenthal,

Hi Paul,

I created the new columns in both tables using the info below.

Saleskey = [DATAAREAID] & "_" & [SALESID] & "_" & [INVOICEID] & "_" & [INVOICEDATE] & "_" & [NUMBERSEQUENCEGROUP]

When I try to create the relationship I get the following message:

You can't create a relationship between these two columns because one of the columns must have unique values.

(It actually tries to create its own relationship using the RECID).

Ideas?

Let me know.

Thanks for your help in advance.

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



4.  RE: Power BI - AX 2012 R2

Posted 24 days ago
​Sam,

You created the columns in Power BI desktop and not the AOT, correct?  Once created in Power BI, you should be able drag the field you created in the CustInvoiceJournal to the same field in the CustInvoiceTrans table in the relationship view.  Try that and let me know if it works.

Regards,

------------------------------
Paul Laubenthal
Computer Programmer
Beltservice Corp.
Earth City MO
------------------------------



5.  RE: Power BI - AX 2012 R2

Posted 24 days ago
@Paul Laubenthal,

Hi Paul,

I created it directly into Power BI.  I deleted the auto relationship that it had created to the RECID and then I matched the two fields by dragging it like you mentioned.

There are some drop downs for the cardinality and the cross filter direction.  Default is Many to 1 and Single.  I left that alone.

I still get the same error message.

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



6.  RE: Power BI - AX 2012 R2

Posted 27 days ago
Hi Sam,
It's like Paul said, you need to create a custom "key" field that concatenates all the elements you need to identify a unique record.

I really fought that at first. I heard it has something to do with the tabular layout structure used in  Power BI / Excel Power Query.

Once you get used to doing it, you don't get as frustrated, but it is different.

------------------------------
Clark Walliser
Business System Analyst
Gigamon Inc.
Santa Clara CA
------------------------------



7.  RE: Power BI - AX 2012 R2

Posted 27 days ago
Thanks for your help guys!

I will try it out.

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



8.  RE: Power BI - AX 2012 R2

Posted 25 days ago
@Sam Clark Thanks for asking this question, I just went to the PBI conference in Seattle a couple weeks back and I was excited to work with what I learned until I struggled with this exact question.  Thanks to others for the answer, looking forward to getting this to work for my company.

Sherry

------------------------------
Sherry Moran
SR Business Systems Analyst
Key Technology, Inc
Walla Walla WA
------------------------------



9.  RE: Power BI - AX 2012 R2

Posted 24 days ago
@Sherry Moran, More questions to come!  Lol.

The materials out there for Power BI are overwhelming but I haven't found a whole lot of information about using Power BI with AX.  If I come up with something creative I will be happy to share.

Thanks for your support!

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



10.  RE: Power BI - AX 2012 R2

Posted 25 days ago
This is a really a question for all in this thread.  It appears that you are pointing PowerBI directly at the AX OLTP database.

How are you handling:
1. Security?
2.  Performance;  what stops someone from just hammering and locking the database?
3.  Are you allowing end-users to make their own reports?  How do you deal with the normalized data?  Or complex structures like DirParty?
4. How do you handle Enums?

I am very interested in PowerBI but haven't found a good solution that doesn't involved a custom data warehouse of some kind.

------------------------------
Corey Vantilborg
ERP Analyst
Tigercat International Inc.
Brantford ON
------------------------------



11.  RE: Power BI - AX 2012 R2

Posted 24 days ago
@Corey Vantilborg, So far I am the only Power BI user in the company and I'm sure I will be the only one creating anything.  At the moment I'm just trying to determine if this is the right tool to use.

I still have investigating to do on security, performance, and etc.

Please keep us posted if you learn anything interesting.

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



12.  RE: Power BI - AX 2012 R2

Posted 24 days ago
@Corey Vantilborg - I'm like @Sam Clark and am currently the only one at my company aware of the tools and I'm working on determining how we can leverage it in our reporting tools offering.  Currently we use Atlas and SAP Business Objects BI 4.1.  For the Business Objects we long ago created a generic userid for data inquiries that has read only abilities, we report directly against the database and don't have performance issues (3 companies in 3 countries on a single clustered instance).  Part of evaluating the Power BI tools is figuring out what could be done similarly and what would need to be done differently, the other part is ease of use compared to the tools we currently have.

I'm a track leader for Summit 2017 in Nashville for the MRP track and I just looked at the submissions list for sessions and there's a planned BI & Reporting track and within that several proposed sessions on Power BI and AX.  If you haven't already planned to go, I would certainly take a look at that.  If you do go, would love to meet up with you to hear what you've figured out.

Regards,
Sherry

------------------------------
Sherry Moran
SR Business Systems Analyst
Key Technology, Inc
Walla Walla WA
------------------------------



13.  RE: Power BI - AX 2012 R2

Posted 23 days ago
Do you think there is enough demand for a Power BI for AX SIG?
Maybe I should post that question in the PBIUG.

------------------------------
Clark Walliser
Business System Analyst
Gigamon Inc.
Santa Clara CA
------------------------------



14.  RE: Power BI - AX 2012 R2

Posted 23 days ago
​I would participate, I think it would be a great addition.

------------------------------
Sherry Moran
SR Business Systems Analyst
Key Technology, Inc
Walla Walla WA
------------------------------



15.  RE: Power BI - AX 2012 R2

Posted 23 days ago
@Clark Walliser

Hi Clark,

I would definitely participate!

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



16.  RE: Power BI - AX 2012 R2

Posted 23 days ago
@Corey Vantilborg - Our developer is becoming familiar with this product and I asked her to respond to your questions in case it might help you or the group discussion.

  1. Security?

In power BI, Row-level security (RLS)  can be used to restrict data access for given users. We can also set up filters restrict data at the row level at the time of development. An Admin can restrict data every user's persmissions.
https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

 

  1. Performance;  what stops someone from just hammering and locking the database?

When DirectQuery method is used for data connection-requests are sent to the source database, the time required to refresh a visual is dependent on how long that back-end source takes to respond with the results from the query (or queries). The recommended response time (with requested data being returned) for using DirectQuery for visuals is five seconds or less, with a maximum recommended results response time of 30 seconds. Any longer, and the experience of a user consuming the report becomes unacceptably poor. In addition, once a report is published to the Power BI service, any query that takes longer than a few minutes will timeout, and the user will receive an error. So the database will not get locked for large amounts of time.

 

When a query in DirectQuery  retrieves excessively large number of rows (more than 1 million) from the back-end data source, in which case the following error occurs:
The resultset of a query to external data source has exceeded
the maximum allowed size of '1000000' rows.

 

Row Level Security (RLS) can have a significant impact as it increases the load on the source database and potentially impacting performance 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/#limitations-of-directquery

 

 

  1. Are you allowing end-users to make their own reports?  

With help of content packs in Power BI users can copy the original dashboard/reports and makes changes accordingly.  These changes will then be Unique to that user.

 https://powerbi.microsoft.com/en-us/documentation/powerbi-service-organizational-content-packs-use-and-work-with/

https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-6-2-create-content-packs/

 

  1. How do you deal with the normalized data?  Or complex structures like DirParty?

We can set up queries in AX to deal with both normalized data and complex structures like DirParty. It would work just like a query SSRS Report but the Report will be hosted on POWER BI.

 

  1. How do you handle Enums?

Create a new dimension table with a list of possible set of constants for the Enum values and merge with the respective fact table.

https://seddryck.wordpress.com/2017/01/30/handling-enum-values-in-fact-tables-with-power-bi/



------------------------------
Lynn Litzau
Dynamics Consultant
Ascent Innovations LLC
Schaumburg IL
------------------------------



17.  RE: Power BI - AX 2012 R2

Posted 21 days ago
Group,

Sorry about the previous post above and the infomercial... To help you with the above question below is some of the secret sauce I created to flatten out the Right Data in AX and expose it in PBI.

This should help with the original question.

Empower how it works

Hope this helped and was less "salezzyy".

Regards,

Chad

------------------------------
Chad Carnes
EmpowerAX
McKinney TX
------------------------------



18.  RE: Power BI - AX 2012 R2

Posted 18 days ago
@Sherry Moran@Corey Vantilborg, and @Paul Laubenthal,

Just a follow up...

I finally gave up on getting that join to work.  Surely it's not going to be this painful using the product?

Thoughts?

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



19.  RE: Power BI - AX 2012 R2

Posted 18 days ago
@Sam Clark Unfortunately my experience is that Power BI is not suited at all to direct use with the AX2012 OLTP database.   The two simply were not designed for each other.

PowerBI seems to shine when it is sitting on a nice flattened (use readable) data warehouse of some kind.   Then PowerBI visualization(and PowerBI.coms analytics) can really start to work.

Trying to use it with the business database has only be an exercise in frustration for me.   In fact I see all these positive posts about PowerBI and I still think I must be missing something.

Now as mentioned (very inappropriately for this forum) by a partner post,  there are lots of third party solutions to this,  pre-built datawarehouse,  or tools to automate building them.  That is likely the direction we will go eventually.

------------------------------
Corey Vantilborg
ERP Analyst
Tigercat International Inc.
Brantford ON
------------------------------



20.  RE: Power BI - AX 2012 R2

Posted 17 days ago
Our experience has been that Power BI and BI tools are presentation tools that provide great functionality for a user slicing and dicing. However, Power BI and any BI like tool, heavy or light, works a lot better with a "star" data model that is a simple flat data warehouse where you pulled together the data in a format that is simpler than the NORMALIZED data model in AX. This is driven by the complexity of the functionality in AX and that makes reporting a LOT harder. We have figured out the data we need and push it out to a simpler SQL DB (data warehouse) and use tools on top of that including XL (users know how to use that) and Power BI
We looked at the BI vendors and there are some great tools there. You still have to know what you want in the report and what the BI tool presents.
Since our users were unable to define what they wanted we took a KISS approach and created a simple XL based tool so our users can see what is there tell us more of what they want.
Eventually, if our users tell us more, we will invest in a BI tool.

------------------------------
Barr Snyderwine
Hargrove, Inc.
Lanham MD
------------------------------



21.  RE: Power BI - AX 2012 R2

Posted 10 days ago
Barr,

I would invite you to come by and see me during Summit this year in Nashville.   Jet has some newer innovations going on, and I would like to talk over a way to explore some things with you.  We will have a place in the hall, so let me know if you would like to set up a formal time for a visit.

Thanks,

------------------------------
Peter Jennings
Jet Reports
Portland OR
818-266-9021
peterj@jetreports.com
------------------------------



22.  RE: Power BI - AX 2012 R2

Posted 9 days ago
@Sherry Moran@Paul Laubenthal@Corey Vantilborg@Clark Walliser

Hello Everyone,

I thought I would follow up and tell you that I've found a solution to my problem that's much easier than trying to join all those AX tables in Power BI (I don't know why I didn't think of it!).

We own Atlas 6.1 which works beautifully with AX since the tables are already joined for you.

I created a table in Atlas today accessing the following tables:

  • CustInvoiceJour
  • CustInvoiceTrans
  • CustTable
  • LogisticsPostalAddress
  • EcoResCategory

This gave me exactly what I need to do the Power BI visualizations that I was looking to do.

I have set the report up to auto-run (in Atlas) every morning  at 7:30 so the data will be fresh.

When I get it all how I want it, I'm going to move my file to One Note so it will be easier to share.

Anyway, if you already have Atlas it's something great to try.  If you don't have Atlas, you might want to look at it.  It's a great way to report off AX even if you don't use Power BI.

Hope this helps someone!

Thanks,

Sam


------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



23.  RE: Power BI - AX 2012 R2

Posted 7 days ago
Oops!  I said I would save it to OneNote.  Meant to say OneDrive...

Thanks,

Sam

------------------------------
Sam Clark
Business Systems Analyst
Team Technologies
Morristown TN
------------------------------



24.  RE: Power BI - AX 2012 R2

Posted 6 days ago
@Sam Clark Thanks for the insight.  ​We have Atlas however due to licensing costs for my global company and tool performance issues, this is not my first choice tool to use.  I'm sure I can get it to work without Atlas with some views I've created keeping our costs to deploy low with the ability to roll it out to a wide group of report writers if we decide this is a viable avenue.

We also have SAP Business Objects BI Edge 4.1 which the majority of my report writing userscurrently use for reporting which does most of what the Power Tools do, short of the ease of the dashboards, with less manipulation by the end report writers.  With it I'm able to create predefined reporting data sets similar to a data warehouse with dynamic data that the end report writer can use with confidence, knowing that table ties are predefined and correct for them and the company standards are used for naming and calculations.  We found this tool easier to roll out that Atlas because it requires even less AX database knowledge.

My goal in exploring the Power tools from Microsoft is to determine it we can move away from the Business Objects tools, at this time I think I'm still on the fence based on the work required to get report writers up to speed on all the tools required to make it work.  There's potential so I'll keep playing with it and keep attending learning sessions on the Microsoft offered tools, especially since this will be the direction with D365.

Regards,
Sherry

------------------------------
Sherry Moran
SR Business Systems Analyst
Key Technology, Inc
Walla Walla WA
------------------------------



25.  RE: Power BI - AX 2012 R2

Posted 6 days ago

Hello folks:

This thread has had a number of responses, BI seems to be a hot topic.
As part of the AXUG,
I'll be presenting a webinar: "AX 2012 BI/Reporting – How to Start Simple – Getting the Data Right" on August 8 at 11 AM EDT. We'll discuss some of the concerns we encountered related to BI and discuss/present the approach our company has used to model our data and create a simple approach to reporting in AX 2012. We will also go over some of the issues we encountered and our future plans.

You can register for the event
here. Look forward to your participation.



------------------------------
Barr Snyderwine
Hargrove, Inc.
Lanham MD
------------------------------



26.  RE: Power BI - AX 2012 R2

Posted 13 days ago
​As others have expressed, utilizing a data warehouse like Solvers BI360 data warehouse will simplify the Power BI end user experience. I have used it at client  sites and internally with great results. This will also give you the flexibility to have unified CPM and BI strategy.

------------------------------
Steve Jirschele
InterDyn BMI
Roseville MN
------------------------------



'