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
 

Use Sybase(SAP)'s SQL Anywhere OLE DB Provider 16 to create a linked server in SQL Server 2012

Linked servers offer the following advantages as noted in MSDN documentation:

•The ability to access data from outside of SQL Server.
•The ability to issue distributed queries, updates, commands, and transactions on heterogeneous
data sources across the enterprise.
•The ability to address diverse data sources similarly.

There are two ways to create Linked Servers in SQL Server 2012.

• Using SQL Server Management Studio
• Using the sp_addlinkedserver stored procedure

Microsoft SQL Server 2012 has two providers to connect to SQL Anywhere 16 from SAP (Sybase). They are,

MSDASQL - Microsoft OLE DB Provider for ODBC
SAOLEDB.16 - SQL Anywhere OLE DB Provider 16

When you install SQL Anywhere 16 the servers can be used for both architectures.,
Of the two providers MSDASQL cannot be used as no MSDASQL is available for x64bit and attempts to use it to configure a linked server results in failure.

However if you use SAOLEDB.16 you can successfully create a linked server.

For a detailed step-by-step explanation review the following link:
http://hodentekmsss.blogspot.com/2014/07/creating-linked-server-to-sapsybase-sql.html

Microsoft Jet or MSDASQL for Linked Servers?

In SQL Server 2008 you can create linked servers with a variety of data sources as seen in this figure. Linked Servers are created using OLE DB technology and there are two possible ways that
you can connect to MS Access, using Microsoft.Jet.OLEDB.4.0 or MSDASQL.

The connection strings for these are:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;Persist Security Info=False


[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Data Source=FromAccess

Now which is better for a linked server? You will find the answer to this in the following article brought to you by SSWUG.ORG. This is site where you subscribe, but you can register as a guest to view this article.

Microsoft Access 2003 Linked Server on SQL Server 2008 using the JET and MSDASQL Providers

MS Access Linked Server, comparing providers

In this article on SSWUG.org web site, the MSDASQL OLE DB ODBC driver is compared with Microsoft Jet 4.0 as related to creating a linked server on SQL Server 2008. Details of creating a linked server as well as accessing data using queries are also discussed.

Microsoft Access 2003 Linked Server on SQL Server 2008 using the JET and MSDASQL Providers

This is a subscription site and you can join as a guest and read the article. If you like what you have read you can become a member.