Wednesday, February 22, 2017

SQL Saturday #614 – South Island 2017

I have been selected to speak at the SQL sqlsat614_headerSaturday Event #614 this year on “On-Premise Reporting - Choosing the Right Strategy (SSRS 2016 & Power BI)”.

Thanks Leila Etaati and Hamish Watson

In today's world Enterprises face challenges in choosing the right Reporting tool that provides great visualisations, easy and secure to use and improves collaboration.

SQL Server Reporting Services (SSRS) 2016 offers many new features and capabilities that makes it a complete solution for On-Premise Reporting.

In this session, I will walk you through the

  • New features of SSRS 2016
  • Power BI Integration both in Native and SharePoint modes
  • Power BI Mobile Apps to view SSRS Reports
  • SSRS Tools

I am really excited to be speaking at SQL Saturday event. It’s really a great community event and looking forward for that. Register here for free for the event.

Thursday, February 16, 2017

Import JSON file using Power BI Desktop

Yesterday at the .NET user group session I was asked a question about importing JSON data using Power BI Desktop. Unfortunately I didn’t have a sample file to show it, so here is the blog post to compensate.

To get sample data, as long as you have SQL Server 2016 installed you can now take the contents of a table and output in JSON format just with T-SQL. I ran the following SQL on my AdventureWorksDW2014 database to get the JSON output and stored it in a file.

SELECT TOP (1000)
      [EnglishProductName]
      ,[Color]
      ,[SafetyStockLevel]
      ,[ReorderPoint]
      ,[EnglishDescription]
      ,[StartDate]
      ,[EndDate]
      ,[Status]
  FROM [AdventureWorksDW2014].[dbo].[DimProduct]  FOR JSON AUTO;

Now the JSON file is ready and now let’s see how to import it using Power BI Desktop.

1. Open Power BI Desktop and click on Get Data from the Tool bar and click More to see list of available Data source options and select JSON

JSON1

2. Click on Connect to select the JSON file you created. [JSON sample to download] Once imported you can see the following screenshot.

JSON2

3. Click on “To Table” from the menu to convert the data to Table.

JSON3

4. Click on Split to split the data in the table to individual columns

JSON5

5. You can rename the column by clicking on each column name and typing the new name you want.

JSON6

6. All the columns have been imported as Text Data type. Change the data type of SafetyStockLevel and ReorderPoint to Whole Number.

JSON7

7. Now you have successfully imported the JSON file in Power BI Desktop and go ahead a build your Report. [Download .pbix file]

 

Technorati Tags: ,

Wednesday, February 15, 2017

.NET User Group meeting - Feb 2017

My presentation today at the .NET User Group on "Power BI Embedded and Business Applications" went really well. Quite a good interest on Power BI with the .NET community. That's great to see.

Watch the Power BI Custom Visual session from the MS Ignite NZ 2016 as discussed in today's session

Check the Presentations page to download the slides.

Application screenshots which I couldn't show at the session.



 
 
 
 
 
 
 
 
 
Screenshot with the filter option shown as part of the application


Sunday, February 05, 2017

Set up a sample SQL Database on Azure

1. Log in to the Azure Portal.

2. Select SQL Databases from the side menu

Azure-SQLDatabase1

3. Click on Add to create a new SQL Database

Azure-SQLDatabase2

4. Type the Database name, select a resource group (can be either new or existing) and Select source as Sample (AdventureWorksLT) to create a sample database with tables or go with Blank Database option if you want to create your own tables

Azure-SQLDatabase3

5. Configure the Server and then click Create button.

Azure-SQLDatabase4

6. Once the sample SQL Database is successfully deployed, you can see the details in the below screen.

Azure-SQLDatabase5

7. Note, to change the pricing tier to basic if you are using for learning purposes.

Azure-SQLDatabase6

8. Connect to the Azure Database server using SQL Server Management Studio.

Azure-SQLDatabase7

9. You will be prompted to sign into your Azure account as shown below.

Azure-SQLDatabase8

10. Once signed in successfully, a firewall wall rule will be created to allow your PC to communicate to the Azure database.

Azure-SQLDatabase9

11. You can now successfully connect to the Azure Server and browse the AdventureWorksLT sample database.

Azure-SQLDatabase10

Technorati Tags: ,