Monday, August 29, 2016

Is there support for R in Power BI?

 Yes, if you have upgraded to the August version.

PowerBI is a powerful tool and Microsoft has updated it regularly and it can connect to very many data sources.

Launch PowerBI after you have installed it on your desktop. In the Visualizations pane you will see the icon for R.



Click on it and you get the message shown above. Click Enable. The Report area and the RScript editor area gets displayed.
..

One needs to drag fields from the Fields pane to this area.

Right now I have no fields. Let me get something to put here.

I connect to one of my old connections (which PowerBI has remembered after several updates although I have not used it very much). I use the GetData to connect to my previous connection to AdventureWorks on my SQL Server 2012. I am unable to connect.


Perhaps the Server is not ready. I wake it up from Services in the Control Panel. I retry connecting, I succeed. Voila! I have my data.


Just for demo, nothing planned yet. I just choose one table as shown in the above and click Load.

The UI gets loaded with fields from the chosen table as shown.


I use the DirectQuery option which connects Live to the table.


Now you can see the fields that are available for me to use (yellow rectangle).

Now you need to click and drag them on to the RScript plane. Actually I did not even drag and as if the program knew that I am going to drag, as soon as I clicked they entered the script area ass shown.


Well the script created a data frame and used the fields selected into the dataset as shown above.

Now I have not filtered the columns etc. but I can export the result as shown.


Add a line type dataset after the last line above and click the Right pointing arrow in the R script editor window which gets enabled.

It comes up with the next display showing that there was no Visual Created (bad choice of data to start with).


The Export produces a .CSV file that you can save. It appears that the default save folder is on the OneDrive. You can change it to a location of your choice.

In my next post I will describe connecting to a table where we can see some Visuals created after processed by the RScript editor.












Saturday, August 27, 2016

Create Apps using SQL Server Compact Edition Version 4

Microsoft SQL Server Compact is an embedded(able) database ideal for desktop, (hand-held) mobile devices and web clients. It is free to download (also free to deploy and redistribute) and comes in the form of just one code-free file. Its small foot print makes it easy to  deploy to a variety of device sizes and requires no administration. It also supports a subset of T-SQL and a rich set of data types.

The latest version in Version 4.0.8482.1 and you can download from here:
https://www.microsoft.com/en-us/download/details.aspx?id=17876

New features in SQL Server Compact 4.0 (per Microsoft site):

  • SQL Server Compact 4.0 is the default database for Microsoft WebMatrix, which is a stack of web technologies for easily building and deploying websites on the Windows platform.
  • For enhanced development and debugging capabilities, including designer support, Visual Studio can be used to develop ASP.NET web applications and websites using SQL Server Compact 4.0.
  • Enabled to work in the medium or partial trust environments in the web servers, and can be easily deployed along with the website to the third party website hosting service providers.
  • Private deployment of SQL Server Compact further simplified by having all the required managed assemblies, and the x86 and x64 DLLs, including the Visual C++ 2008 runtime libraries (x86 and x64) SP1 in a single private folder in the install location of SQL Server Compact.
  • Stronger data security with the use of the SHA2 encryption algorithms for encrypting the databases.
  • T-SQL syntax enhancement by adding support for OFFSET and FETCH that can be used to write paging queries and new APIs like SqlCeConnection.GetSchema that provides the metadata information of the database.
  • Used with ADO.NET Entity Framework, SQL Server Compact now supports the columns that have server generated keys like identity, rowguid etc. and the code-first programming model.

    These article provides sufficient information for the previous version and some starting points:
Basics of SQL Server Compact 3.5
http://hodentekmsss.blogspot.com/2010/07/sql-server-compact-35-basics-you-must.html

http://hodentek.blogspot.com/2009/11/connecting-to-sql-server-compact-from.html

http://hodentek.blogspot.com/2010/07/sql-server-compact-35-with-sql-server.html

Wednesday, August 24, 2016

Entity Framework and Visual Studio 2015 Community Update 3

Installing Visual Studio 2015 Community Update 3 will not automatically provide you with templates that you can use for working with Entity Frame work. You need get the NuGet Package Manager to install the Entity Framework related package as shown here:

NuGet_00.png

If the link is not showing search for NuGet.

Click the link to install and from File you can launch the NuGet Package Manager Console as shown.


NuGet_01.png

Click Package Manager Console to open the pane for entering commands to NuGet Package Manager as shown. Here I tried the to install the Entity Framework Package.

The package is installed for a saved project. In order to install the package you need to create a solution and save it. After which you can install the Entity Framework package from NuGet Package Manager.


After installing we can see the various appropriate references added to the Windows Forms project I created.


With these references you can work with Entity Framework in Visual Studio 2015 Community Update 3. The same procedure can be used for Visual Studio 2013 Community as well.




Friday, August 19, 2016

Hands-on Learning Event in Honolulu 1 : Introduction to Structured Query Language

The above course will be offered again for the fourth time (Oct 4 - Nov 3, 2016) as a non-credit course by the Pacific Center for Advanced Technology Training.


