Power BI Report Server

Power BI Report Server  is a new product which can be used to host both Power BI reports and SSRS reports.

Currently beta version is available   at https://powerbi.microsoft.com/en-us/report-server/

Below are the few key notable aspects of  Power Bi Report Server

• PowerBI Report Server will be  part of power BI premium package  which avoids per user licensing .
• PowerBI Report Server can be used as mixed environment both for on premise and cloud hosted environment

Few Notable points on Power BI  report management  are  

• PowerBI Report Server has a similar web portal as Reporting services which can host  both both Power BI reports and SSRS reports as shown below
• Most of the  report server management properties are similar to SSRS report server

As shown in below  figure I have created few report folders for SSRS Paginated report and Power BI reports

I have uploaded a Power BI report file   as shown below

As shown it gives a range of options to view manage and edit   the report

Below report shows the Power BI report view when opened in portal,the report is built using Analysis services data a source,currently as of today in beta version  power bi reports  deployed on to the  power bi report manager can only use live data source.

Power BI Report Server and Power BI Cloud integration:

Power BI Report server reports can be pinned to PowerBi cloud dashboards


Below steps need to be followed for the integration

Report configuration need be updated for cloud integration as shown below

Report Manager needs to be updated with the power bi cloud details  in user settings of report manager as shown below

As the integrations steps have been addressed now reports can be pinned  as below by clicking

the below selected section

Upon selection it asks which cloud dashboard the report needs to be pinned as shown below

Upon selection of the report item it gives option to select the workspace and dashboard hosted on the Power Bi cloud as shown below

Note:All content in this post is subject to change and all the opinions expressed are my own opinion and not associated with any organisation and use of the information is at individual users risk.

Addressing SSRS Excel Rendering Issues of Cell Spreading and Format loss

SSRS   reports when rendered to an excel format , some times creates       cell spreading and this can become an  issue ,when the end user want to analyze the results and do some analysis on the worksheet.

The cause of the problem usually lies   with the use of    any text boxes  or rectangles on any  header or on the top of tablix  or table structure .

In the above report i have placed a text box on the report header to display the report name Contoso Adventure Works and as a result cell spreading of subcategory field has occurred   and it occupies two cells B and C

One of the solution' to this problem is    set those items to be hidden when rendered,in my case as   i was using SSRS 2012 and rendering to an excel format i have used a global parameter in SSRS  called Globals!RenderFormat. Name and set the hidden expression like below

  IIF(Globals!RenderFormat.Name="EXCELOPENXML",True,False)

After setting this property now the cells remain intact  as shown below

Another  Common issue is the format setting on the data columns  is not preserved when the report is exported to excel ,the   solution to this problem is to use the   same format  expression   in the format function of the actual fields  and leave the format  property to empty   as   we are not setting any custom formatting on the format property the excel render will display whatever  value in that column

Querying SSRS Report MetaData for data sources , datasets associated query and connection string information

SSRS  metada    contains wealth    of information which   can be usefull   under varieous circumstances , but extracting    that information can be a timefull process , the basic information  is   stored    under the ReportServer  database  tables, but to retrieve the embedded data  from the report xml  ,the data needs to be   extracted into readable format from an xml format.

The below query  queries the report catalog table of ReportServer  Database   and flattens the xml content   from the Catalog table.

In this below dataset i have  mainly flattened the  Report xml   data   from the Content column of the Catalog table  ,  which was initially converted  to a varbinary type and than to xml,After the conversion to xml and  flattening  the xml , we can associate report name with its related datastes and datasources and connection string     as below



