Power BI Connector - Web by Example

Web by Example is a new feature update from Power BI. Here is the step by step illustration.

1. Get Data --> Web

2. Enter a proper URL --> OK; I have used rottentomatoes Example :

3. On "Access Web Content" Page --> Choose Authentication --> Connect

4. Wait while its "Connecting..." :)

5. On Navigator window Click "Add table using examples"

6. In Column1 first row start entering Names of movies as examples. Enter at least 2 names. Power BI will automatically populate the other movie names in Column1 based on the examples provided.

Similarly Enter % Likes for at least 1st 1 or 2 movies as example and the rest is detected automatically by Power BI.

Similarly in 3rd or more columns as per your choice...

Rename the columns.

7. Load the Custom Table

8. Start creating reports...

Please provide your valuable feedback on this blog in comment section. Thanks,

Paras Singh

19-Jan-2019

Configure Database Mail in SQL Server

How to configure Database Mail in SQL Server ?

Introduction : Database mail is a SQL Server component that uses the 'Simple Mail Transfer Protocol' or SMTP to send email messages. Follow the below given steps to configure database mail:

STEP 1 : Connect to SQL Server Management Studio (Preferably run as Administrator). Go to Management and double click on Database Mail. A wizard as shown below will open. Click Next.

STEP 2 : Select Configuration Task window will appear. If you are installing Database Mail for the 1st time select the set up option radio button : Set up database mail by performing the following tasks. In case you want to modify or manage a previously configured database mail chose other options as applicable to you.

STEP 3 :  Choose Create a new account :

STEP 4 : Provide the following values

Account Name : Any Name
Description : Optional
Email address : Your Email Address
Display Name : Any Name
Reply Email Address : Optional
Server Name : Where\How to get server name is described below :
Port : Usually 25
Tick server requires SSL
SMTP Authentication Method : Choose any, I have chosen basic authentication

Now  Where\How to get server name ? The easiest approach :

Send a mail from your outlook in the format : wrongname@yourcompany.com (Please note that part of email id before @ should be wrong and not the part after @ )

You will get back MAIL DELIVERY FAILURE MESSAGE as shown below from the Server. Find the GENERATING SERVER name in this mail.

Provide this server name in Server Name.

STEP 5 : Complete the wizard. Finish.

And you Database mail is configured now.

Now lets TEST : Run this query to test :


EXEC msdb.dbo.sp_send_dbmail 

 @recipients='Provide email id here whom you want to send mail',  

 @profile_name = 'Account Name you gave in STEP 4',  

 @subject = 'Any Subject Line',  

 @body = 'Type Message Body Text',  

 @file_attachments='Attachment Full File path with file name';

The recipient must receive mail provided if all is good. To check the quue and status of mail like : Sent\Unsent\failed\Retry etc, Run this query :

USE MSDB
GO

Select * from sysmail_allitems

To check error messages :

USE MSDB;

GO

SELECT items.subject,

    items.last_mod_date

    ,l.description FROM dbo.sysmail_faileditems as items

INNER JOIN dbo.sysmail_event_log AS l

    ON items.mailitem_id = l.mailitem_id

WHERE items.recipients LIKE '%%' 

    OR items.copy_recipients LIKE '%%'

    OR items.blind_copy_recipients LIKE '%%'

GO

 In some cases if you are using ssis send mail task or using a ssis job to send mails with attachment; the SQL Server account or SQL Server Agent User may not have access to the Folder from which you are taking the file to be attached. You may get error : SQL Service account not having the correct access to the shared drive :

To solve this :

Grant access to the folder : 

NT SERVICE\MSSSQLSERVER if running SSIS Package or Running a SQL SERVER to send mails :

If using SSIS JOB then add NT Service\SQLSERVERAGENT User :

Follow the screenshots :

STEP 1 : Right click the folder containing the attachment and go to properties Click Edit then Add :

STEP 2 : Browse for NT SERVICE\MSSSQLSERVER or NT Service\SQLSERVERAGENT, change location if required; Check Names and OK: Once users added tick Full control and Modify under Allow : Apply

That's it.

Thanks for reading. Please like and follow & Suggest improvements.

