In this blog I will demo, how you can take reports in Excel or in Power BI, when the data is in a self made database. The database is simplified just to demo how you can report the data with Excel.

The editor I am using is SQLite3.

The table “sales” includes sales data, product and volume. The sales table is a transaction table.

The product table includes basic data of the product. This is like product master data.

In order to get SQLlite connected with Excel and Power BI, you need to download the connector from http://www.ch-werner.de/sqliteodbc and there sqliteodbc_w64.exe.

In ODBC admin press add.

Take the SQLite3 ODBC driver and press finish.

Browse your database and give a name.

Press OK.

In Excel take data – get data – from other source – from ODBC.

Open up the drop down list and select sales_demo.

Press ok.

Select windows and use my currect credentials. Press connect.

Select multiple items and select both the tables. Then press transform data.

Price in prod table was changed to be integer.

Also the sales volume is the data type of integer.

The data in Power Query is same as in SQLite3, as it should be.

Close & load – close & load to.

Only create connection and add this data to the Data model.

Take Power Pivot and Datamodel.

Diagram view.

Create a minor data model.

Take pivot table that takes the data from data model.

Use the Data model as the basis for the Pivot.

Let’s create a measure to sum the sales.

Select size as rows and sum of sales as values.

The total 121 is divided 101 large products and 20 small ones.

To calculate the sales in money, we will use SUMX and RELATED functions.

The formula is here: =sumx(sales,sales[vol]*RELATED(prod[price]))

Results in Excel.

The same process in Power BI:

Get ODBC, then connect.

Sales_demo press ok.

Connect.

Tick the tables and press transform data.

Change data type for price as integer.

Also change data type for vol to integer.

Close & apply – close & apply.

Power BI created the relation automatically, unlike Excel.

Create measure for summing the sales.

Create another measure just like in Excel.

Results in Power BI.

Doublecheck the results from database directly.

Sales volume by size.

Sales in money by size.

If you have data in a self-made database, it is possible in some cases to use Excel or Power BI for reporting.  Connector is needed but in this case, the connector was available on line. The same connector work for both Excel and Power BI.

Even though SQLite is not listed in Excel or in Power BI as a source of data, but via ODBC you can access SQLite database.

Once more, Power BI visualize the data instead of writing SQL scripts in editor even though the results are same both in database and in Power BI.