Declare @ReportName varchar(200)
Set @ReportName='SomeReport'
;With Cte
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] AS varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] WHERE type in (2))t 
where t.Name=@ReportName
)
/*** Get the Report Name and DataSource Name ***/,
ReportDataSources
As
(

Select 
dss.dtt.value('(@Name)[1]','varchar(100)') as dsDataSourceName,
Ltrim(Replace(dst.dts.value('(text())[1]','varchar(max)'),'/Data Sources/','')) AS DataSourceName,
Cnts.Cn.value('(text())[1]','varchar(100)') as Cnstring
FROM Cte
cross Apply cte.Content.nodes('//*:DataSource') dss(dtt)
Outer Apply dss.dtt.nodes('.//*:DataSourceReference') dst(dts)
outer Apply Cte.Content.nodes('.//*:ConnectString') Cnts(Cn)
) 
,ReportsAndDS
As
(
SELECT Distinct 
cte.name AS ReportName,

dss.dtt.value('(@Name)[1]','varchar(100)') AS DatasetName,

ds.dstt.value('(text())[1]','varchar(max)') AS DataSourceName,
Ltrim(Replace(shared.Sha.value('(text())[1]','varchar(100)'),'/Datasets/','')) AS SharedDataSet,
COALESCE(ct.cts.value('(text())[1]','varchar(max)'),'ReportQuery') as QueryType,
dst.dts.value('(text())[1]','varchar(max)') AS Query
FROM Cte
cross Apply cte.Content.nodes('//*:DataSet') dss(dtt)
cross Apply dss.dtt.nodes('.//*:CommandText') dst(dts)
Outer Apply dss.dtt.nodes('.//*:CommandType') ct(cts)
Outer Apply dss.dtt.nodes('.//*:DataSourceName') ds(dstt)
Outer Apply cte.content.nodes('.//*:SharedDataSetReference') shared(Sha)

)

,FtReportsAndDs
As
(

Select rs.ReportName,rs.DatasetName,rds.DataSourceName,rs.SharedDataSet ,rs.QueryType ,rs.Query, Substring(rds.Cnstring,CharIndex('=',rds.Cnstring,1)+1, CharIndex(';',rds.Cnstring,1)-(CharIndex('=',rds.Cnstring,1)+1)) as ServerName
from ReportsAndDS rs
Left Join ReportDataSources rds 
on rs.DataSourceName=rds.dsDataSourceName
)
/*** Get the DatSource Name and DatSource XML ***/
,DataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (5))t )
,SharedDataSources
As
(
SELECT t.name,Convert(XML,t.Content) AS Content FROM (
SELECT [Name],Cast ([Content] as varbinary(max)) AS Content
FROM [ReportServer].[dbo].[Catalog] where type in (8))t )

,SharedDataSourceConnectionDetails
As
(
Select sdc.Name,dsr.ds.value('(text())[1]','varchar(100)') as DataSourceName ,
ddr.dr.value('(text())[1]','varchar(max)') as Query

from SharedDataSources sdc

Cross Apply sdc.Content.nodes('//*:DataSourceReference') dsr(ds)
Outer Apply sdc.Content.nodes('//*:CommandText') ddr(dr)

)
/*** Prepare the Final Query to Get Report Name and its asscoiated datasources datasets ,QueryType,Query and Connection String ***/

Select t.ReportName,t.DatasetName,t.DataSourceName,t.QueryType,t.Query, t.DatSourceConnectionDetails
,
t.DataSourceCredentials
, Isnull(Substring(DatSourceConnectionDetails,CharIndex('=',DatSourceConnectionDetails,1)+1, CharIndex(';',DatSourceConnectionDetails,1)-(CharIndex('=',DatSourceConnectionDetails,1)+1)),t.DataSourceName) as ServerName
from (
SELECT Distinct 
ds.ReportName,ds.DatasetName,IsNull(ds.DataSourceName,ds.ServerName) as DataSourceName,ds.Query,ds.QueryType,

dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials

FROM FtReportsAndDs ds Left join DataSources ctee ON
ds.DataSourceName=ctee.Name
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt)
UNION ALL
SELECT Distinct 
ds.ReportName,shd.Name as datasetName,shd.DataSourceName,shd.Query,ds.QueryType,
dss.dtt.value('(./*:ConnectString)[1]','varchar(100)') AS DatSourceConnectionDetails,
dss.dtt.value('(./*:CredentialRetrieval)[1]','varchar(100)') AS DataSourceCredentials
FROM ReportsAndDS ds Inner Join SharedDataSourceConnectionDetails shd
on ds.SharedDataSet=shd.Name
Left Join 
DataSources ctee ON
ctee.Name=shd.DataSourceName
Outer Apply ctee.Content.nodes('//*:DataSourceDefinition') dss(dtt))t

This is the snapshot of the above query without any filter applied for report  for this demo.

As you can see i haveflattened the xml content so that the we can see each report its associated dataset datasource name and its connection string, this can be very handy when we have very large number of reports   and wanted to migrate those reports to see the depended data sources   .

Splitting Strings in TSql using CTE

There are number of occasions where we need to split a string to individual values,one such common use  case is for multivalue parameters  in ssrs, when a stored procedure  is used  to  feed the dataset   we need to split the    string parameter  values  .


This can be implemented in a number of ways, i chooose to use a cte  and than embedd that cte in a table valued function,   and this use case  only  works  in sql server 2012 , for other versions, replace the  conditional IIF function with a case


Consider  u   got string values of countries as shown below

Declare @sp VarChar(max)='UK,USA,FRANCE,CANADA,MEXICO,BRAZIL,CHINA, MALAYSIA,TURKEY,INDIA,AUSTRALIA'


and we want to split it into individual countries ,to split the string, whatever logic we use, we need to know the  postion of  individual string  i.e the starting position and ending position of the sorrounded delimiters .

This is where recursive cte  can be very usefull,the way it is done is we use Charecter index   string function in the anchor query to get the first  end position of a delimiter   of first individual string   and 0 as the starting position, as there will be no delimeters at the start of string.

Than we use these values recursivly to get the  next delimeter position ,and to do that we  increment the previous  delimeter postion in the charecter index function start value,  in this case ,after the anchor query gets 3 as the first delimeter value  than it is incremented by 1 i.e   4 and this 4 will become the string starting  position for the second individual string i.e USA  and the charecter index gets the  next individual ending  delimeter position as 7 ,and this logic is repeated  recursively to build the starting and ending delimeter position for each string as shown in the second figure.


;With Cte
As
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

The above cte gets the delimited index values  recursivly    for each individual   string    as shown below

Once we  have these values ,the next statement
Select    SUBSTRING(@sp,IIF(t=0,1,t+1),IIF(tt=0,len(@sp)-t,tt-t-1) )  from Cte

simply selects the values between those delimeters
as shown below

 we can embedd this logic into a table valued function as shown below and subsequently use this function in the stored procedure  as shown in the query following this function


 Create   Function apn_split(@Sp varchar(max))

 Returns Table

 As
 Return
WIth Cte
as
(

Select   CHARINDEX(',',@sp,0)  tt, cast (0  as int) t
Union ALL
Select  CHARINDEX(',',@sp,tt+1),  cast(tt as int) as t      from  Cte
where  tt>0
)

Select    SUBSTRING(@sp,iif(t=0,1,t+1),iif(tt=0,len(@sp)-t,tt-t-1) )  as Country  from Cte



