Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
niyati_61
Helper III
Helper III

Power BI Report Server Data and SQL Server

Hi All and @d_gosbell,

 

My company purchased SQL Server Enterprise Edition 2019, with which I got the Power BI Report Server (Jan 2022 Version). Since our client had a strict on premises requirement.

 

I develop Power BI Dashboards on the Power BI Desktop (For Report Server Jan 2022 Version). I then save it on the Power BI Report Server Web Portal.

 

I have 2 main questions;

 

1) I develop a Power BI Dashboard which displays project progress for multiple departments such as Engineering, Construction, Procurement, etc.

 

I get multiple data sources (Manual and Live) for this report. Eg: I get an Excel/ PDF/ Word File sent by Email every week, I have to load Excel manual data from SharePoint, I have already linked to some Oracle/ SQL Databases for live data, etc.

 

Currently, what I do is, for example, My KPI is Project Plan Vs Actual Data. I get data for this table on a weekly basis. I have stored this data in an Excel Table which I linked to the Power BI Dashboard. When I get next week's data, I will overwrite on the current data in the Excel table.

 

The issue with this is every week I'm overwriting the Past Week's data. So I'm not able to retain past data, and will always only have the current week's data.

 

My requirement is to store all weeks data in one place. I cannot only have the recent week's data. I need to have a filter which can show all the week's data.

 

Question, how and where am I supposed to store every week's data for different visuals? Can I store it in Excel (Where every week I add new rows in the table instead of overwriting the past data), or do I store all this data in a database such as SQL?

 

2) My 2nd Question is this. I'm aware that when I develop Dashboards and save it in the Power BI Report Server. My data gets stored in the  SQL Server (SQL Server Management Studio) - the one we installed along with PBIRS.

 

--What kind of data get saved in the SQL Server? How do I access that?

 

--Based on Question 1 - Can I use this SQL Server as a database where I store all my week's data for all my Power BI Visuals? How would that work exactly?

 

Kindly assist on the above.

 

Thank you in advance for your support!

6 REPLIES 6
d_gosbell
Super User
Super User


@niyati_61 wrote:

Question, how and where am I supposed to store every week's data for different visuals? Can I store it in Excel (Where every week I add new rows in the table instead of overwriting the past data), or do I store all this data in a database such as SQL?

You can store it where ever you like. If it's small you can just add new rows in Excel every week. But Excel is not a very effecient storage format. My preference would be to put it in a SQL database. But as Ibendlin has mentioned you should not put anything in the ReportServer database you should create your own separate one

 


@niyati_61 wrote:

--What kind of data get saved in the SQL Server? How do I access that?

All of the data needed by report server gets stored in that database, but you should never access the database directly. Instead you can use the REST API if you need to access that data Develop with the REST APIs for Power BI Report Server - Power BI | Microsoft Learn

 


@niyati_61 wrote:

--Based on Question 1 - Can I use this SQL Server as a database where I store all my week's data for all my Power BI Visuals? How would that work exactly?

If you have licensed SQL Server Enterprise with Sofware Assurance to get your PBIRS license then you are free to put any other databases you like on that server

Hello @d_gosbell  and @lbendlin ,

 

Thanks for your response!

 

A follow-up question, you mentioned - 'if you have licensed SQL Server Enterprise with Sofware Assurance to get your PBIRS license then you are free to put any other databases you like on that server'.

 

So, do you mean on my current SQL Server (With which PBIRS is linked) - in that same SQL Server itself, I can create another database, then create a table, and store data within the table (Which would resolve my storing data issue?).

 

Also, @lbendlin , can you please explain what this below means?

 

'Power BI has no memory. You must store your data in a SQL Server database, but a different one than what comes with the RS install.'

 

What do you mean Power BI has no memory? Where is it's relevant backend data stored then?

 

As @d_gosbell mentioned, in this same SQL Server (the one linked with PBIRS) - I can create another database within it right?

 

Thank you!

 


@niyati_61 wrote:

So, do you mean on my current SQL Server (With which PBIRS is linked) - in that same SQL Server itself, I can create another database, then create a table, and store data within the table (Which would resolve my storing data issue?).

 


Yes, this is correct.

Power BI is a reporting tool. It takes someone else's data and visualizes it. Power BI is not a primary data store , any data it has can be erased and overwritten with no easy mechanism to get it back.

This is correct for data stored in Power BI semantics models (which are stored as BLOB image in ReportServer database on SQL Server), these models is not for primary data. But you can store primary data on another DB on same SQL Server.

 

If PBI Report Server is licenced through SQL Server EE SA licence, you need to have least one MSSQL server (covered by same licence or another one licence), and on this MSSQL server you can have more independent databases (not only ReportServer DB), for any other purposes, including storing primary data for reporting, or for any else purpose (little different situation is if PBI RS is licenced through PBI Premium licence, where is licence some restriction).

BUT...

For simple small scenarios it is ok, but for large production scenarios it is not good practice, is better to install separate MSSQL server for primary data, separated for MSSQL with ReportServer DB, due very specific workload on this server.

lbendlin
Super User
Super User

Power BI has no memory. You must store your data in a SQL Server database, but a different one than what comes with the RS install.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.