Let users dynamically change database schema

[ollie_mills]

I've been wracking my brains trying to think of the best solution here...

At my organisation we have identical SQL schemas for US and UK data (e.g, uk_view_name, us_view_name). We have several large tables/views with 100m+ rows

Alot of our reports are fed from central star schema power bi datasets. In general, the reporting requirements are the same for US and UK report consumers (any differences in reporting logic would be pushed down into SQL). There are 3 solutions (that I can think of) to serve up the US and UK reports to end users:

• Combine the US and UK data models into one pbix (with no connections between each model), create 1 report with a US tab and UK tab, and just use the respective tables for each report tab. My worry here is performance issues...
• Keep the data models in seperate pbix's and create seperate UK and US reports. My worry with this one is the two reports falling out of sync in terms of data definitions. If a DAX measure is updated in the UK report I can imagine report creators may forget to do the same in the US report
• Union the US and UK datasets and create a column for Country which the users can filter on. Again i'm worried about performance on some of those larger tables and also the refresh times might cause issues on our tenancy

Ideally i'd like to have a combined US/UK star schema model in service, and be able to specify which schema I want per report (without loading all the data, ie at power query level), but have this exposed on the front end of the report so regular users don't need to go into PQ to update the parameter.

I've seen recently that dynamic M query params have been announced for sql server, but I think this is only for direct query, and i'm not sure if DQ would be ideal given the size of some of the tables we're working with: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

Are there any solutions i'm missing? What is best practice in situations like this? Curious to hear peoples opinions!

[ollie_mills]

@StefanoGrimaldi What are the benefits of using a dataflow instead of just appending the datasets into 1 big model? Can row level security be set at the dataflow level?

[Anonymous]

Hi @ollie_mills ,

When to use dataflows.

Dataflows are designed to support the following scenarios:

1. Create reusable transformation logic that can be shared by many datasets and reports inside Power BI. Dataflows promote reusability of the underlying data elements, preventing the need to create separate connections with your cloud or on-premise data sources.

2. Expose the data in your own Azure Data Lake Gen 2 storage, enabling you to connect other Azure services to the raw underlying data.

3. Create a single source of the truth by forcing analysts to connect to the dataflows, rather than connecting to the underlying systems, providing you with control over which data is accessed, and how data is exposed to report creators. You can also map the data to industry standard definitions, enabling you to create tidy curated views, which can work with other services and products in the Power Platform.

4. If you want to work with large data volumes and perform ETL at scale, dataflows with Power BI Premium scales more efficiently and gives you more flexibility. Dataflows supports a wide range of cloud and on-premise sources.

5. Prevent analysts from having direct access to the underlying data source. Since report creators can build on top of dataflows, it may be more convenient for you to allow access to underlying data sources only to a few individuals, and then provide access to the dataflows for analysts to build on top of. This approach reduces the load to the underlying systems, and gives administrators finer control of when the systems get loaded from refreshes.

Please refer.

Introduction to dataflows and self-service data prep

Q2. 
RLS is specific to the dataset, connect to the data flow and you can still configure RLS for the dataset.

Blogs that may be helpful to you.

Dataflow Archives

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[StefanoGrimaldi · Accepted solution]

have yo utry dataflows? you could create a dataflow to model each one separated, and make tranformations to do a table matching format and them combine them in a new single dataflow that just append the table having a column that separates the UK vs US fact info and a global dim tables, them using users rules you can specify what data each user group have permits with the use of roles