Hi Deidra,

Its' great to hear you're implementing Power BI. 

I'm a bit confused as to whether you're wanting to use the Power BI data in Excel or in Power BI, as you say "I import the data from a nightly refresh and the file is a power bi file."

To clarify, the only way the data gets into Power BI is via the Power Query tool inside of Power BI, which works the same as Power Query in Excel. You can either connect to the database tables directly and bring in the ones you want, filtering out any rows and columns you don't need before loading. Or you can connect to a SQL query or view which brings in a selection of tables and data already filtered. The latter can be less efficient as it prevents query folding. 

When you connect to a SQL database it can be overwhelming because there are often a lot of tables and connections. You might need to spend some time with the database administrator to understand where to find the data you need. Once you've done this and written the queries, it should be more straightforward.

I hope that clarifies some things and points you in the right direction.

Mynda

Hi Mynda, 

Thanks for the reply.  This is where I am a little confused about the Power Query tool.  The process I use for importing the data into Power BI is as follows: 1.  I log on to our Power BI workspace and then access the dataset file which is the file that is connected to our SQL production database. The data is refreshed nightly. I download the data everyday or every other day.  2.  I then download the Power BI file from the workspace and name the file with the date of the download.  3. I then upload the Power BI file into my Power BI Desktop application.    Once the data is in Power BI Desktop I then start looking at doing reports and visualizations. Again, this is the new way.  Our old way, which we are still doing, is to get a data dump from the application. The data dump is provided as raw data in an excel format.  We would then use excel to generate pivot tables and other needed reports. 

I like the idea of talking our DBA.  I think I am just stuck on what are my next steps to get the data model we need so that we can start creating the visualizations. I am still on the Power BI course but I start the Power Query class next so I am hoping that would help. Do you do 30 minute coaching session? If so, what are you fees? I think I may need that to help give me direction.  If not, I will continue with courses which are excellent. 

Thank you so much Mynda! 

Not giving up!

Hi Deidra,

Thanks for clarifying. It sounds like you are using the Power BI service to get data from a Workspace that your IT people have published the dataset to? If so, this is fine if you don't need to write any DAX measures and can simply dive into building your reports using the Power BI Service authoring tools as I show in the Quick Start session (1) of the course. This also means you won't be able to filter out data you don't want to include in your data model, instead you'll have to do the filtering as part of the visuals design process.

This may make navigating the data model for building your reports is more complex as you may have to navigate the whole database, rather than just the tables you're interested in.

However, if you want to write DAX measures or use the Quick Measures feature, you need to build your reports in Power BI Desktop. You can still connect to the Workspace data via the OneLake Data Hub > Power BI Datasets (see screenshot).

Again, the whole database will be available in the file and is treated as a Direct Query (see lesson 2.04 of the Power BI course). However, you will be able to write DAX measures on the data.

The third option is to connect to the SQL database or SQL View via the SQL connector in Power BI Desktop. Get Data > More > Database > SQL Server Database and follow the steps shown in the Power Query course lesson 2.06. With this method you can choose the tables you bring in and filter out rows and columns before loading the data to the data model. 

Your IT people may not want you to do the last option because they have less control over the quality of the data. e.g. you could accidently exclude something that is essential to the model. You'd be best to ask them first. Either way, you'll need to spend some time with them to understand the database so you can find what you need.

I would speak to your Database Administrator to ask if you're allowed to connect to the database directly, rather than using the dataset provided in the Workspace, as this may be a non-starter for you. In which case you have two options:

1. get your head around the database and skip the Power Query part because you can't modify the data you bring into your model. DirectQuery is likely to be optimised and run faster than any other method of getting the data.

2. ask IT to continue providing the data dumps, but use Power Query in Power BI to bring the data into your Power BI model and visualise it from there. This may be slower to update than the DirectQuery method.

I hope that helps! Shout if you have more questions.

Mynda

Hi Mynda, 

You are correct. I am using the Power BI service to get data from our PSO workspace where our  IT Department has published the dataset. When I get to work tomorrow I will send you a screenshot. The part where I am still confused is  when you discuss DirectQuery, Power Query, etc.  For instance, I download the data from our workspace and then I bring it into the Power BI desktop.  I am having to navigate the entire database and in some cases more than one database. I do need to filter out some tables, etc but I am doing the filtering when creating the visualizations. 

Also, I don't think my IT Department will allow a direct connection to the database because our connection is to the production environment.  I am not sure but that is my assumption is that IT does not want us to connect to the production environment. 

My next question is since I have to navigate the entire database and I am not sure what tables I need to get the reports and dashboards that I need how do I move forward.  I did talk to a co worker and he mentioned that I should just use the excel spreadsheets that we have been using for years to build the reports in Power BI. The only problems  is that is easier said then done. The dataset in PowerBI has a vast amount of tables and databases and the data I need is in multiple tables and in multiple tables. I know this can be done to do we we need its just I am so new to Power BI. I will still continue to work on the videos. I start the Power Query class over the weekend. 

In closing, I think I need to take your suggestion number 1.  I just don't understand when you say, " DirectQuery is likely to be optimised and run faster than any other method of getting the data."  What does that mean?  

Thank you so much for all your help and  for your replies.  I am really enjoying the training and I am slowing learning day by day. Again, thank you. 

Hi Deidra,

I cover Direct Query in lesson 2.04 of the Power BI course. It's just a way of connecting to a dataset other than via Power Query. 

DirectQuery:

- No data is imported or copied into Power BI when you connect to your data source using DirectQuery. The selected tables and columns simply appear in your Power BI Fields list and from there you build your visualisations.

- And this means you can build visualizations over very large datasets, where it otherwise would be unfeasible to first import all of the data

- DirectQuery reports always use current data. By contrast, underlying data changes can require a refresh of data, and for some reports, the need to display current data can require large data transfers, making re-importing data unfeasible.

I agree it's unlikely your IT people will give you a direct connection to the database. In which case, I'd ask them to help you set up your Power BI Desktop file. Explain that you need to be able to replicate the Excel files they currently provide, but the database is too complex for you to know what tables and fields you need. You can set tables and fields you don't need to use in your report to 'hidden', which will make building your dashboards much easier as you'll only see the tables and fields you need.

Mynda

Hi Deidra,

This was announced yesterday, which might be useful for you: Announcing new ways to create connected tables in Excel connected to Power BI | Microsoft Power BI Blog | Microsoft Power BI

Mynda

Thank you so much Mynda!  I will check out the link you provided. As far as my work project, I am currently still getting my head around the data and I have been looking at the direct Query suggestion you mentioned to me on 8/17/2023. . 

Let me know if you do coaching sessions and the cost. I am still learning but it has been a challenge trying to learn what I need quickly so that I can meet my work deadline.  I will say your videos are excellent and very easy to follow.  I watch the video twice and then  do the practice on my own.  I am loving the training!!  

Again, thank you and I will be  sure  to  check out the links. 

I don't do coaching as such, but you can ask me questions when you get stuck. It's a toss-up for you between getting a consultant in to build what you need and teach you as they go, which will be very expensive, vs you learning the skills yourself and then being self-sufficient.

I would lean on those in your organization to give you guidance for setting up Power BI as I'm sure there are others there who have done it before, and will be able to give you support for free.

Mynda

Thanks Mynda and you are correct. I want to learn it and be self sufficient. Currently, I am the SME in our unit.  Also,   I was able to get some guidance from someone in our Power BI Community of practice. They helped me to do a join which pulled in a column I needed from another table.  Your training is really helping me. 

Thank you and I will post questions to you as I move forward.  Again, thank you! 

Great to hear