Datamarts: Cannot load a table from a datamart into a Dataset.

[altbra]

Trying to use Datamarts as a cache layer for a couple of models.

1. A table with 20mln rows loaded into Datamarts from a Dataflow without any issues.

2. Trying to load the table into a model in Power BI Desktop. Loading stops after several thousands rows. 

Checking the approach using SQL Server Managment Studio. A query can load the whole table without any issues.

[v-yetao1-msft]

Hi @altbra 

When you create a datamart , system will also create a dataset , so I don't quite understang that you said "can not load a table from a datamart into a Dataset" . Can you describe your process in detail to me ? 

Best Regards,
Community Support Team _ Ailsa Ta

[altbra]

I do not need the Dataset created automatically in my scenario so I connect to the SQL database which is advertised as an important feature. 

[altbra]

@v-yetao1-msft What info do you also need?

[v-yetao1-msft]

Hi @altbra 

The second thing you mentioned, “Trying to load the table into a model in Power BI Desktop. Loading stops after several thousands rows“, can you describe the steps in detail ?

Best Regards,
Community Support Team _ Ailsa Tao

[altbra]

@v-yetao1-msft ,

Sure.

1. Create a Datamart and load a table from a Dataflow.

2. Refresh the Datamart.

3. Copy the Connetion string from the Server settings of the Datamart settings.

4. Create an empty dataset in Power BI Desktop. Get data from a SQL Server using the connection string from step 3.

5. Try to load the data into the Dataset. This step does not work.

[MrNobody]

@v-yetao1-msft 

I don't think this needs more detail. As @altbra has stated: When importing data from a Datamart into a Dataset in desktop, the import will fail after several thousand rows have been imported.

I suspect there is a limit on the number of cells that can be exported from a Datamart - and it is this that is the issue. Sometimes I can get it to import ~100K of rows, sometimes ~25K, dependant on table width.

It is sooooo super frustrating as I want to share datasets so users can use them to supplement models their building...

[MrNobody]

@v-yetao1-msft 

Also, you get this message in the service if you try and refresh too much data.

NB: I was able to create this situation as i used a parameter to switch between full load, or first 1,000 rows.