tables with above 30Millions

[Anonymous]

hi all

i am working for healthcare project where i have like 10 fact tables with more than 20 milllion records .

like the highest table record count is like 70 milllion . can you help me with your past expereinces on below questions:

did you use direct query to load the million of records?.

in this case should be go with azure analysis services ?. since i dont have more measures being requested by business should i go with cube or else can i new featire of aggreagtions in composite model ?.

can you atleast guide me in load point of view whether i should go with import or direct query ?

[v-cherch-msft]

Hi @Anonymous

It seems you may use import mode. Below are some documents for your reference.

http://radacad.com/directquery-live-connection-or-import-data-tough-decision

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery

Regards,

Cherie

[Anonymous]

hi @v-cherch-msft

can  you let me know the reason for import mode so that i can put these points to my business team .meanwhile i can go through the links shared by you

[v-cherch-msft]

Hi @Anonymous

You may check the Limitations of DirectQuery. There is a 1 million row limit for returning data when using DirectQuery. 

Regards,

Cherie

[Anonymous]

hi @v-cherch-msft : Even if we are pulling the data from Azure SQL data warehouse /SQL database is 1 million limitations apply ?.

If going to Azure analysis services or ssas cube or tabular cube and puling the data from Live connection is there any limitations for this also?

[v-cherch-msft · Accepted solution]

Hi @Anonymous

That's different. If you select import, then you define a query against that external SSAS source, and the data is imported as normal. If you select to connect live then there is no query defined, and the entire external model is shown in the field list. If you select DirectQuery, as visuals are built, queries are sent to the external SSAS source. However, unlike DirectQuery, there is no sense in which a new model is being created; in other words, it's not possible to define new calculated columns, hierarchies, relationships, and so on. Instead you are simply connecting directly to the external SSAS model.

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about

Regards,

Cherie

[LivioLanzo]

If you structure the model correctly I think you should be fine with import. Try that first