PowerBI Preview reports using data on an EXCEL file

Excel is a well known number cruncher and a super hero of Self-Service BI while PowerBI is an easy to use interface for turning out eye catching, cutting edge reports. PowerBI is still in preview.

This post describes visualization report creation using PowerBI previewon a Windows 8.1 machine which does not have Office installed.

This starts of from an MS Excel  file that was created using the Export-Import Wizard and the AdventureWorks2012 database on an instance of SQL Server 2012 running on a Windows 8.1 laptop. If you want to know how AdvTest.xls was created follow this link:
http://hodentekmsss.blogspot.com/2015/02/exporting-data-from-sql-server-2012-to.html

The screen shots shown here may not be step-by-step. Please review by previous posts on this topic:

http://hodentek.blogspot.com/2015/02/spin-out-cutting-edge-report-with-power.html

http://hodentek.blogspot.com/2015/02/powerbi-preview-reporting-from-sql.html

http://hodentek.blogspot.com/2015/02/powerbi-preview-reporting-from-sql_13.html

http://hodentekmsss.blogspot.com/2015/02/authoring-report-with-power-bi-using.html

When PowerBI is launched and Get Data for an Excel application is clicked you will get the following displayed:

Excel22

 

For some reason I am not able to explain presently, PowerBI seems to duplicate the entries here for each of the tables on the SQL Server. Perhaps this will go away in the release version.

 

When I tried to load three of the chosen tables, the program cranks to load as shown:

 

Excel24

 

However it does not load and comes up with this excuse:

 

 

 

Excel11

If you over come this objection then you can load the table/tables as shown. The designers could have imagined this scenario and provided a direct link if not a download and install of required file.

 

This was the table (vSalesPerson) loaded to create the report. The Excel file (AdvTest.xls) had all these columns in several sheets in the workbook.

 

vSalesPerson

BusinessEntityID
Title
FirstName
MiddleName
LastName
Suffix
JobTitle
PhoneNumber
EmailAddress
AddressLine1
AddressLine2
City
StateProvinceName
PostalCode
CountryRegionName
TerritoryName
TerritoryGroup
SalesQuota
SalesYTD
SalesLastYear

 

To create a report few of the columns in the table was used. Here is a Card Type report created using this table exported to Excel File "AdvTest.xls".

 

 

Excel29

The columns chosen for the visualized report are shown on the right presently highlighted. They are just numbered column names that must be associated with the list of fields shown earlier. However the visualized report shows the names correctly.

 

Something the designers could have done easily was during loading of the file they should have brought in the original column names and not the numbered columns as shown here.  Also in the visualized report the column name appears as one of the cards (3r or 4th) in the above report.

Another feature that (perhaps I missed this time) perplexed me was how to move the fields to the Vertical and Horizontal axes. The drag and drop did not work.

I could list out few more problems but considering the fact it is in preview things can only get better.

 

Namaste

p.s: One of the screen shots was replaced with the correct on
 

Do you need to connect to SQL Anywhere 16 from OpenOffice 4.0?

Sybase Central 16 is a very nice interface and I never encountered any problem from its earliest version (I jumped in at version 9, may be). However, if you want to connect to a database and create tables; run queries; create views; create forms and reports you can use OpenOffice 4.0 which is very simple to use.

Main content is temporarily removed.

Sorry for the inconvenience,

Jay

I am restoring the original post for the following reasons:
1. It  is an opportunity to get a feedback from the larger audience.
2. The described procedure is not wrong, but the 'odbc' confusion is not completely resolved. If and when this is resolved, the procedure will be useful in its entirety.

When you install SQL Anywhere 16 it comes with a 'demo' database and incidentally this has not changed from the earliest version to which I was exposed.
You can connect to the 'demo' server and create an ODBC connection as shown in the next image (note that you will be using a 32-bit driver):

Now launch OpenOffice 4.0 from its shortcut on the desktop and click Database as in the earlier post. You are already in Step 1.
In Step 2 select the demo ODBC connection and click OK.

In Step 3 provide 'dba' as username.

Click Test Connection. Provide 'sql' as the password.

 

Click OK.
You will see that you easily get connected.



In Step 4, save and proceed. For this example it was saved as SAP16.odf.

Click Finish. Now you see the Group O tables in the saved database.

 

Note added in this reinstatement:

 

This looks great. But what is the problem and why did I try to retract?

 

Well the problem with this is none of the tables can be opened to see what they contain. There is a cryptic error message but no clue. My guess is that the ODBC driver that is installed with the software produces can architectural mismatch and therefore results in an error. I have posted this to OpenOffice forum, but I think the problem is the ODBC driver (probably not having the correct one). If I find some explanation, I will edit this post.  I invite readers for suggestions.