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.

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

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.

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

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 statement 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.

R interactive using Visual Studio 2015 Community Update 3 to access SQL Server

R GUI is an interactive window from R Foundation.

When you install Visual Studio 2015 Community Update 3 or install Microsoft R Client you can bring up R Interactive within Visual Studio IDE. This is really very nice and you have the full access to intellisense that makes it very easy to use the interface. You would really appreciate if you had the experience of using the Open Source R.

Here are some screen shots of its usage in Visual Studio 2015 Community Update 3. You launch R Interactive from R Tools menu in Visual Studio 2015 Community Update 3.

This brings up the message that you have logged into your Microsoft Account.

If you are logged in you get to display the R Interactive pane as shown.
 

This looks similar to the one from Open Source, but wait. You can load the library RODBC straight away and you get the functions that you can use, thanks to intellisense.

 

Now I try to establish an ODBC connection to my SQL Server 2016, voila, again intellisense to the rescue. In Open Source you have to look up some documents to get these.

OK, I have a User DSN to my SQL Server 2016 called 'HSQL' and I try to call.

Well, I have the full details of my User DSN.

This is the way to go if you are interested in productivity.

More in my future posts on this blog and the other:
http://hodentekMSSS.blogspot.com

Read the posts related to this post here:


http://hodentekhelp.blogspot.com/2016/08/what-is-needed-to-leverage-r-from.html

http://hodentekhelp.blogspot.com/2016/07/how-are-user-dsn-system-dsn-and-file.html

http://hodentekmsss.blogspot.com/2016/08/viewing-tables-on-sql-server-2016.html

http://hodentekmsss.blogspot.com/2016/07/you-need-this-to-connect-to-sql-server.html

Viewing tables on SQL Server 2016 Developer using R GUI

In this post we will use ODBC Connectivity to connect to SQL Server 2016. We create a new ODBC UserDSN or modify an existing ODBC DSN to connect to the 'pubs' database on SQL Server 2016 (the modification in the link changed the database from 'master' to 'Adventureworks2014').

You need to user the RODBC package to connect to SQL Server if you are using the R from CRAN.

From R GUI (x64bit) click Packages and choose to install packages.

rodbc_00

You may be directed to a list of mirror sites from where you can get the package.

rodbc_00_1
Click "OK" to download the package to your computer.

I had earlier chosen California site and the package has RODBC.

robdc_01

Click on it and click OK.
The package gets downloaded to local file system.

rodbc_02

Load the library for RODBC so that you can use it in the session.

rodbc_03

Connect to the ODBC User DSN "HSQL" you created earlier. Then take a look at all the tables (this is a long list and only few pieces are shown).

rodbc_04

You can also use sqlTables() and sqlFetch() functions with a table name (such as 'employee' in this case) to get relevant items as shown.

rodbc_05

In case you are using a DSN with a username and passwordd, you can connect to it as shown here:
         odbcConnect("dsn", uid="user", pwd="***")

You can close the conneciton with:
         odbcClose(ch)

You will not get a response to the above.

It is quite easy if you have the DSN and the RODBC library.

Importing a data-tier application to SQL Server 2016

You have seen how to export a data-tier application of a database in  SQL Server 2012. In fact, we exported one of the oldest of Microsoft's Sample Databases, the TestPubs. We also saw some limitations in this process of export due to incompatible data, although we have not fathomed as yet what type of data is incompatible.

When the process of export succeeds you create a .bacpac file which can be saved either to a local drive or to Microsoft Azure SQL Database. In our previous export we exported a TestPubs.bacpacfile from TestPubs database to the local drive.

In this post, you will see how you can import it into a higher version of SQL Server, SQL Server 2016.

Start up the named instance of SQL Server 2016. You can see that the server has no user database presently except the one we created recently (AdventureWorks2014).

ImportDT_07

Make a right click on the Databases node in SQL Server 2016 as shown.

ImportDT_00

Click on Import Data-tier Application... submenu to launch the import wizard's Introduction page as shown. Read the instructions on this page.

ImportDT_01

Click Next to display the Import Settings page. Click on the ellipsis button to browse the default page where the .bacpac files are stored as shown.

ImportDT_02

The file enters the processing by the wizard as shown.

ImportDT_03

Click Next to display the Database Settings page. The deployment is targeted to the SQL Server 2016 and .mdf and .ldf files are created. You can change the database name and herein it remains unchanged.

ImportDT_04

Click Next to display the Summary page as shown - Deployment details, file and target as shown.

ImportDT_05

Click Finish. There is little bit of processing activity as shown.

ImportDT_06

The operation has succeeded as shown.

ImportDT_08

The TestPubs database is now found in SQL Server 2016 (you may need to Refresh the Databases node).

ImportDT_09
The data has also entered the database as shown in one of the queries on the TestPubs in SQL Server 2016.

ImportDT_10

Well. It is quite simple and not a line of code is needed!

Exporting a Data-tier application from SQL Server using SSMS has a problem

According to MSDN (https://msdn.microsoft.com/en-us/library/hh213241.aspx)  it is quite simple to Export a data-tier application. Here is an overview of exporting a database as a Data-tier .bacpac file which can be deployed to another server.

"Exporting a deployed data-tier application (DAC) or database creates an export file that includes both the definitions of the objects in the database and all of the data contained in the tables. The export file can then be imported to another instance of the Database Engine, or to Azure SQL Database. The export-import operations can be combined to migrate a DAC between instances, to create an archive, or to create an on-premise copy of a database deployed in SQL Database.
"
The steps are very clear as the Export data-tier application task brings up a wizard and you just have to follow the steps.
The following screen shots show the process for exporting from a SQL Server 2012 instance.

Initiating the Export Data-Tier Wizard. Click Export Data-tier Application...

Wizard shows up.

Click Next.

In the above you can save it to a local disk (Folder/File) or save to Microsoft Azure. Here the default is accepted.

The Next is greyed as there was an earlier file. However the database had changes and a new one was created with a different name.

Data in the tables will also be exported.

Click Finish. The Wizard starts processing.

The result of processing is that the operation did not succeed.

The reason is in the error shown below.

Looks like some implied incompatibility with some Microsoft Azure SQL database. It may be noted that the target file was supposed to be saved to the local disk and not Microsoft Azure.

This happens in SQL Server 2014 as well.

According to a recent MSDN forum post, the failure in SQL Server 2012 and SQL Server 2014 for this particular case of Northwind database is perhaps related its

Read these popular blog posts related to data-tier applications:
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html
http://hodentekmsss.blogspot.com/2013/12/deploying-dac-to-sql-server-2014.html