Desktop Questions

[mj2024]

Hi

I am looking at using Desktop (free version) to do some data visualization.  I have some questions:

1.  Am I able to connect to a SQL Server database?

2.  What is the limit on the size of the data being imported into Desktop?

3.  Am I able to run a SQL query in Desktop to retrieve data on a as-needed basis (eg, if I apply a filter then the query is re-run to retrieve the data that way the entire dataset is not loaded but only load what is needed to display the visualization) OR is the entire dataset loaded into Desktop and I just filter what I need?

4.  Is there any limitation on what types of graphs/reports I can create? The data visualization is just for me so there is no need to share/publish anything.

Thank you

[MFelix · Accepted solution]

Hi @mj2024 ,

Please see the answers below:

1.  Am I able to connect to a SQL Server database?

Yes you can connect to on-prem SQL database if you search for the SQL connector you can see several types of connector.

2.  What is the limit on the size of the data being imported into Desktop?

In theory the limit depends on the capacity of your machine because it's a very memory consuming process but you can do several millions of rows the question is that it can take a long time to refresh your data.

3.  Am I able to run a SQL query in Desktop to retrieve data on a as-needed basis (eg, if I apply a filter then the query is re-run to retrieve the data that way the entire dataset is not loaded but only load what is needed to display the visualization) OR is the entire dataset loaded into Desktop and I just filter what I need?

You can setup a SQL query with parameters that will allow you to filter the results so you can have a where clause and change the parameters so that you get only some of the rows

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

4.  Is there any limitation on what types of graphs/reports I can create? The data visualization is just for me so there is no need to share/publish anything.

Power BI includes some standard visuals but you can also download additional ones from the appsource, this includes Vega and Vega-Lite visuals and on the standard ones you can create Pyton and R visuals that will increase the options.

[Uzi2019 · Accepted solution]

Hi @mj2024 

1)  Am I able to connect to a SQL Server database? 

      Yes . 2 modes Import and Direct query

Import ( import the complete data set into power bi desktop. its very faster but limited to scalability, but you can perform data tranformation dax, data manipulation)

Direct Query( Real time data , scalable suggested for very large data set, very slow in performance (because when you filter the data it request goes to actual server)

You can also write native query to get the data. like select * form table etc...

2)What is the limit on the size of the data being imported into Desktop?
Model compress size should be 1 GB data model. check this blog. you will understand.

3)Am I able to run a SQL query in Desktop to retrieve data on a as-needed basis (eg, if I apply a filter then the query is re-run to retrieve the data that way the entire dataset is not loaded but only load what is needed to display the visualization) OR is the entire dataset loaded into Desktop and I just filter what I need?

You can write the sql query and run to get the result. or if you want your filter should reflect n only fetch the filtered data then go for Direct query mode.

is the entire dataset loaded into Desktop and I just filter what I need? this method I would recommed Import mode because its actually very fast.  nad you can easily slice and dice you data and do transformation and write time intellegence fucntions.

4)Is there any limitation on what types of graphs/reports I can create? The data visualization is just for me so there is no need to share/publish anything.

you have default visualization in you canvas. you can add more at free of cost or you can pay for more advanced visual from appsource.

or you can create your own visual/graph if you are coder of python or R . there are lots of apps available where you can create your own graph in power bi ( custom made by you)

The data visualization is just for me so there is no need to share/publish anything??

It depends if you want to share your report you have to publish it on power bi service. so other can see your report and data.

If you just want to learn power bi so there is no need to share the report or publish it.

I hope I answered all you question!

[mj2024]

Thank you to everyone for your replies.  I appreciate it very much.

[anmolmalviya05 · Accepted solution]

Here are answers to your questions about using Power BI Desktop (free version) for data visualization:

1. Am I able to connect to a SQL Server database?

Yes, Power BI Desktop allows you to connect to a SQL Server database. You can use the "Get Data" option and choose SQL Server to establish the connection. You’ll need the server name and database name, and if authentication is required, you’ll need to enter the appropriate credentials.

2. What is the limit on the size of the data being imported into Desktop?

In Power BI Desktop, there isn’t a strict size limit for data imports, but practical limitations exist based on your system’s memory and performance. Typically, Power BI Desktop can handle datasets up to about 1 GB or slightly more, depending on your system resources. However, performance may be affected if you work with very large datasets.

In practice, if you need to work with very large datasets (over 1 GB), you might consider using DirectQuery mode (explained below), which allows you to query data directly without importing it.

