Power BI connection with AEP

[Michael_Soprano]

Which option do you rather choose?

If I click import then all 5GB dataset is downloaded?

How it work in details?

If I create custom field group then its always nested on 1st level:

And then how to load these records as a flat table? I know about adding ?flatten but I see that sometimes it does not work ....

[TylerKrause]

Hey Michael - for me I have always done all of my work within the query services UI, and once I am certain of the result of my query, at that point I run it as a Data Connectivity import to just pull the full table into BI. 

Within your query, if you select the paths that you need for each of your data points as opposed to doing a select *, you should be able to get a really nice flat table for whatever you're using.

I've pulled in up to 7 GB via this method and have had pretty good success.

Let me know if you have any other thoughts or run into any problems.

Tyler Krause 

[Michael_Soprano]

@TylerKrause thanks for response!

You request data from RTCDP or Data Lake? 

I have really simple schema like that:

(its dummy data for demo purposes)

However the result in Power BI is like that:

Tried both import and directquery. Do you any hints?

[SubeAEP · Accepted solution]

Hi @Michael_Soprano, you can try this. Click on advanced option and write your query to fetch all columns or few columns that you need. I generally use ?FLATTEN in the Database option, and it works perfectly for me. 

[Michael_Soprano]

Did not about this SQL statement option. I am newbie to Power BI 

Thanks!

Event nested XDM objects (nested ones) are becoming flat??

[TylerKrause]

Are you including the query that you're hoping to run within the SQL statement editor? What you're seeing on that column seems to indicate that it's just trying to grab the object that has everything on it as opposed to your table. 

I bet you would find success with the select * from dataset, though depending on schema config, I have had to do a select and then break out each column as opposed to the * to get what I'm looking for.

Hope this does the trick!


Best,
Tyler Krause

[Michael_Soprano]

Why did not Adobe include this option in the documentation.....

Thanks Tyler!