Follow me on twitter @msbigyan

Thanks.

Paras Singh

Senior Consultant : BI & Analytics

SSIS 2016 - Exploring New Features

In this Blog we'll list out 11 new much awaited features in SSIS 2016.

Note : By the time I am writing this blog SQL Server 2016 (RC3) and CTP 3.3 is there in the market.

1. Incremental Package Deployment : 

In SQL Server 2005 and 2008 we used to deploy either through file system or to the msdb database. We used to deploy a package at a time and if we need to delete\update\add a new package, we simply were able to deploy that 1 package. But tin 2012 and 2014 we had to deploy the entire project containing all many packages at one go. The drawback was even if 1 package is changed the entire project had to be deployed again.

Thankfully in 2016 we can deploy entire project in one go as before and we also now have the flexibility to deploy package-by-package.

2. Separate Installation of SQL Server Database Engine and SSDT : 

Compared to SQL SERVER 2005, 2008 we used to install SQL Server Database Engine and BI Tools in one go by just selecting the features; SSDT is installed separately in SQL Server 2012, 2014 and continues in 2016.

3. Custom Logging Level in SSIS Catalog : 

Previously we had 4 built-it logging levels to choose from : Basic, Performance, Verbose and None. Now we can create multiple custom logging levels and save them to use\choose while running packages. Hence it is flexible, reusable, portable and easy to use. While creating custom logging level we have 3 categories of statistics to choose from : Executable Execution Statistics, Component Data Volume Statistics & Component Execution Statistics. 

To show custom logging levels we now also have a new view catalog.cutomized_logging_levels.

A new role ssis_logreader role has also been introduced.

4. SSDT - Visual Studio 2015 accommodates all different versions in single installation :

No more requirements for installing several versions of SSDT. In configuration properties we can now choose TargetServerVersion as SQL Server 2012, SQL Server 2014 or SQL Server 2016.

5. Column Name in error output : 

Now we have full path to error (Error Column - Description) column in output not just column id. Great help in debugging and identify the bad columns.

6. Package Parts : 

We can now create a new control flow package part file (.dtsxp)  and save it for reuse if something is to be reused multiple times. It will reflect in Package Parts folder and also in SSIS Control Flow Tool Box under package parts. Note that it is visible only for that project only.

7. Explicit Support for Excel 2013 and Excel 2016

8. Connection Mangers for HDFS : 

For performing common HDFS operations we have connection managers to connect to hadoop clusters and tasks.

9. SQL Server Import Export Wizard : 

Now we can import\export data to\from Azure Blob storage using Import Export Wizard

10. Project Upgrade : 

When we upgrade from prev version to current version project level connection mangers work as expected, Layout and annotations remain unchanged.

11. Enable Always on support for Integration services catalogs :

Thanks you for reading. Please like and comment
Paras Singh

Follow me on twitter @msbigyan

Create Virtual Machine on Microsoft Azure and connect in minutes

This is a step by step illustration of how to Create Virtual Machine on Microsoft Azure and get started in minutes with 15 steps :

STEPS WITH SCREENSHOTS :

STEP 1 :

Login to new Azure Portal

STEP 2 : Virtual machines --> Add

STEP 3 : Search the Windows Server with the Software of your choice with which you want to provision your machine. Here I have selected "SQL Server 2016 RC3 Evaluation on Windows Server 2012 R2"

STEP 4 : Select a Deployment model : Resource Manager or Classic. Every resource you create through resource manager exists within a resource group. Click on Create.

STEP 5 : Configure Basic Settings : Provide Name, User name, password : "Remember the password". In resource group select any existing resource group which you made earlier or make a new one. Select a location.

STEP 6 : Choose VM Size. Make a wise choice based on cost.

STEP 7 : Configure Optional features : You can go with defaults.

STEP 8 : In may case I have to configure SQL Server settings. I go with defaults.

STEP 9 : Check summary. If you want to modify click on previous steps and do again :

STEP 10 : Deployment usually takes some time. For me it took 4-5 minutes. Check Notification

STEP 11 : Final blade will look like this : Look at the 3 important links on top : START, STOP and CONNECT. 

