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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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

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.

 

 

Status: Needs Info

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

Comments
v-yetao1-msft
Community Support
Status changed to: Needs Info

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

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

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

v-yetao1-msft
Community Support

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

@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
Frequent Visitor

@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
Frequent Visitor

@v-yetao1-msft 

 

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

 

MrNobody_0-1667806494146.png

 

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