Forum Discussion
Creating Index on datamart SQL
smpa01 Is this datamart created in Power BI service or an external server?
AntrikshSharma created on Power BI service.
smpa01 using SSMS I noticed that the 'tables' in datamart are actually Views so even if you try to use SCHEMABINDING in the new view you somehow first need to to SCHEMABIND the original view. And for some reason I am not even able to create View while in the same workspace I can create Views in the Lakehouse as well as a Warehouse, so I am not sure if creation of view is actually allowed right now in datamarts?
AntrikshSharma I have Fabric but Lakehouse is not approved by IT yet, hence Fabric DW is not much of use to me. Till then Datamart is my best bet and all my datasources are ON-PREM.
Datamart is pretty solid (though pending any support Power Automate) due to the avaialbilty of SQL endpoints. However, the data ingested through PQ gets converted into SQL view without giving me any option to create index. I mean I can always create a table variable and / temp table and assign INDEX but that stays within the scope of query. Hence, I was looking for a better solution to have index on the table/views itself to be utilized in downstream queries seamlessly.
While I have you and it seems like you are already engineering data through it. Do you know what are the options Fabric has for data coming from SQL server configured to ON-PREM gateway. To elaborate, do notebook and Data Factory supports ON Prem yet? I know for a fact dataflow GEN2 supports it. If you have any knowledge and share with me would be great.
I will oepn the thread for others to share their views ; specfically someone from MS.