Best way to minimize count of queries

[Anonymous]

Hi,

I want to build some nice Power Bi Reports which i can upload on my Report Server. I am using for this the Power BI Desktop App for the Reports Server (black one). So far so good. 

Now I noticed, that I have to query (odbc sql) for all the different reports the same databases which is kind of redundant behaviour. The second point is, that some queries go directly to production databases and I dont want to 'attack' them with like 50 sheduled big queries at night, because I need a lot of data from them. 

Is there a way to query just ones and build on top of this all the beautiful reports?

If not, what is the best way to walk around that? 

Cheers 🙂

[d_gosbell · Accepted solution]

In Power BI Report server you cannot build reports on top of the dataset in another report like you can in the cloud version, but you could build your model in Analysis Services and then create mulitple reports off that.

---

@Anonymous wrote:

Is there a way to query just ones and build on top of this all the beautiful reports?

If you build a Tabular model and host it in SSAS you can do this. If you set this model up in import mode you can then refresh this once at night and then all your reports can run off this model without hitting your production system mulitple times.

[Anonymous]

Thank you. Where do I find the Analysis Service? How do I host a tabular model there? 

And how can a Power BI Report access that model? Is that tabular model in the end a normal dataset? 

[d_gosbell]

---

@Anonymous wrote:

Thank you. Where do I find the Analysis Service? How do I host a tabular model there? 

---

Analysis Services is a component of SQL Server. If you don't already have an instance installed you would re-run the SQL Server installer and you can add an Analysis Services instance or you can install Analysis on it's on machine (providing it is also licensed to run SQL Server). Note that SSAS will host and models you deploy to it in memory so you need to make sure any machine you install it on had the appropriate resources.

You would then create your tabular models in SSDT and deploy them to the SSAS Instance.

But if you have never used Analysis Services before you should do some reading up on it in the Microsoft docs or look at engaging an expert to help advise you in getting it setup properly.

---

@Anonymous wrote:

And how can a Power BI Report access that model? Is that tabular model in the end a normal dataset? 

---

You just choose the Analysis Services option from the Get Data button, choose the Live Connection option and it will work pretty much like a normal Power BI dataset.