Steps to connect to Sql server on Windows Azure Virtual Machine from local machine


Here are the detailed steps to connection Sql instance in a Windows Azure Virtual Machine, from your local machine, through SQL Server Management Studio.

Assume you have already had Sql Server installed on the VM, and SSMS installed on your local machine.

1, Configure your virtual machine’s firewall to allow connection to port 1433

a)      Go to windows firewall, launch Advanced Settings;

b)      Select Inbound rules on the left, and click “New Rule…” on the right;

c)       Select Port in New Inbound Rule Wizard, click Next;

d)      Make sure TCP is selected, and put 1433 in Specific local ports text box, click Next;

e)      Select Allow the connection, click Next;

f)       Select Domain, Private and Public, click Next;

g)      Put whatever name in the Name field, click Finish;

2, Create an endpoint for the VM, to forward port 1433 to 1433

a)      To go the new Windows Azure portal

View original post 184 altre parole

Dynamically Changing A Chart Axis In Power BI Using Bookmarks And Buttons

Chris Webb's BI Blog

A very common requirement when building Power BI reports is to allow the end user to change what is displayed on a chart axis dynamically. A lot of people have blogged about how to do this – Kasper’s blog post here is a great example – but the problem is that all of these solutions involve a lot of work remodelling your data and writing DAX code. However, the good news is that now we have Bookmarks and Buttons in Power BI there’s a new, easy, code-free way of achieving the same result, at least for some chart types. In this post I’ll show you how using the same data that Kasper used in his post.

Say you have the following dataset (using data from the Adventure Works DW sample database) in Power BI Desktop:


…and you need to display a column chart that shows the sum of SalesAmount broken…

View original post 329 altre parole

Power Bi group by Index

It can often be useful to create an index column in power bi. But power Query does not allow you to create a grouped index for a specific product category. Let’s predict this simple example:

Image 004.png

As you can see we have two columns, one that contains the group (column 1) and one that contains values ​​(column 2). First of all on power query we have to add the index column starting from 1 and close the window.

Image 005.png

Now that we have the index column, the next step is to create a calculated column that uses the following measure:

SequenceColumn1 =
VAR CurrentValue = Sheet2[Index]
ALLEXCEPT ( Sheet2; Sheet2[COLUMN 1] )
Sheet2[Index] <= CurrentValue


  • Current Value: is the column used to order our value. In our case is the index but we can order the column using a specific value
  • Allexcept: have to contain the column used to do the group by of countrows
  • Sheet2[Index] <= CurrentValue: is the same value used to create order

The result is :

Image 006.png

As you can see now the index is grouped by a specific attribute and is repeated for each attribute combination.

Power Bi Map BOX

While using Twitter I found a new interesting map to use on power bi. It’s called Mapbox and is a service to be activated and used to generate maps in power bi destkop:

Image 003.png

To activate this type of map, you must first go to the website and register for the site.The service is paid, but under a certain limit of views is completely free. In particular:


Once registered, on your home page you have to select the “Access Tokens” tab and then click to create a new Token. The Token will serve us to enable the power bi map. Now let’s go to power bi desktop, click on the “From marketplace” tab. then in the window select map and go down until you find “Mapbox Visual Preview”:

Image 1

Then add our custom visual and select the longitude and latitude fields (remember to have correctly configured longitude and latitude on power bi). At this point the custom visual will tell us that it needs the access token. Let’s go back to the site where we generated the token, copy it and paste it into the “Token” text-box under Viz setting in the Format custom visual section:

Image 001


The game is done! now you can safely use the map. Why use this map instead of the others?

  • it’s very quick to create the map
  • has many usable standard views
  • it is easy to use
  • allows you to create completely custom maps with customizable icons based on projects and maps of others using a specific and user friendly tool on

Image 002.png

Stop SSRS Rendering on SQl

Sometimes occur that a specific SSRS subcription is in pending or blocked or using too time to create report. if you want to force the stop of subcrption you can do it using this two string of code directly on sql database of report server:

truncate table  [ReportServer].[dbo].[ActiveSubscriptions]
truncate table  [ReportServer].[dbo].[Notifications]
Done that your Subscription is stopped but his status is not refreshed and remain on pending. If you want to change Status you have to execute this script:
update ReportServer.dbo.Subscriptions
set LastStatus = ‘Done: 0 processed of 0 total; 0 errors (Stopped).’
where Description = ltrim(rtrim(SubcriptionName))

Power BI desktop January News

Happy new year to everyone!

Here’s the complete list of January updates:


  • Show and hide pages: you can now hide any number of pages you want through right clicking the page name and selecting hide.
  • Control data label background color for Cartesian and maps visuals:  You’ll find the option under the Data labels card in the formatting pane.
  • Increase area used for axis labels in charts:  This new slider can be used to increase the percentage of the chart used by the axis labels.
  • Bar/column padding control: added the ability to control the inner padding between bars. You can make the inner padding up to 50% of the bar’s width.
  • Show dates as a hierarchy (preview): created it automatically for you when you drag your data column into a visual.  You’ll need to turn on the preview feature switch under File > Options and Settings > Options > Preview features.
  • Add an anchor date for relative date slicer: Once you have an anchor date set, the slicer will be evaluated relative to that specific date permitting to start further back in time than today. . You can set it in the date range options in the formatting pane.
  • Top N selection in Q&A


  • Correlation coefficient quick measure: calculates the Pearson correlation coefficient between two measures within a certain category

Custom Visuals

  • PowerApps: With the new PowerApps custom visual, you aren’t only able to quickly find insights in your data, you can take action on it as well.
  • TreeViz:you display your hierarchical data in a tree like structure. You can expand and collapse each level and decide how many children data points to show before grouping the smaller ones into a single child group.clip_image023
  • Funnel with Source by MAQ Software: The Funnel with Source custom visual is perfect to track any metric of interest over various stages along with the source of entry of the data point to the funnel.
  • clip_image025
  • Box and Whisker chart by MAQ Software:clip_image027
  • Agility Planning Matrix Light: Some benefits of this matrix visual is the ability to selectively drilldown on specific rows and to display currently applied filters on the visual itself.clip_image029
  • Image Grid: similar to a word cloud but with image:clip_image031
  • Gantt Chart by MAQ Software: This visual shows additional details of the task by providing a grid where one can view more data related to the task and can display the hierarchy of data category. Also, it can be sorted based on any data point of the task.

Data connectivity

  • Support for Azure Active Directory authentication for Azure SQL Database & Data Warehouse connectors: With this month’s update for Power BI Desktop, they are introducing a new “Microsoft Account” option within the Credentials dialog for the Azure SQL Database and Data Warehouse connectors, allowing users to authenticate using their AAD accounts.


  • Advanced language settings for the Windows store app: You’ll see these new options under the Regional Settings tab in the Options dialog. Changing the application language will ignore the Windows default display language and switch to the chosen language. Changing the model language will decouple the model language from the application language for any newly created reports. (Note that this won’t affect the model language for existing reports.) Changing either of these settings requires a restart.

Determine free space for SQL Server

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach. Also, this information can be used to shrink data files as needed if there is a lot of free space in the files.

One option is to use sp_spaceused. If we run the following command we can see how much free space there is in the database, but this shows us total free space including the transaction log free space which may be totally misleading.

USE Test5 



sp_spaceused output

Another option is to use the SpaceUsed property of the FILEPROPERTY function which will tell us how much space is used and then we can calculate free space based on the current size and what is being used.

USE Test5 

name AS FileName, 
size/128.0 AS CurrentSizeMB, 
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Here we can see the output from the above for the Test5 database.

sql server fileproperty output