cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
I_NeedMorePower Regular Visitor
Regular Visitor

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

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@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

Moderator v-yuezhe-msft
Moderator

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

@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
I_NeedMorePower Regular Visitor
Regular Visitor

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

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

Moderator v-yuezhe-msft
Moderator

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

@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

I_NeedMorePower Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

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!

Moderator v-yuezhe-msft
Moderator

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

@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

I_NeedMorePower Regular Visitor
Regular Visitor

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

Thank you Lydia for your kind help! Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 276 members 3,333 guests
Please welcome our newest community members: