Read .gz file in Power BI Desktop

There could be multiple ways to load .gz files in power BI desktop. Instead of decompressing the files, you can read and load .gz file directly in Power BI using R Script. It’s pretty fast and require less line of codes.

Click on Get data option in Power BI Desktop and choose “R Script” and click on connect.

Then use following R Script to load .gz files into power BI Desktop.

install.packages -> data.table
library(‘data.table’)
data <- fread(file=”C:/Users/aansari/Development/Jay/base_BookingsChargesBridge.csv.gz”)
data

Happy coding!!!

Execute SSRS Subscription on Demand

There could be many ways to fire SSRS subscription but here i will explain how to fire SSRS subscription on demand using RS.EXE utility.

First step is to create your report Subscription through Report Manager using option “New Subscription” or “New Data-driven Subscription” by navigating => Properties => Subscription . Inside your create Subscription, there will be “Select Schedule” button. Click on this button and choose “Once” as option and give any past time under “Start Time” and complete required details for your subscription and click “Ok” button to create your report subscription.

Second step is to create a .RSS file that will be used to trigger newly created subscription in first step. Open Notepad and put below script and save this Notepad file as “..\\Subscription.rss“. You can choose any file name with extension .rss

you can put multiple rs.FireEventstatement inside Sub Main block to fire multiple report’s subscriptions at the same time.

Sub Main() 

rs.FireEvent(“TimedSubscription”, ““) 

End Sub

Now, we need to get SubscriptionIDof newly created subscription in our first step to use it above block (highlighted as yellow). For that, you need to login to Sql Server Management Studio and execute below query on your ReportServer Database.

select s.SubscriptionID,sD.name subscriptionname,c.Name as reportname from Subscriptions s
inner join ReportSchedule RS on RS.SubscriptionID=s.SubscriptionID
inner join Catalog c on c.ItemID=RS.ReportID
inner join Schedule SD on SD.ScheduleID=RS.ScheduleID
where c.Name=’‘

will be name your report for that you have created subscription in first step.

Third step to execute .rssfile that we created in second step. Again open a new notepad and put below code and save your file as “..\\RunSubscription.bat“

\rs.exe -i “..\\Subscription.rss” -s http:///reportserver

We are done. you can run your RunSubscription.bat by double click on .bat file.

Last ran Query in Sql Navigator

There are two ways to find past ran queries in Sql Navigator:

1) Click (see image-1) on SQL History or press (CTRL+ALT+R) 

 

Image-1

2)Find the path …\SQL Navigator 6.2.1\Unified Editor\history in your system.

SharePoint Items Security using SharePoint Powershell

We can use following SharePoint PowerShell script to pull all SharePoint items security whether an item is using unique permission or inherited permission. The output will be written in a .csv file “SharepointSitesOutput.csv”

Just copy and paste following code in .ps1 file and execute that file on SharePoint PowerShell commad using commad &

 

get-spsite -Limit All|get-spweb -Limit All|Select URL,Title, hasUniquePerm |Export-csv SharepointSitesOutput.csv –NoTypeInformation

List SSRS items Permissions using PoweShell

If we pull SSRS Items security using ReportServer database using following query then we get stale information. It includes those users as well that has been deleted/deactivated in Active directory.

select C.UserName, D.RoleName, D.Description, E.Path, E.Name

from dbo.PolicyUserRole A

   inner join dbo.Policies B on A.PolicyID = B.PolicyID

   inner join dbo.Users C on A.UserID = C.UserID

   inner join dbo.Roles D on A.RoleID = D.RoleID

   inner join dbo.Catalog E on A.PolicyID = E.PolicyID

order by C.UserName

So instead of using query at ReportServer Database, we can use reportservice2005.asmx GetPoliciesmethod. Following is the Powershell Script that writes the SSRS Folders permissions in SSRSSecurityOutput.csv. Just copy and paste the following code in a .ps1 file like SSRSPermissions.ps1.

.Ps1 is file extension for poweshell script.

$ReportServerUri = ‘http:///ReportServer/ReportService2005.asmx&#8217;

$InheritParent = $true

$SourceFolderPath = ‘/’

$outSSRSSecurity=@()

$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential

$items = $Proxy.ListChildren($sourceFolderPath, $true)|Select-Object Type, Path, Name|Where-Object {$_.type -eq “Folder”};

foreach($item in $items)

{

Add-Member -InputObject $item -MemberType NoteProperty -Name UserName -Value ”;

foreach($policy in $Proxy.GetPolicies($item.path, [ref]$InheritParent))

{

                $objtemp=$item.PsObject.Copy();

                $objtemp.UserName=$policy.GroupUserName;

                $outSSRSSecurity += $objtemp;

                $objtemp.reset;

}

}

$outSSRSSecurity|Export-csv SSRSSecurityOutput.csv -NoTypeInformation;