Course summary:

Databases, organized repositories of information, have become indispensable. Knowledge of databases is a must for professionals and in fact even more relevant since the emergence of Big Data in today's world.

In this introductory course you will learn all about relational databases and the basics of Structured Query Language (SQL) including sorting; grouping result sets; using DDL, DML, DCL, and TCL. All SQL statements will initially be written for one table. Most practical, modern and relational databases will include a large number of tables and 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 which will take you from installing SQL Server 2016; learning the ins and outs of SQL Server Management Studio and of course a full dose of SQL. You will be both coding as well using Graphical User Interface during this training.

During each meeting the students will be assessed for their knowledge, competency and proficiency in SQL.

New this session:

Microsoft SQL Azure Database: SQL Server in Microsoft Cloud
Windows PowerShell for SQL Server
SQL Server 2016



My sincere thanks and best wishes to my students in the previous meets of this course.

PowerShell is written in .NET code (C#), not true anymore

The great productivity tool, PowerShell has entered the Open Source community (both Linux and Mac) according to an article that appeared in redmondmag.com.

This is what the Inventor of PowerShell said about this move:

 "PowerShell on Linux is now designed to enable customers to use the same tools, and the same people, to manage everything from anywhere," Snover said. The initial ports are available on Ubuntu, Centos and Red Hat, he noted. "PowerShell also now runs on Red Hat Linux and Mac OS X with additional platforms planned for the future. The "alpha" builds and source code are now on GitHub
"

IT pros can use this task-based scripts to provide automaton support to Windows OS and all the other Windows Products.

PowerShell skills are highly marketable and now even more.

Read all my PowerShell posts here:
http://hodentekmsss.blogspot.com/search?q=Powershell

Wednesday, August 17, 2016

Update your SQL Server Data Tools to the latest

SQL Server Data Tools is a modern development tool which can be used to build:

SQL Server Relational DBS
Azure SQL Databases
Integration Service Packages
Analysis Services Models
Reporting Services Reports


You can, not only build you can also deploy SQL Server content with same ease as you do in Visual Studio. If you already have Visual Studio 2015 Community installing SSDT will add full set of SQL Server tools into existing Visual Studio. If there is no Visual Studio, a shell Visual Studio programe will be installed which will have only SQL Server related BI templates.

You will have a stirling exprience if you all ready have Visual Studio 2015 Community which happens to be free as well. This is highly recommended.

This post is related to the July 7, 2016 update. The new version is 14.0.60812.0.

Here are the older versions on my computer:


SSDT_01

If you already have Visual Studio 2015 Community, you will have received a notification which will show the updates available for you as shown:


SSDT_00

Just hit the Update button and you will be taken to the download URL. If you do not have Visual Studio 2015 Community and you want a copy of SSDT just follow this link.(https://msdn.microsoft.com/en-us/library/mt204009.aspx)

Details of SSDT installation are at this link including links for the .ISO Images and other details:
https://msdn.microsoft.com/en-us/mt186501

The ISO images are available in several languages as well.
Portuguese (Brazil)
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x416
Chinese (PRC)
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x804
German
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x407
English (United States)
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x409
Spanish
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x40a
French
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x40c
Italian
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x410
Japanese
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x411
Korean
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x412
Russian
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x419
Chinese (Taiwan)
https://go.microsoft.com/fwlink/?LinkID=824661&clcid=0x404

Supported Operating Systems are:

Windows 10 (x86 and x64)
Windows 8, 8.1 (x86 and x64)
Windows 7 SP1 (x86 and x64)
Windows Server 2012 (x64), R2 (x64)
Windows Server 2008 R2 SP1 (x64)

Executing a stored procedure in dynamic SQL

Stored Procedures can be executed with a command or, using sp_executeSQL and the defined procedure in the context of the database.

Here is a stored procedure in the pubs database (just the name of the database in the instance of SQL Server 2016 is named TestSQL. It has everything that pubs has except for the name).

Well, pubs database may not be available on SQL Server 2016 and you can use any other database.

I am jsut taking the example of the stored procedure as shown here:
---------------

USE [TestPubs]
GO

/****** Object:  StoredProcedure [dbo].[reptq2]    Script Date: 8/17/2016 11:37:46 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[reptq2] AS
select
    case when grouping(type) = 1 then 'ALL' else type end as type,
    case when grouping(pub_id) = 1 then 'ALL' else pub_id end as pub_id,
    avg(ytd_sales) as avg_ytd_sales
from titles
where pub_id is NOT NULL
group by pub_id, type with rollup
GO
---------------------

The parameter is the return integer. There is no IN parameter.

The following statement executes the stored procedure as a dynamic sql.

---------------------
declare @proc nvarchar(25);
set @proc='dbo.reptq1'
--exec @proc
exec sp_executeSQL @proc

--------------

Sometimes instead of the procedure being defined with the schema as in the above, it could be just the name of the procedure. However, the statesmen needs to be executed in the context of the database. There are no input parameters and if there are any they should be declared and set.