Query folding and dynamic parameters in SQL Queries in Power Query

Before you start reading this article, I recommend you to go through very good post on Ken Puls’s blog – Pass Parameters to SQL Queries.

If it is fine for you to use native queries to database then most probably scenario described below is not so interesting for you.

Here I’ll talk about workaround allowing to use query folding and pass parameters in Power Query without building of native SQL query.

Level: intermediate

Task definition

Generate N workbooks with data models populated with limited data scope.

Then each workbook will be shared with certain group of people, that allowed to see only relevant set of data (e.g. department OPEX of particular subsidiary).

This is dictated by fact, that if you share Excel workbook with someone – user immediately has access to all info inside data model. So, I cannot create one common data model and hide slicer somewhere with activated department. I must prepare separated workbooks.

Conditions

Data: Table with millions of records in SQL database – main data source for report (query folding required for obvious reasons)

Tools: Excel + Power Query + PowerPivot

Support data: table in Excel, defining datasets – data scopes, let’s call it Control Table

Continue reading

Bulk extract data from SAP Business ByDesign

Post describes how Power Query (Get & Transform) can be used as ETL tool for SAP Business ByDesign (SAP ByD).

Purely Self-Service BI solution built on Excel for SAP Business ByDesign (SAP ByD).

Level: Advanced

How to extract master and operational data from SAP ByD using Power Query

Common information can be found in my previous posts, which I’d recommend to go through

Several ways of data extraction from SAP Business ByDesign

Query SAP Business ByDesign Web Services via Power Query

From all possible methods of data extraction, I chose Excel + Power Query as the most simple one for Modern Excel data analysts (non-IT pro).

What do we need to start this journey?

What can help?

Massive data extraction from SAP ByDesign

Let’s start from the most interesting – extract of large data volume from SAP ByD.

Continue reading

Power Query: Xlsb vs Xlsx

Recently I helped my colleague with a report. The task was to consolidate data from multiple Excel files, each contains 100k-500k rows. Hence, size of each Excel file is relatively big.

I proposed to use xlsb to store those files. One file per month, report needs R24m, so 50 MB is less than 150 MB.

In the end I finished with deep analysis what is better as Excel-files database for Power Query – XLSB or XLSX.

Resume

For PQ solutions use XLSX instead of XLSB

PQ from XLSX twice faster than from XLSB

Engine handling XLSB from time to time return error – cannot read file

“UseHeaders” parameter of Excel.Workbook function works differently for xlsx and xlsb

If sheet and table names are equal in Excel workbook – Navigator generates unique name of object.

If you interested how I revealed all this – continue reading.

Continue reading

Restore Scheduled Tasks

Recently, one of our workstations used for automated reports refresh “died”. OS stopped to load after reboot.

Fortunately, all necessary stuff were copied to backup.

We used “Self-Service BI for poor” approach on that machine. Excel + Vbscripts, scheduled in Windows Task Scheduler.

It’s typical task was to handle following simplified schema

  • Run Excel
  • Open workbook
  • RefreshAll (Power Query connections + Data Model)
  • Save workbook
  • Kill Excel

Repeat for all reports scheduled in Task Manager.

Continue reading

SAP ByDesign + Power BI = cloud friends


In this post you will find an idea how to pull data from SAP ByDesign directly to Power BI. This is a totally self-service solution, which can be built if you have literally nothing. Only you, Excel, free license of Power BI and SAP ByDesign tenant(s).

Samples based on data from test tenants that were opened for participants of openSAP course “Reporting with SAP Business ByDesign“.

Couple of words about SAP ByD for those who are not aware what is this. Cloud ERP system, has own real-time analytics engine, quite powerful, but poor. Poor calculation capabilities, poor visualization capabilities. Handles only basic things, obvious calculations.

Moreover, if you have several SAP ByDesign systems in responsibility you most probably have to consolidate data somewhere. Build sub-regional, regional reports etc.

Continue reading

Several ways of data extraction from SAP Business ByDesign

There are several reasons to extract data from corporate ERP system.

First one and the most important – reporting. Nicely visualized data, usually pre-calculated using specific logic before. We all want to see beautiful reports.

Second, I think, is a data transfer to: BI system(s), DWH etc. Maybe for some companies this is a first priority instead of reporting.

Third, rare, but happens in my practice – data migration due to system “end-of-life”. Yes, this happens. What to say? In a moment I’m involved in such project, second in my practice.

I would like to share my experience. Hope it will simplify life of many other reporting specialists working with SAP Business ByDesign.
First of all, basic ways of getting data:
Continue reading

Rename columns of nested tables in Power Query

Typical scenario

You have several data sources – Table1, Table2 and Table3

You want to combine data


Unfortunately, column names are different in those data sources, but structure is similar.

One of possible solutions is to create three separate queries, apply necessary transformations there and combine them after all.

And this would be fine solution. Moreover, it is only possible solution when function of data extraction is different for each data source.

Scalability: when we get another source, e.g. Table4, we will create fourth query and add it in Table.Combine() in addition to previous three.

