Understanding what it can do

[moosetracks]

I havent used power bi yet, and I want to make sure it can do what I need before I invest a lot of energy into learning power bi.  

I want power bi to display information about the status of machinery design project.  We have a budget in exel for each piece of machinery.  Right now we have a sheet for each piece of machinery breaking down each item and its projected cost.  Some are repeats.  We have a purchase order database in acess that has tables that will contain the purchases made for each piece of machinery.  There as several tables involved (item table, PO table, po breakdown table and catagory table) and it would need a query to pull the information needed.    

Question 1.   

Will we be able to tie these two data sourses together showing total budgeted spending vs actual spending for each of the 50 different machines we are building?  Can power bi querry live data then sort out what items have been purchased.  

Question 2.

If we revise the budget spreadsheet can power BI show historical data about the budget showing when it case changed, or would we have to create another data sourse that has the budget data and dates?

[waltheed]

Hi moosetracks,

I do think Power BI can help you with this. You can first try Power Pivot, inside Excel, which is where you create the data model. Later on you can move it to Power BI Desktop, the Power BI Service (online) or to an SSAS server.

Q1: Yes, you can link those tables together in a data model. Then you can use the numbers from the different sources combined in an excel pivot table for instance, connected to the power pivot data model. If you use an Access  database as data source, it will not be a live connection. You'll have to refresh your model. Press a button basically.

Q2: Power BI (or Power Pivot data model) cannot keep historical data by itself. When you refresh the model it just gets the new data from the source. If you do want to maintain history you will have to take care of that in the source system, which is the preferred way.

The greatest features of the Power BI (and Power Pivot)  data model are:

- speed

- virtually no record limit

- creating relationships between tables

- powerful aggregation and time intelligence functions

I think you will like it 🙂

[moosetracks]

Thanks for the info waltheed.  We have power bi as part of our companies office 365.

Do I need to use the Power pivot to the get the data ready for power bi?

Do you have a suggestion for how to add the historical portion of this into power bi?  Since the budget is in excel and we might make a change to many different areas how could we preserve the history?  

Thanks again for your insights.

[waltheed]

I would advise you to start building your model in Power Pivot. Later on you can always import the model into Power BI Desktop and start making nice visualisations and publish stuff to the cloud. But first get the data right.

You can also start in Power BI Desktop rightaway, that has the same functionality for building the data model. There is no need to do it in Power Pivot first. But once you are in Power Bi desktop there is no way back to Power Pivot or an SSAS server.

The data model cannot keep the history. when it refreshes it just gets the new data from the source. If you anhance the source tables with columns to record   history, you'll be fine. Another (not preferred) option is to create a new table in the source after every budget round, and givce it some meaningful name. Then you could use Power Query (or Get Data) to merge old versions and the new version together and load it into the data model. There are many roads that lead to Rome..

Biggest advice: keep it simple 🙂