STEP 12 : Click on Connect. This will download a .rdp . Click open to connect(RDP) to your Virtual Machine.

STEP 13 :

STEP 14 :

STEP 15 : That's it : ALL SET Your Virtual Machine is ready to use now.

In case you want to download anything on your Machine you need to disable "IE Enhanced Security Configuration" and then you can use your Internet Explorer to download :

Thanks,
Paras Singh
Note : Please leave your comments\suggestions and like this post

Follow me on twitter @msbigyan

10 key features of SQL Server 2016 Preview (Coming soon) to watch out for:

1. Performance Enhancements :

• Operational Analytics: Insights on operational data, works with in-memory OLTP or on disk

• In-memory OLTP enhancements: Greater T-SQL surface area, terabytes of memory supported and greater number of parallel CPUs

• Query Data Store: Monitor and optimize query plans with full history of query execution

• Native JSON: Parsing & storing of JSON as relational data & exporting relational to JSON

• Temporal Database: Track historical changes 

2. Security Related Upgrades :

• Always Encrypted: Help protect data at rest and in motion with the master key residing with the application & no application changes required
• Row Level Security: Apply fine-grained access control to table rows based on users rights
• Dynamic Data Masking: Real-time obfuscation of data to prevent unauthorized access

3. Enhanced AlwaysOn:

• Up to 3 synchronous replicas for auto failover across domains
• Round-robin load balancing of replicas
• DTC & SSIS support
• Automatic failover based on database health

4. Improved Scalability:

• Enhanced Database Caching: Cache data with automatic, multiple TempDB files per instance in multi-core environments
• Support for Windows Server 2016

5. Access Any Data :

• PolyBase: Manage relational & non-relational data with the simplicity of T-SQL
• Power Query for Analytics and Reporting: Pull data from countless sources into data models and reports
• Enhanced SSIS: Designer support for previous SSIS versions and support for Power Query

6. Manage and Scale:

• Enterprise-grade Analysis Services: Enhanced performance and scalability for Analysis Services
• Single SSDT in Visual Studio 2015: Build richer analytics solutions as part of your development projects in Visual Studio through a single tool.
• Enhanced MDS: Excel add-in 15X faster, more granular security roles, archival options for transaction logs, and reuse entities across models

7. Powerful Insights on any device :

• Mobile BI: Business insights for your on-premises data through rich visualization on mobile devices with native apps for Windows, iOS and Android
• Enhanced Reporting Services: New modern reports with rich visualizations

8. Built-in Advanced Analytics:

• Predictive analytic algorithms directly in SQL Server.
• Expand your “R” script library with Microsoft Azure Marketplace. 

9. Hybrid Solutions :

• Stretch Database: Stretch operational tables in a secure manner into Azure for cost effective historic data availability works with Always Encrypted and Row Level Security
• PowerBI with on-premises data: New interactive query with Analysis Services. Customer data stays behind your firewall
• Hybrid Scenarios with SSIS: Azure Data Factory integration with SSIS, package lineage and impact analysis and connect SSIS to cloud data source
• Enhanced Backup to Azure: Faster restore and 50% reduction in storage, support larger DBs with Block blobs and custom backup schedule with local staging

10. Simplicity and Consistency:

• Easy migration of on-premises SQL Server: Simple point and click migration to Azure
• Simplified Add Azure Replica Wizard: Simplified hybrid HA & DR with AlwaysOn
• Common Development, Management and Identity Tools: Including Active Directory, Visual Studio, Hyper-V and System Center
• Consistent Experience from SQL Server on-premises to Microsoft Azure IaaS and PaaS

Power Map

 Power Map

Software Requirements:

• If you have any subscription for Microsoft Office 365, you have access to Power Map for Excel as part of the self-service business intelligence tools.
• Whenever any new Power Map features and performance enhancements are released, you'll get them as part of your subscription plan.
• If you have Office 2013 Professional Plus or a standalone version of Excel 2013, you can download and use the Power Map Preview for Excel 2013 as an add-in.
• You can download Power Map Addin from this link:
• http://www.microsoft.com/en-in/download/details.aspx?id=38395

What is Power Map?

