Only allow DirectQuery datasets

[Norm]

Hi,

Do any of you know if there's a way to prevent uploading a PBIX file that contains data in "import" mode and only allow Datasets that are using DirectQuery sources?

My problem is that I work in the healthcare sector and because of regulations we don't want patient data to be physically stored in the cloud. That's why we are only using SQL connections in DirectQuery mode. But if fear that sooner or later some BI analyst will add a data source to a PBIX file and select Import instead of DirectQuery and publish that PBIX to the cloud. We are uploading the PBIX with the Rest API and I haven't seen an option that would do what I would like to do.

Thanks!

Norm

[lbendlin]

You can't prevent it but you can audit it. Dataset uploads are in the audit logs. If you had a rule that you have to be admin of any workspace then you could then disable the dataset as needed.

You need a non-technical process where your developers have to sign an agreement not to create import datasets.

Remember that you can connect to the dataset via XMLA .  So another option would be to load the .PBIX into a holding workspace, then connect to it from an XMLA client and then run the scripts against the partitions.

Here's a crazy idea.  You know the .pbix files are actually ZIP archives, right? Somewhere in there is probably a flag that has the storage mode. My money is on DataMashup...

[lbendlin]

ok I missed slightly - it is actually in DataModelSchema

     "partitions": [
          {
            "name": "Dates-3beb2295-2712-48ff-bf4b-e7d596a9517d",
            "mode": "import",
            "state": "ready",
            "modifiedTime": "2020-07-24T02:15:37.063333",
            "refreshedTime": "2020-07-24T02:15:37.063333",
            "source": {
              "type": "m",
              "expression": "let\n    Source = Sql.Database(\"xxx\", \"yyy\"),\n    dbo_Dates = Source{[Schema=\"dbo\",Item=\"Dates\"]}[Data],\n    #\"Kept First Rows\" = Table.FirstN(dbo_Dates,1)\nin\n    #\"Kept First Rows\""
            }
          }
        ],

[Norm]

Thanks for your reply.

We though of checking the content of the PBIX file for the DataMashup file. Unfortunately that file does not exists when the PBIX file is saved using the new "Enhanced metadata format" (I guess that information is in the DataModel file but it seems to be a binary file in a proprietary format). As for the DataModelSchema I think it's only in PBIT files (and I don't think the Rest API allows to upload a PBIT). 

[lbendlin]

Yes, that XPress9  compression thing is a pain. Haven't found a way around that yet.  XMLA is probably your best bet.

select [Name], [Mode] from $SYSTEM.TMSCHEMA_PARTITIONS

Mode 0 is import, mode 1 is Direct Query