Consider to build to report to analyse sales data (in this example  i'm using AdwentureWorksDW2008R2) which will  allow the end user to  select multiple countries    as show below

Than you can use the above function in the stored procedure to pass  multiple values from ssrs to stored procedure  as shown below

Alter Procedure ap_ProductSalesByCountry

@Country varchar(max)
As
SELECT     DimProduct.EnglishProductName, DimProductSubcategory.EnglishProductSubcategoryName, DimProductCategory.EnglishProductCategoryName,
                      DimSalesTerritory.SalesTerritoryCountry, DimDate.EnglishMonthName, DimDate.CalendarYear, FactResellerSales.SalesAmount
FROM         FactResellerSales INNER JOIN
                      DimSalesTerritory ON FactResellerSales.SalesTerritoryKey = DimSalesTerritory.SalesTerritoryKey INNER JOIN
                      DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN
                      DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN
                      DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN
                      DimDate ON FactResellerSales.OrderDateKey = DimDate.DateKey
WHERE     DimSalesTerritory.SalesTerritoryCountry  in ( Select distinct country    from apn_split(@Country))

SSRS Parameter Post backs

Most people consider parameter postbacks in SSRS as  an  unwanted operation, but some times they are necessary ,I had  a situation where i need to post back when any of the parameters is selected 

In this situtaion ,create  a   new parameter at the bottom of all parameters   and set some value like datetime as default ,one important  thing to notice is that , when  you set this  default parameter sourced from a dataset query , than this will not work,it needs to  be sourced manually  as shown below.

Irrespective of what you  select in advance parameters (as always refresh or never refresh), setting the default value as above ,will always result in post back of all  the parameters   above   this parameter ,as long as this parameter  is the  last parameter .

Replacing Selfjoins with analytic functions in Sql Server 2012

Sql Server 2012 has introduced a  set of new  analytic  functions ,which helps to  get  different analytical   answers   from an dataset , with  a less complex and more understandable  approach.

The aim of this article is to implement newly introduced lag function ,and the same implementation with an old approach, in my case a self join   based.


DatasetUsed:Adventure Works2008R2

Imagine I want  tocompare  the sales in each country by  current  calendar year  with the sales for  previous calendar year for the below dataset


with cte
as
(
select  d.CalendarYear,t.SalesTerritoryCountry ,sum(s.SalesAmount) as sales  from [dbo].[DimDate] d inner join [dbo].[FactResellerSales] s
on d.DateKey=s.OrderDateKey
inner join DimSalesTerritory t
on s.SalesTerritoryKey=t.SalesTerritoryKey
group by d.CalendarYear,t.SalesTerritoryCountry)


Implementing using the lag function,

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as  CurrentYearSales,lag(cte.sales,1,0) over(partition by cte.salesterritorycountry order by Cte.calendaryear) as t   from cte


Implementing using a selfjoin approach

select cte.CalendarYear,cte.SalesTerritoryCountry,cte.sales as CurrentYearSales,Coalesce(t.sales,0) as PrevYearSales from cte
left join cte  as t on
t.CalendarYear=cte.CalendarYear-1
and  cte.SalesTerritoryCountry=t.SalesTerritoryCountry
order by SalesTerritoryCountry,calendaryear

Note:The null values which were replaced by coalsce in this query, is done by the third attribute of lag function with a value 0(lag(cte.sales,1,0))


As you can observe analytic functions makes  the code more readable,  and we can embed more complex logic dervived from this.

Sql Server 2012 Always on Availability Groups

Sql Server 2012 Always on Availability group is a new form of   High availability  which has many advantages  to the database  mirroring  and it can be  seen as an alternative solution  to the database  mirroring solution  for  high availability

Some of the important advantages of Always On Groups

• Always on availability group enables  group of data bases to fail over  when compared to single database  of mirroring
• It allows up to 4 secondary  replicas  which  can be used as an active readable database and fail over over occurs on a per replica bases. 
• Supports  automatic fail over , automatic page repair  and the fail over occurs at the instance level

Setting up Always on Availability group

Key Steps:

• Sql server Always on requires all the participating nodes to be joined to a failover cluster
• It doesn't require sql server to be installed as a clustered instance
• Use the domain accounts for the sql server service accounts of the all the participating instances as it enables to create mirroring end points of the all the participating nodes automatically 
• Always On requires the primary replica  database to be in full recovery mode and  if you got the secondary instances with different names than  you need to manually take the full backs and transaction log backups and leave it in a recovery state

Creating the Failover Cluster

Enable the fail over  future on each participating  node  from the add futures section of  server configuration dashboard as shown below

• The next step is to create fail-over cluster   which can be done by  going to fail over cluster manager from the start administrative tools  and when on the cluster manager click create new cluster as shown below

• In the next step   add the participating nodes  by browsing through AD directory as shown below

• After joining the nodes to cluster, enable always on from the sql server configuration manager and restart the sql server service as shown below

Creating the AlwaysonGroup

• Go to any one  sql server instance which you want make  as a primary replica  by going to sql server management studio  and right clicking always on management  and new availability group  as shown below

• Click next as shown below

• Click next  give a name

• In the next step select a data base  to be part of availability group , as i have already setup the always on group previously it shows  as already part of the availability group but you can select any  user database  which is in full recovery mode 

In the next step u have the option to  configure the  mode of the availability group as in mirroring you can choose high performance asynchronous mode or highly available synchronous mode,in the same section u need to add the secondary replicas ,  and also the option to configure  how  the secondary replicas to be configured you got three options read intent only ,yes, no,read intent only allows for read only connections and if you select yes it allows all connections if you set no its doesn't allow any connections  as shown below

• In the next step you can configure whether you want the databases  should be taken backup and applied automatically at the secondary replicas or whether you want to join to the already existing databases  which already have been backed up and restored manually in a recovering state i have chosen the second option to join as my secondary  instances are on different locations from the primary instance   as shown below

• Click next to perform the validations as shown below

Click next to verify the configuration choices you made as shown below and later click next to finish