How do you use R Script in PowerBI?

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.

Here is an example of Visuals created using  R Script  in Power BI. The 'demo' database used is on a SAP SQL Anywhere 17 Server.
 

http://hodentekhelp.blogspot.com/2016/09/what-is-box-plot.html

What's new in August update of PowerBI?

Believe me it is a very powerful data visualization tool. If you do not have PowerBI do not worry. You can sign up for free desktop PowerBI.  Follow the link at the end of the post.

Image from Microsoft site

If you are new to PowerBI get a head start using this 'from scratch' post here.

PowerBI got updated in August 2016 and the following categories got new stuff or improvements:

Report View

• Drill on line chart
  Continuous axis for Date axis
  General availability of inline hierarchies
  Predefined matrix styles
  Reorder fields in charts and tooltips
  Color formatting for KPI visual

Analytics

• Analytics pane
  Dynamic reference lines

Data Connectors

• Impala DirectQuery support
  Snowflake connector
  Improved Web connector – Web page previews
  General availability of SAP BW connector

Query editing improvements

• Option to Merge/Append as New Query

Desktop improvement

• Auto-recover Desktop files

Watch the video here:



Get more details in the form of a video for each of the new items or improvements here.

How do you work with projects that use Entity Framework in Visual Studio

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.

Are you getting this Code 19 error after recent Windows 10 Update?

This is the error you would get if you find some hardware problem and try to access that hardware on your Windows 10 Computer.

Windows cannot start this hardware device because its configuration information (in the registry) is incomplete or damaged. (Code 19)

This happened to me after the recent Anniversary update of Windows 10. The DVD drive disappeared. It is still unresolved.

Here are the details of this error.

I am one of the Microsoft Windows 10 Insiders and the OS gets updated as and when Microsoft updates. This latest update about 11 days ago has resulted in the loss of my DVD drive. I do not use the DVD as I am not into watching movies or listening music.

Recently I needed to MOUNT an .ISO file and suddenly I was presented with this message.

V_DVD_000.jpg

Not believing the message I must have tried many times. Finally I decided to see the Device Manager and, lo and behold, there was indeed a problem.

V_DVD_00.jpg

The Virtual CD-ROM properties looked like this and the problem was the corruption.

V_DVD_01.jpg

Of course the Volume information was not available.

V_DVD_02.jpg

I immediately thought I would recover to an earlier update and found this:

V_DVD_04.jpg

But the option is greyed out (I was a day late for this fix).

V_DVD_03.jpg

The only option is to reset the OS which will remove all the programs I have installed since I started using Windows 10 (files will not be removed). I hate doing this as I have invested hours and hours of painful installations.

http://answers.microsoft.com/en-us/windows/forum/windows_10-hardware/after-the-dvd-drive-disappeared-the-only-option-is/1b227f49-aac2-4ec9-908a-116cec5ae168

Looks like this problem  is not new to Windows 10, I have seen this in the Technet forum in 2007.

If I fix this problem it will be

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 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 2012; 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 Userr 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.

What is RavPower Filehub?

RavPower Filehub has a number of functionalities in one small device. What I have is a model called 'Seabird', a middle range device in the Filehubs. They have one called 'Combi' a low end device and 'Pon' a higher end device.

I am basically interested in file transfers from my phones which have reached limits for storage and I would like to have files on my own device rather than in the cloud. I think this device would do it.

I have just started using it and perhaps in a couple of days I should be able to verify its full potential. This is just the basic set up and it is fairly easy to use as you can read from the post.

You need to download software (from www.RavPower.com) for the device and they have software to go with the iOS, Windows, Android and Mac Platforms. Since I needed file transfer capability from both Lumia 950 and iPhone 6S, I downloaded the required files shown in this image.

FileHub Plus_V1.0.8.0.exe   Windows
FileHub Plus V2.0.024.ipa   iOS


Basic set up for file transfer:

• Install the Windows version of FileHub Plus.

• You connect your storage device to the Filehub with a USB Cable. The storage device in this case is the My Passport ULTRA from Western Dynamics.

• Turn on the Filehub device and hold the power for the Wi-Fi indicator at the ceenter of the device above the power button. In the beginning it will flicker and then becomes steady.

