Manuel Robalinho: Power-bi

https://business.tutsplus.com/

these days of working at home, the information piece has become more critical. Any type of information, usually stored in folders on-site servers, which is necessary to share. In this case, the need was to share excel files with budget information, where they should be read by a PowerBI platform.

I didn’t want to use the VPN (Virtual Private Network), in order not to be restricted to the Firewall’s internal access and permissions, but I shouldn’t expose the information either. The information would have to be kept private. One of the ideas for remote connection to the file was to put it in Google’s cloud, Drive.

Two problems arose with this decision:

1-PowerBI have no native connection to Google Sheet.

2- The connection to Excel (xlsx) saved in the Drive has connection errors.

One apart from this discussion, has a connection with so much platform in PowerBI, that it is hard to believe that there is no connection with Google Sheet. Market issues or lack of commercial ability to know that today Google applications are fighting on an equal footing in the market.

Studying the problem I found the workaround for this connection. 3 steps for this solution:

1-First it will be better to transform your excel sheet into a Google Sheet. It’s automatic using Google conversion.

2-Share your Google Sheet by obtaining the shareable link.

3-Define your PowerBI connection link as Web, and use the URL link obtained in step 2.

Here we have a hint, with the relationship the URL. You need to change the spreadsheet URL to the following format: https://docs.google.com/spreadsheets/d/[id=spreadsheet] / export? format = xlsx & id = [spreadsheet id]

An example:

Your shared URL is: https://docs.google.com/spreadsheets/d/5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs/edit?usp=sharing.

Look that the ID is only ‘5aEqaRdzheBRpTez_Y0GBxBH2vKBIzT-jcmds2VJjbIs’

After our modification the URL will be:

https://docs.google.com/spreadsheets/d/58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw/export?format=xlsx&id=58-0c4aNM0i0npydD8VKZfinye-y6DH4WrbvCC-eLvrw

So, put this URL on the Web connection in PowerBI.

Let’s go test:

1-First my original excel file, load on Drive:

2-Transforming excel on Google Sheet

3-Obtain the link to share and put it on the Configuration data source, type Web.

Now we can watch our data on PowerBI:

I tested the modification of some customer records in the Drive Google Sheet. At PowerBI I push the Refresh button and the update was immediate.

Conclusion

The use of multiple platforms and the knowledge of their basic skills can be a strong ally in decision making, in record time.

In the case above, the need for an ingenious solution that solved a basic and urgent problem led to the complementary use of two products from competing companies. This use solved the proposed problem very well.

References:

My Medium

Reporting system to OBZ Budget

This is a development of a reporting system for the zero base budget, using PowerBI.

To extract the data, run a report from SAP tables COSP and COSS (that have the accounting costs by cost center and account).

We have a SAP SET OBZ_TIPO_DESPESA to classify the accounts the Type of Expense
We have a SAP SET GAC_P% sets have the accounts for spending packages

Excel File layout to import into PowerBI

Empresa - Company in text field
Exercício - Year in text field (YYYY)
Centro custo - Number of cost center
Denominação - Name of cost center
Data de lançamento - Date (the system makes a single date per month, on 01 / mm / yyyy)
Real - Actual month value
Plano - Planned month
Comparativo - Actual value of the month for the comparative year
Tipo Despesa - Expense type
Pacote - Spending Package
Nome 1 - Directors (Department)
Nome 2 - Management

What is OBZ - Zero Base Budget

Zero-based budgeting is one of the budget planning methodologies that is commonly known as OBZ, this tool requires managers to justify all of their budgeted expenses, rather than a more common approach of just requiring justification for incremental changes to the budget or actual results the previous year. Thus, a manager is theoretically assumed to have a zero expenditure baseline. OBZ is a plan made from scratch, with no background so it works, everything needs to be carefully budgeted and aligned.

In reality, a manager is assumed to have a minimum amount of funding for basic department operations, above which additional funding must be justified. The intention of the process is to continually focus the financing on key business objectives and to close or reduce any activity that is no longer related to those objectives.

The basic process flow under a zero-based budget is:

Identify strategic business objectives
Create and evaluate alternative methods to achieve each goal
Assess alternative funding levels, depending on the planned performance levels to establish priorities.

The concept of tiered expenses can also be used in reverse, where you outline the specific costs and capital investment that will be incurred if you add an additional service or function. Thus, management can make discrete determinations of the exact combination of incremental cost and service for its business. This process will usually result in at least a minimum service level, which establishes a cost base below which it is impossible for a company to go, along with several service gradations above the minimum.