Developing data models in large datasets in Power BI Service

[pade]

When working with large datasets (close to 1 GB) how do you best update the model?

My dataset is supposed to be shared to others as a content pack, and it's connected via a gateway to an OnPrem SQL.

From times to times I will need to update the data model, adding new tables, creating new relations, new columns, or new measures. Doing so with Power BI Desktop will each time publish a ~1 GB file to the Power BI Service.

Is there a way to use tools like SSDT to connect to the Power BI Service, and/or use Power BI Service to publish just the data model without any data to the Power BI Service?

[v-sihou-msft]

@pade

In this scenario, you can use Direct Query mode so that you don't need to import all underlying data into Power BI. Since you have a big data model, I suggest you create a SSAS tabular model or cube with this model and open live connection to SSAS in Power BI Desktop. You can update the model within SSAS. Those changes will be sychronized to Power BI Desktop. You can only use Power BI Desktop to publish your .pbix onto Power BI Service.

Regards,

[pade]

Thanks Simon,

Direct Query mode is not an option since it will reduce a lot of features and slow down a lot of my queries (I need the Data Model to be in-memory). This not only for me, but for others using my model

I'm trying to avoid using an SSAS by two reasons, 1) The cost, and 2) My knowledge (I know I can use the Azure Analysis Service for this, but still its one extra component to take care of)

I guess the Power BI Service (as the Power BI Desktop does) are using a SSAS anyway, and its up to Microsofts Business Model if they will provide the possibility to either a) Publish a pbix file with just the data model (excluding the data), or b) Make it possible to use SSAS tools to change the data model in Power BI Service.

Is there some kind of trick 🙂 that could be used in the T-SQL queries on the largest SQL-tables. Maybe using

SELECT TOP 10 * FROM large_table

But including "TOP 10" in some IF statement checking a variable only defined in Power BI Desktop and not in Power BI Service?

BR //Pär