• Now turn on your PC which is connected to a Wi-Fi network. Refresh the Wi-Fi Connections.

RP-WD03_01.jpg

Run the downloaded executable (FileHub Plus_V1.0.8.0.exe) and the login window is displayed and hit the center REFRESH button. The device is discovered

 

RP-WD03_02.JPG

Highlight the device in the above and click Log In button to display the FileHub Plus login screen. Just click Login as the password is empty (you can change it later if you wish to).

RP-WD03_03.JPG

The password is blank and remove if any characters are in this field. If there are no errors then you should see this FileHub Plus dashboard.

RP-WD03_04.JPG

Click on Settings and review the Settings menu.

RP-WD03_05.JPG

The next image shows that I can connect to two of my windows devices on the Wi-Fi (from Network DNS Clients).

RP-WD03_06.JPG
The basic set up is very easy and with the dashboard which is nicely arranged you can test out the other file operations.

Problems encountered in file transfer.

The app cannot find the local files.

What is R Interactive and how do you load it?

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

What is an easy way to move a database from one server to another?

Here is a easy way and you do not need to code. Also the Data-tier files can be emailed from one place to another. Step 1 and Step 2 describes the complete procedure for moving a database from SQL Server 2012 to SQL Server 2016. The procedure is quite easy as all you have to do is to follow the wizards shown in the two images in this post.

Step 1: At first you need to export a .bacpac file of the database you want to move from your server and save it locally or on Microsoft Azure SQL database.

Details are here:

http://hodentekhelp.blogspot.com/2016/08/can-you-export-data-tier-task-in-sql.html

ExportDT_01

Step 2:  You need to import the data-tier application, namely the .bacpac file to your Databases node of the server you want the database moved to. 

Details are here:

http://hodentekmsss.blogspot.com/2016/08/importing-data-tier-application-to-sql.html

ImportDT_01

Can you modify a UserDSN?

Yes, You can.

In my previous post I described the procedure to create a UserDSN. While creating the DSN, I had opted for the 'master' as the default database.

Herein, I show how to change the database that DSN connects to from its present option('master') to another user database, 'AdventureWorks 2014' on the same server instance.

We start with the ODBC DataSource Administrator by brining it up from start as shown.

Mod_Odbc_00

Click HSQL, the DSN we want to modify and click Configure.

The Microsoft SQL Server DSN Configuration window is displayed.

Mod_Odbc_01

Click Next.

Accept the default, 'With Integrated Windows authentication' and click Next.

Mod_Odbc_02

The next windows where you need to change the database appears.

Mod_Odbc_03

Place ccheck mark for 'Change the default database to:'. Click on the handle for this field and change from master to AdventureWorks2014 as shown.

Mod_Odbc_04
After making this change, click Next. In the window that is dispalyed click Finish.

The summary screen of the choices are displayed as shown.

Mod_Odbc_05

You can test the connecctivity using the Test Data Source....button. Click OK.

Now your DSN is connecting to 'AdventureWorks2014'

Can you a export data-tier task in SQL Server 2016 Management Studio?

Yes, mostly. There could be instances when you may not be able to. It appears that your database must be compatible with Microsoft Azure SQL Database v12. There could be other instances as well. Please refer to the documentation.

Herein are screen shots of how you export a data-tier application from SQL Server 2012. You can of course connect to SQL Server 2012 from SQL Server 2016 Management Studio.

The instance of SQL Server 2012 has a database called pubs, one of the oldest of Microsoft's sample databases. This will be used in the demonstration. You can invoke a wizard to do this task from inside the SQL Server 2016 Managment Studio as shown.

This pops-up the wizard as shown.

The steps are clearly indicated. Click Next.

You will be creating a .bacpac file. Provide a name and it usually saves the file to the default location. After providing a name by browsing to the default location, click Next.

All the data also goes with it and you can deselect some if you want to. Click Next.

Here is a summary of actions that will be taken. Click Finish.

There is a progress stage when the action is processed as shown.

After processing, if it succeeds you should see the following screen.

The task succeeds and a .bacpac file is created.

Follow this post to review a case when the task files.

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

You may want to read this as well:
http://hodentekhelp.blogspot.com/2016/08/what-is-easy-way-to-move-database-from.html