• Microsoft Power Map for Excel 2013 is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. You can discover insights that you might not see in traditional two-dimensional (2-D) tables and charts.
• With Power Map, you can plot geographic and temporal data on a 3-D globe, show it over time, and create visual tours you can share with other people.

You’ll want to use Power Map to:

• Map data:  Plot more than a million rows of data visually on Bing maps in 3-D format from an Excel table or Data Model in Excel.
• Discover insights: Gain new understandings by viewing your data in geographic space and seeing time-stamped data change over time.
• Share stories:  Capture screenshots and build cinematic, guided video tours you can share broadly, engaging audiences like never before. Or export tours to video and share them that way as well.

You’ll find the Map button on the Insert tab of the Excel ribbon, as shown in this picture:

Let us create a Power map report:

STEPS:

STEP 1: Open Excel 2013 having your data with at least one column as country\State\Province\Zip Code\City name\Address (which Bing Map can identify\locate)

STEP 2: Click on Insert à Map à Launch Power Map\Add Selected Data to Power

STEP 3:

Power Map uses Bing to geocode data based on its geographic properties. After a few seconds, the globe will appear next to the first screen of the Layer Pane.

This window has 4 visible parts:

•  Power Map Ribbon: On Top
• Tours Window: Left Pane
•  Layer Pane : Right side
•  Map Window : Middle

      Tours window will open if you have already created a power map earlier and want to add\edit new\existing tours as shown below :

    

     In layers pane on left side; we can choose geography based on available data:

     Based on our selection Geography is visible as dots on the Map.

    STEP 4:

    

    Click NEXT and start Exploring:

    Select values, Category, Time (If any column have time based data) and create nice visualizations.

    Additionally you can create Tours, Add more layers, Create videos, create flat maps etc…

   

    

    Let’s discuss the available options on ribbon:

           TOUR:

1.       Play Tour : Switch to tour view and watch it play

2.       Add Scene: Add a new scene to the list of existing scenes in your tour.

3.       Create video: Turn the tour you have created into a video so that you can share it.

4.       Capture Screen: Take a static picture of your current map to put it on the clipboard so that you can paste it somewhere else.

MAP:

5.       Themes: Pick a theme to give your tour instant personality or a more professional look. Each scene can have different theme and a theme can have different colors. Some themes even have satellite images for the globe.

6.       Map Levels: Show or hide the labels on the map.

7.       Flat Map: Switch between globe and flat map format.

8.       Find location: Go to any city, address, state, or other geographical location on the map. Just enter the one you want and search by typing the name of the location you would like to find.

LAYER:

9.       Add layer: Add another data layer to the selected scene.

10.   Refresh data: Update all layers of all scenes to show the most current data.

11.   Data Shapes: Change the shape of the columns on the map.

INSERT:

12.   2D Chart: Show the top or bottom 100 locations by value for any data layer on a 2D Chart, and quickly navigate to each location.

13.   Text Box: Add text to a scene that stays on the screen when playing your tour. Resize it or move it to the place you want.

14.   Legend: Show the legends for all data layers in the scene. You can turn individual legends ON or OFF afterwards.

TIME:

15.   Time Line: Show or hide the Time Line control. Use this to play a tour over time. This option is available when a time field is added to a Scene’s time box.

16.   Date & Time: Show or hide the date or time you have added to the time box in the scene. This option is available when a time field is added to a Scene’s time box.

VIEW:

17.   Tour Editor: Show or hide the tour editor. Use the tour editor to view all scenes in your tour and to rename your tour.

18.   Layer Pane: Show or hide the layer pane. Use the Layer pane to manage layers, add data to layers and change settings.

Let me add some visuals of maps and videos which I have created to attract your interest in Power Map :

Note : India Population data used in this Map has been downloaded from Public Catalogue available for download from govt website using power query. Watch out for my blog on Power Query Coming Soon...

There are plenty of other options for you to explore. 

So Get...Set...GO...

Enjoy using Power Map!!!

Thanks,

Paras Singh

04th Feb 2015

07:18 PM IST

Click here to Follow me on Linkedin

Follow me on Twitter @MSBIGYAN