Authoring a report with Power BI using SQL Server 2012 data

This post provides a tutorial approach to authoring a report with Microsoft PowerBI using data on a  SQL Server 2012.

PowerBI is Microsoft's preview software that you can download for free if you are in USA. Probably it will be available to users in other countries as well. It is the latest iteration to provide a toll to the
non-programmer type who desires to create Ad-hoc reports easily without knowing too much of the innards of the data source but knowing all about their business.

Read here for more information:
http://hodentek.blogspot.com/2015/02/power-bi-unchained.html

Before you start authoring the report make sure your SQL Server is running and that you have downloaded and installed the PowerBI software from this location: http://www.powerbi.com/
The installation is very simple and takes only 4 interactive screens.

 

After the download PowerBI gets launched.


PowerBI08.png

On the left you have a navigation pane. You can click on the link and get to the task you want to accomplish. In the right hand pane you have a video you can watch to get started and a few other videos. You also have access to the forums; the PowerBi blog and several tutorials. You may
also opt out to not show this page in subsequent launches,

Click on Get Data. The Get Data page gets displayed as shown.

PowerBI09.png

Notice that you can also access data on your Azure as well as quite a large selection of data sources. The picture shows only half the resources. Scroll down and review the rest.

In this post, you will be connecting to an instance of SQL Server 2012 that contains a AdventureWorks2012 database.

Click SQL Server Database in the above image. You will be displayed a page with ttle Microsoft SQL Database and you will be importing data from a Microsoft SQL Server Database. As I mentioned earlier I will be getting data from an instance of SQL Server called RegencyPark. I will have to give the complete reference to this instance. Since my laptop is named Hodentek8, the complete refeerence is Hodentek8\RegencyPark and the name of the database I will be using.

PowerBI10.png

Click OK. You get the 'Access a Microsoft SQL Database' page displayed.

PowerBI11.png

Herein  you need to provide authentication information. Windows authentication was used during installation and so just click Connect at the bottom of the screen.

You will be displayed the Navigator pane showing all the tables in your
database as shown.

PowerBI12.png

Clicking on any of the tables you can see the details for that table in the Preview is Blank area as shown. If you remember a table name you can also search.

PowerBI13.png

 

You can select multiple tables to create a report. Here to keep it simple I have chosen the Sales.vSalesperson table and you can see a blank report shown as a rectangle bordered green.


PowerBI14.png
The designer has the typical Office look with Menu and toolbar menu. You can also see various  charts design templates for data visualization.
Click Load. You may also use the GetData and Recent Resources links to get
the data.

Hidden behind the image on the top of the above screen you will also find Fields pane at extreme right. It lists out all the columns in the Sales.SalesPerson table. Use the scroll-bar to see more of the fields.

In the fields pane I choose Last Name, SalesLastYear, SalesQuota and
Sales YTD and voila the report is generated. I could choose to create the type chart I like by choosing in the menu bar.

PowerBI16.png

I can immediately create another report (a new one) by choosing other different fields (TeritoryName, SalesYTD and SalesLastYear) as shown here. I need not get out of the designer and the first report also exists to which you can switch into by clicking on it. This is very much like Power View introduced in SQL Server Reporting Services 2012.

PowerBI18.png

That's all. No calculating X-axis, Y-axis etc. The type of tasks you do with Excel. This is as simple as it can get. I took a whole page to describe and I bet you can do it in 10 minutes.

A summary veriosn was presented here:
http://hodentek.blogspot.com/2015/02/spin-out-cutting-edge-report-with-power.html

 

Creating a database using Power Shell and SQLServer Management Objects

Review this previous post    http://hodentekmsss.blogspot.com/2014/11/accessing-sql-server-via-smo-using.html

In the above post you will get an introduction to Power Shell and SQL Server maangement Objects. You also learn how to access the SQL Server and carry out a few tasks like listing out all the databases.

In this post you will learn how to create a new database in SQL Server using SMO and Power Shell. Using Transact-SQL or the SSMS you can easily create a database. Using PowerShell is another option to create a database using a script.

Launch Power Shell by right clicking the instnace node as shown.

PS_SMO_DB01

This ensures that you have access to the SQLServer's SMO library.

You will be launching the shell with the following line:
PS SQLSERVER:\SQL\HODENTEK8\REGENCYPARK>

Enter the following code one line at a time. After you enter the line and click enter you may not get any message. Continue with the other lines.

$server= new-object Microsoft.Sqlserver.management.smo.server 'Hodentek8\RegencyPark'
$dbname= "Feb6"
$db=new-object  Microsoft.Sqlserver.management.smo.Database ($server,$dbname)
$db.Create()

When the final command line is processed you will have created a new database Feb6 in your SQL Server Instance as shown. It will have default settings.

PS_SMO_DB02

Windows PowerShell ISE on Windows 8.1

 

While Windows PowerShell can be accessed by searching for PowerShell in Windows 8.1, the Windows PowerShell ISE is in the Windows Adminsitrative Tools.

