cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thale
Helper I
Helper I

Best way to minimize count of queries

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 🙂

1 ACCEPTED SOLUTION
d_gosbell
Super User II
Super User II

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.

 


@thale 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.

View solution in original post

3 REPLIES 3
d_gosbell
Super User II
Super User II

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.

 


@thale 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.

View solution in original post

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? 


@thale 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.

 


@thale 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.

 

d_gosbell_0-1619506031847.png

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!