Warehouse Table writeback supported from PowerApps?

[charrington]

Hi,

We tried a scenario to use PowerApps to write data to Fabric Warehouse, in order to keep all our data into Fabric.

We know we can store this data somewhere else like Dataverse, etc.  but still we wanted to see if feasible.

We tried 2 approaches:

1) Defined a virual table from Fabric Warehouse in PowerApps

We identified a Primary Key, and table was successfully created:

But the insert or update doesn't work: 

2) Adding Primary Key manually in Fabric table

For PowerApps to work with insert/update/delete records, you need a primary key. We defined it manually with some SQL as explained here: https://learn.microsoft.com/en-us/fabric/data-warehouse/table-constraints

It's also pretty much clear from the same doc that primary key enforced is not supported:

So we defined it not enforced this way:

CREATE TABLE Country2 ([countryOrRegion] [varchar](100) NOT NULL, [nb] [int] NULL)

ALTER TABLE Country2 ADD CONSTRAINT PK PRIMARY KEY NONCLUSTERED ([countryOrRegion]) NOT ENFORCED;

From SSMS we can see that the primary key exists:

But in PowerApps we receive also some (different) error:

Some additional info:

-When trying to CREATE TABLE directly in Fabric Warehouse with PRIMARY KEY, we get this error:

The PRIMARY KEY keyword is not supported in the CREATE TABLE statement in this edition of SQL Server.

-When trying to CREATE TABLE first without primary key, then ADD CONSTRAINT PRIMARY KEY, we get this error:

Enforced constraints are not supported. To create an unenforced constraint you must include the NOT ENFORCED syntax as part of your statement.

-We tried also different types for the primary key: INT, GUID...PowerApps won't update.

But Primary Key not enforced seems not sufficient for PowerApps to work.

So the question is: is this a supported scenario, and if not, is it in the planning to be supported?
Again we do have some workaround to store the data somewhere else, but this is more to better understand the Fabric concepts.

Thanks!

[Anonymous]

Hi, 

I am following documented scenario and trying to create a table with primary key in Fabric DataWarehouse, but looks like even provided sample did not work here.

@HimanshuS-msft can you help please? Thanks.

source: Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn

[KevinConanMSFT]

Greetings @Anonymous!  This is a recently discoverd issue that we are working on resolving.  If you run the statements seperately they work.  The issue happens when you try to run them at the same time.

[Anonymous]

Thanks @KevinConanMSFT it was very helpful for as right now.  

@charrington thanks for your time and investigaton.  What I am trying to achieve is only to build star schema above Lakehouse (silver) data in Warehouse (Gold) layer.  

As using SCD2 for dimensions, I want to create surrogate key there and not to use Id from source system.  

[charrington]

@Anonymous Welcome!

You might wanna look also at this thread in case you haven't come across it, discussing identity fields. The Guid trick might help or not in your case, not sure.

[charrington]

Hi @Anonymous 
I just tried and got the same error as you...weird...I am 100% sure it was working when I posted the issue.

It looks they are doing a lot of changes...they will probably fix it at some point.

But anyway the Primary key is not very useful right now in Fabric. You cannot enforce it. So it's more for performance only. Not sure what you wanted to acheive with this. It's not meant for transactional data, at least for now.

Thanks

[KevinConanMSFT]

At this time, Primary Key, Unique Key and Foreign Key can only be added with the ALTER TABLE command.  I apologize for the inconvience!

[charrington]

Thanks for the reply.

But even with ALTER TABLE, we are not able to add a Primary key enforced. We are able to alter the table to add Primary key NOT ENFORCED only. But this is useless in the described scenario, with PowerApps, which requires a "true" Primary key.

[KevinConanMSFT]

Unfortunately, enforced Primary Key is not supported at this time.

Primary, foreign, and unique keys - Microsoft Fabric | Microsoft Learn

I am keeping an eye on your request to possibly add it to our roadmap in the future.

If folks want this feature, I encourace you to upvote it and add comments!  Link to it is here: Microsoft Idea

[charrington]

The idea is already added. Thanks

[HimanshuS-msft]

Hello @charrington,

 Thanks for using the Fabric community . I think the issue which you are facing is that  power apps wants you to have a primary and in warehouse  PRIMARY KEY is only supported when NONCLUSTERED . Let me know if this is not correct .

Since warehouse supported Massively parallel processing so having a primary does slows down the data load process . Can I request you to post the idea here ?

Since in your case the end goal is to get the data in Fabric , have you explored the option to write to the lakehouse in a file and then convert that to table . 

Thanks
HImanshu

[charrington]

Thanks for the reply

Yes you correctly understood the issue.

But the idea of loading a file to Lakehouse in this scenario is not possible. First PowerApps, if you know a little about it, is a low-code app builder, so users can interact with live data, modify it or insert new records etc. So it's not about loading some data, the app sends SQL operations to the database for one or multiple records.

Thing is in PowerApps there is no connector so far for Fabric. The closest we can use for now is the SQL server one:

When creating a new connection, I enter the SQL endpoint string. This connection string is the same for Lakehouse and Warehouse. So using Lakehouse or Warehouse won't make a difference.

Based on what you said with parall processing, it might make sense that primary key is not supported. But maybe another type of table to support Operational Data Store scenarios could be added. I will add the idea. 

Have a good day