3. Am I able to run a SQL query in Desktop to retrieve data on an as-needed basis?

Yes, you can achieve this in two ways:

DirectQuery Mode: When connecting to your SQL Server, you can select DirectQuery instead of Import mode. With DirectQuery, Power BI does not import the entire dataset; instead, it only retrieves the necessary data based on the visualizations and filters you apply. This can reduce memory usage, as only the relevant data is fetched as needed.

Custom SQL Query: You can also write custom SQL queries when connecting to the SQL Server. After selecting SQL Server as the data source, you can specify your SQL query, which will run and retrieve only the data you need. This is especially useful if you need to pre-filter or aggregate data on the server side.

Note that DirectQuery can impact performance for complex queries or if the connection to the SQL Server is slow, but it can be efficient for large datasets that don’t need to be fully loaded into Power BI.

4. Is there any limitation on what types of graphs/reports I can create?

Power BI Desktop provides a wide range of visualizations, including bar charts, line charts, scatter plots, maps, tables, matrices, and custom visuals. For personal use, there’s no restriction on the types of visualizations you can use.

You can also import custom visuals from the Microsoft AppSource marketplace or create your own custom visuals using Power BI’s developer tools if you have specific needs.

However, the free version of Power BI Desktop does not include publishing or sharing capabilities. If you eventually want to share your reports with others, you would need to publish to the Power BI Service, which requires a Power BI Pro or Premium license.

[Uzi2019 · Accepted solution]

Hi @mj2024 

1)  Am I able to connect to a SQL Server database? 

      Yes . 2 modes Import and Direct query

Import ( import the complete data set into power bi desktop. its very faster but limited to scalability, but you can perform data tranformation dax, data manipulation)

Direct Query( Real time data , scalable suggested for very large data set, very slow in performance (because when you filter the data it request goes to actual server)

You can also write native query to get the data. like select * form table etc...

2)What is the limit on the size of the data being imported into Desktop?
Model compress size should be 1 GB data model. check this blog. you will understand.

3)Am I able to run a SQL query in Desktop to retrieve data on a as-needed basis (eg, if I apply a filter then the query is re-run to retrieve the data that way the entire dataset is not loaded but only load what is needed to display the visualization) OR is the entire dataset loaded into Desktop and I just filter what I need?

You can write the sql query and run to get the result. or if you want your filter should reflect n only fetch the filtered data then go for Direct query mode.

is the entire dataset loaded into Desktop and I just filter what I need? this method I would recommed Import mode because its actually very fast.  nad you can easily slice and dice you data and do transformation and write time intellegence fucntions.

4)Is there any limitation on what types of graphs/reports I can create? The data visualization is just for me so there is no need to share/publish anything.

you have default visualization in you canvas. you can add more at free of cost or you can pay for more advanced visual from appsource.

or you can create your own visual/graph if you are coder of python or R . there are lots of apps available where you can create your own graph in power bi ( custom made by you)

The data visualization is just for me so there is no need to share/publish anything??

It depends if you want to share your report you have to publish it on power bi service. so other can see your report and data.

If you just want to learn power bi so there is no need to share the report or publish it.

I hope I answered all you question!

[MFelix · Accepted solution]

Hi @mj2024 ,

Please see the answers below:

1.  Am I able to connect to a SQL Server database?

Yes you can connect to on-prem SQL database if you search for the SQL connector you can see several types of connector.

2.  What is the limit on the size of the data being imported into Desktop?

In theory the limit depends on the capacity of your machine because it's a very memory consuming process but you can do several millions of rows the question is that it can take a long time to refresh your data.

3.  Am I able to run a SQL query in Desktop to retrieve data on a as-needed basis (eg, if I apply a filter then the query is re-run to retrieve the data that way the entire dataset is not loaded but only load what is needed to display the visualization) OR is the entire dataset loaded into Desktop and I just filter what I need?

You can setup a SQL query with parameters that will allow you to filter the results so you can have a where clause and change the parameters so that you get only some of the rows

https://learn.microsoft.com/en-us/power-query/power-query-query-parameters

4.  Is there any limitation on what types of graphs/reports I can create? The data visualization is just for me so there is no need to share/publish anything.

Power BI includes some standard visuals but you can also download additional ones from the appsource, this includes Vega and Vega-Lite visuals and on the standard ones you can create Pyton and R visuals that will increase the options.