But! We can slightly simplify scalability process when same extraction function used for each data source.

Continue reading

Xlsb vs Xlsx PowerPivot model

I bet over the Internet can be found number of posts with comparison of workbook size with different formats. However, nothing is better than own data🙂.

Assume that we have an Excel file with quite big amount of data

~250k rows


As we already know XLSB format makes file size much smaller than XLSX


But what if we load data into Data Model of empty workbook – will it be smaller than xlsb?

To check this I created new Excel workbook, went to PowerPivot tab and opened Data Model window


I need import from Excel

It is stored under “From Other Source” – scroll down to the end


Browse for my source file (I took xlsx as a source)


On the next step I can make “Preview and Filter” to exclude unnecessary data.

You may see checkboxes and usual dropdown filter buttons on columns – like in Excel I can select what I need in filter menu


For this “size challenge” I should take everything. So simply press “Finish” and wait while PowerPivot imports data.

After few seconds we may see result. All rows were imported – no errors.


Result in PowerPivot window


Save this file as xlsx and xlsb


Champion still is XLSB with simple table. PP version size is 11% greater.

And we may see minimal difference between PP versions in different formats. xlsx and xlsb have almost the same size.

And what interesting – xlsb is slightly bigger than xlsx in this case.

Let’s make one more test. Delete text column from initial file. It contains comments for transactions.


This column contains huge number of unique values, in addition it is text – the most heaviest data type.

I repeated same actions with file without text column and result is opposite


PP version has 11% less size than initial “xlsb without text column” and 4.5 times less than initial Without text column.xlsx.

Thoughts that come in my mind after all

Choice where to store data, in Excel table or in PowerPivot, depends on data. There are pros and cons. In the end, PowerPivot don’t lose. Usually data models contain more numbers and less text.

Work with 250k rows in Excel so slow that close to impossible, several SUMIFs will raise a “lack of memory error”. Instead of SUMIFS PivotTables can be used and then GETPIVOTDATA function… All of this currently “old style” modelling that helped us a lot before PowerPivot came – should stay in the past.

What if we want analyze R12M and take 12 files? We will have >1M rows, that is not supported by Excel sheets, BUT PowerPivot supports and still has quick performance of calculation.

Happy modelling in 2016!

Query SAP Business ByDesign Web Services via Power Query

In this post I would like to share my experience about work with web services using Power Query.

Unfortunately, some very important information is not available in data sources, e.g. Accounts Relationships and Pricing Conditions (price lists, customer specific discount lists etc.). Hope someday we will get this in reports.

However, currently, we can get this info only from

  • UI manually, almost impossible to do if you have X tenants and XXX customers with specific price lists
  • Web services – Jedi path, require knowledge and skills, or trustable tools, that can be checked

To our luck, with every new release we get more and more web services.

Basic idea of web service usage

  1. Make POST http request with XML
  2. Parse XML response

Continue reading

Load Power Query function from GitHub

Why scenario of “Loading Power Query function from GitHub” is interesting?

Assume that you work in big company, or small, doesn’t matter.

You and your colleagues use shared library of Power Query functions, published on shared drive.

You also have separated workstation or server, that has no access to the internet, only can access shared folders in corporate network. This workstation updating workbooks with Power Query all day and night long.

Typical model
101615_0737_LoadPowerQu1.png


From time to time, you also have to collect data from web using PQ functions. E.g. if you are using cloud ERP, like SAP Business ByDesign.

When you are in the office, everything is OK, your laptop is in corporate network, you simply take functions from shared folder.

Of course, company’s network resources are not accessible from the outside – from home, from partner’s office or other place.

You probably can use VPN, it solves problem, however, partially. Because VPN reduces speed to some sites.

What would be good in such situation

  • library on the web that can be used by you and colleagues from everywhere
  • library on corporate network for isolated server

If you have such two libraries then following scenario becomes possible :

When you or your colleague in the office – you simply take function from corporate network

When you outside of company network, you load same function from web library

This simple function, Load.m published to GitHub, will help you

https://github.com/IvanBond/pquery/blob/master/Load.m

(fnName as text, optional BasePath as text) as function =>
let
//If you wish to hardcode the path to load the queries from, you can edit the following line:
DefaultPath = “C:\PQuery\”,
GitHubPath = “https://raw.githubusercontent.com/IvanBond/pquery/master/“,

BasePath = if (BasePath <> null) then BasePath else DefaultPath,
Path = BasePath & (if Text.End(BasePath, 1) <> “\” then “\” else “”),
File = Path & fnName & “.m”,

Function = try Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(File))), #shared)
otherwise Expression.Evaluate( Text.FromBinary(Binary.Buffer(Web.Contents(GitHubPath & fnName & “.m”))), #shared)
in
Function

If you provide BasePath as parameter – function will take code from there.

If BasePath is empty then Load.m will use hardcoded DefaultPath.

If Load.m failed to load function from BasePath and DefaultPath – then it goes to hardcoded GitHubPath.

GitHub library opens wide opportunities, because it is shared, open-source, accessible from any place.