Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
I_NeedMorePower
Helper III
Helper III

Dynamics 365 O&F: After Retrieving The OData, Can't Find The Tables I need Between The Results

Hello Community!

To be clear I'm totally new to Power BI and this is my first project with it, so please bear with the newbie Smiley Happy

 

My issue is after selecting OData and putting the Dynamics 365 F&O URL added with "/data" at the end of the URL, Lots of tables have been shown but the ones I need are not shown. The tables I need are "CustInvoiceJournal" and "CustInvoiceTrans", since I will be doing a daily sales report, I need those 2 tables.

 

Maybe they does appear with different names? or should I include something in the URL?

What is the issue? any advices are appreciated since I'm still learning.

Thanks in advance!

2 ACCEPTED SOLUTIONS
v-yuezhe-msft
Employee
Employee

@I_NeedMorePower,

Do you have SQL backend database for  Dynamics 365 O&F in the UAT server? If you have, please use SQL Server connector in Power BI Desktop to connect to this database and check if you can see the two tables.

When you use odata connection in Power BI Desktop, you can never see all your tables, views, only the entities which are made available. So you would need to connect using the entity name. However, based on my research, it can sometimes be difficult to find out what name should be used. In Visual Studio, you can see what name is used in the Public collection name for the entity, that is the one that is used for OData.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

v-yuezhe-msft
Employee
Employee

@I_NeedMorePower,

For production environment, you would need to create new entity as you are unable to connect to SQL. Regarding to the entity questions, please post questions in Dynamics 365 forum to get better support.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-yuezhe-msft
Employee
Employee

@I_NeedMorePower,

For production environment, you would need to create new entity as you are unable to connect to SQL. Regarding to the entity questions, please post questions in Dynamics 365 forum to get better support.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you Lydia for your kind help! 🙂

v-yuezhe-msft
Employee
Employee

@I_NeedMorePower,

Do you have SQL backend database for  Dynamics 365 O&F in the UAT server? If you have, please use SQL Server connector in Power BI Desktop to connect to this database and check if you can see the two tables.

When you use odata connection in Power BI Desktop, you can never see all your tables, views, only the entities which are made available. So you would need to connect using the entity name. However, based on my research, it can sometimes be difficult to find out what name should be used. In Visual Studio, you can see what name is used in the Public collection name for the entity, that is the one that is used for OData.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your informative reply!

After I did some reading I just understood what you explained since i'm still new to D365O&F.

So basically OData can only retrieve data entitites which are something like de-normalized tables of Dynamics tables.

 

It will be difficult to get the desired fields then, since the data entities has names "Puplic Collection Name" which are different than the original tables as you mentioned.

 

So the other workaround is to connect to the SQL database directly so we can retreive the original tables, but in D365O&F, microsoft does not allow access to the SQL database in the production environment.

So how can we retrieve the original tables? Or is there some kind of map or dictionary that shows the entities and which tables it's derived from?

 

Thanks!

Hello and thanks for your time to help!

 

I can access the SQL in the dev environment but in the Production environment, I can't.

So, if I wanted to work on the production environement what should I do? How will I be able to retrieve all the dataset?

 

Thanks again!

I_NeedMorePower
Helper III
Helper III

BTW: The dynamics 365 enviroment i'm using is a UAT one if it leads to something.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors