cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gatapia
Frequent Visitor

Large dataset options for reporting server

I'm looking at building a large report (>100M rows) and I can't seem to find a good workign strategy on report server.  On premium this is easy using deployment pipelines, or incremental refresh, etc.  But what options are there for report server? I.e. how do I have a small working dataset locally (desktop) and the full dataset on the report server?

 

 

4 REPLIES 4
gatapia
Frequent Visitor

I managed to find a slightly better workaround (still terrible though) which worked in my scenario.  I am using explicit SQL queries to get my data, i.e. not just pointing to a table, but actual select .... from ....  I do the subset for development using a where clause.  

What I needed to do in PROD was to ignore the where clause filtering the data.  So what I did was created a service account to refresh the data on report server, say account was domain\svc_data_refresh.  So then I just changed my query to:

 

select ... from table
where ...
and ((select CURRENT_USER) = 'domain\svc_data_refresh' 
  OR /* filter clause */ date_created > '2021-01-01')


This ignored the filter clause if the user is the service account.  This way when I refresh data in PBI desktop (using my account) it filters data.  The server using the service account refreshes the full dataset.

gatapia
Frequent Visitor

Wow, what a terrible workaround.   But thanks for the confirmation.


@gatapia wrote:

Wow, what a terrible workaround.   But thanks for the confirmation.


It depends on what your use case is. In our scenario some of our models have 20+ reports linked to them using a live connection and we can then update a measures in one place and any fixes/improvements flow through to all those reports. And adding a page or changing a property on visual on a report does not require re-processing the entire dataset - it can be deployed instantly.

d_gosbell
Super User
Super User

There is not really a good solution for this on Report Server alone. What we do with models of this size is to build them as SSAS tabular models. Then we can use deployment tools and partitions for incremental processing and we just create pbix reports in live connect mode to SSAS. If you have SSAS on a separate server (which is a good idea to spread the load) you just need to ensure you have Kerberos setup.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.