WinPowershell02

A search for Administrative Tools brings up the following selection.

WinPowershell02

 

The following Power Shell related items can be found in this folder


WinPowershell03
There appears to be both a x32 bit version and a x64 bit version of Windows PowerShell ISE in Windows 8.1

Classes starting soon, register asap

This class is going to start as advertised and you are encourage to sign up as the seats for this hands-on course are limited. Hurry up!
Summary:

Databases, organized repositories of information, have become indispensable in today's world. In this introductory course you will learn about databases and the basics of Structured Query Language (SQL) including sorting, distinct, and aggregate functions and grouping of data. All SQL statements will initially be written from one table. Most practical, modern and relational databases will include a large number of tables. SQL queries have to access information from several tables. This course will then introduce you to querying more than one table. With this skill you will be able to query two or more tables in a database. This is a hands-on course.


New in 2015: You will also get an introduction to Power Shell.

Dates: (Section 1) Section 1 - Feb 17 - Mar 12, 2015; TTh; 5:30 pm - 7:30 pm; 10 mtgs.
Venue: Honolulu Community College;

Note: SQL Server 2012 Express will be used and a knowledge of SQL Server is not assumed. Students will be using the free version.

For details : Access http://pcatt.org/index.php/search and look up the course or write to:
Hodentek@live.com with course name in the Subject line.

Creating a scalar function in SQL Server 2012

General:
Consider the following equation:

y = x + x*x + x*x*x
where * is a symbol for multiplication

Here y is a function of x (the value of y depends on the value assigned to x) written symbolically as y=f(x)

A Scalar function in general can depend on more than one variable such as y=f(x,z,t) and return a single value.

How do you create a function in SSMS?

The following function named TestMultiply takes two parameters @param1 and @param2 and returns their product, a single value.

Create function [dbo].[TestMultiply]
( @param1 int,
  @param2 int
)
Returns INT
AS

BEGIN

RETURN @param1 * @param2
END
GO

This is implemented in SQL Server (in SQL Server Management Studio) by opening a query window and typing in the above code and executing the query as shown:


How do we evaluate the function?

First of all we should call the function in a query window by selecting it and providing the two parameters.

Here is how it is evaluated for @param1=5 and @param2=10



How can you create a function that uses values in the database tables?

Let us go to the AdventureWorks2012 database and review the Sales.SalesOrderDetail table. The following shows the structure of the table:

In this table we focus on two columns, UnitPrice and OrderQty. We plan to write a function that multiplies OrderQty with UnitPrice and return a value in the function we call TotalOrder. The function we create is as follows:

Create Function [dbo].[TotalOrder]
(
 @OrderQty int,
 @UnitPrice money
)
Returns MONEY

as

BEGIN
Return @OrderQty * @UnitPrice
END
GO

When you execute the query with the above a scalar function called TotalOrder will be created as shown:



In order to use this function you can call it as shown(shown with some results):

There is a lot more to understand. This just Basic stuff.

Learn Querying SQL Server 2012 using LinqPad - Part 4 Stored Procedures_2

In Learn Querying SQL Server 2012 using LinqPad - Part 3 we considered a stored procedure, albeit simple which did not have any output parameters.  LinqPad can also be used for evaluating stored procedures with an out put parameter. Make sure you review Parts 1, 2, and 3.

Let us modify the stored procedure in Part 3 so that the FullName of a Person is returned from the stored procedure as shown here.

Create Procedure PPFull
@fname nvarchar(10),
@lname nvarchar(20),
@fullname nvarchar(25) OUTPUT

as

Select @fullname=FirstName+','+Lastname from Person.person
Where FirstName=@fname and LastName=@lname
go

Note that @fullname varible is declared as an OUTPUT Parameter.

This can be executed in SQL Server Management Studio as shown.

 

The same can be executed in LinqPad as shown here.

Learn Querying SQL Server 2012 using LinqPad - Part 3 Stored Procedures

You can execute stored procedures with LinqPad. In order to do so you need to know the details of the stored procedure which can reviewed in SQL Server Management Studio as shown here for a stored procedure in AdventureWorks2012. Review Learn Querying SQL Server 2012 using LinqPad parts 1 and 2.

Create a simple test stored procedure as shown:

Create Procedure dbo_PP
@fname as nvarchar(10),
@lname as nvarchar(20)
as
Select * from Person.person
Where FirstName=@fname and LastName=@lname
go


This stored procedure requires two arguments, @fname and @lname for its evaluation.

Let us examine and execute this procedure first in SSMS and then in LinqPad.
In SSMS:
You find this stored procedure in the Programmability folder as shown.

 

This can be executed in SSMS as shown. Two paramters are provided

 

Once this procedure is created you can access it in LinqPad (review posts Part1 and Part2 )

 

It can be executed two ways in LinqPad; as a C# expression or SQL 

Evaluated as SQL:

 

Now evaluted as C# Expression:

Now as SQL:

This is how you get the return value: