Forum Discussion
Understanding what it can do
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 :-)
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.
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 :-)
