Forum Discussion
Dynamic calculated table
What you describe in this post is not what I would recommend as an approach. If you are building lots of calculated tables then this is unlikely to be the right approach
Direct query is designed to be used in one of two use cases. Either you have a data warehouse configured with a star schema and a very large data set (difficult for import mode), or alternatively you have something similar but with a very high velocity of data changing (frequency of refresh is an issue). If these are not your motivations, then you may be better to use import mode. Import mode is designed to allow you to load the data from your source and make changes to the table structure at the same time. Ideally I would recommend you create a star schema by writing views/queries at your data source and then loading them into Power BI Desktop. Alternatively you could use power query to create the star schema/table structure during data load.
That’s my five cents worth
What you describe in this post is not what I would recommend as an approach. If you are building lots of calculated tables then this is unlikely to be the right approach
Direct query is designed to be used in one of two use cases. Either you have a data warehouse configured with a star schema and a very large data set (difficult for import mode), or alternatively you have something similar but with a very high velocity of data changing (frequency of refresh is an issue). If these are not your motivations, then you may be better to use import mode. Import mode is designed to allow you to load the data from your source and make changes to the table structure at the same time. Ideally I would recommend you create a star schema by writing views/queries at your data source and then loading them into Power BI Desktop. Alternatively you could use power query to create the star schema/table structure during data load.
That’s my five cents worth
- Thanks Matt.
We have tried to build the tables in query editor but the performance was very poor that time.
We do have frequently changing data so import mode is not apt.
We have done the data modelling using dax.So at this point of time redoing the whole aggregations at the backend is not feasible since we are nearing the go live.
Any other alternative to resolve the 1 m error.
Also when we connect the backend tables without any calculations in powerbi the performance is very bad.Taking a lot of time for a drag and drop of fields in the visualization pane.
Anonymous wrote:
So at this point of time redoing the whole aggregations at the backend is not feasible since we are nearing the go live.Is going live with what you have got feasible? If not, then you may need to rethink. Direct query mode assumes you have a well structured star schema data model in a SQL DB with approrpiate indexes etc. In this mode, Power BI sends the queries to the SQL DB instead of managing itself, but it needs to be configured in a way that will work. From what I understand in your description, if you want a different outcome you will need to take a different approach. JMO