Execute SSRS subscriptions manually

USE [ReportServer];
GO 
SELECT
     S.ScheduleID AS SQLAgent_Job_Name
     ,SUB.Description AS Sub_Desc
     ,SUB.DeliveryExtension AS Sub_Del_Extension
     ,C.Name AS ReportName
     ,C.Path AS ReportPath
FROM ReportSchedule RS
     INNER JOIN Schedule S ON (RS.ScheduleID = S.ScheduleID)
     INNER JOIN Subscriptions SUB ON (RS.SubscriptionID = SUB.SubscriptionID)
     INNER JOIN [Catalog] C ON (RS.ReportID = C.ItemID AND SUB.Report_OID = C.ItemID)
WHERE
     C.Name IN('MyReportName'),


--copy SQLAgent_Job_Name from above query into below proc and execute

USE msdb
EXEC sp_start_job @job_name = '92C06E98-08F1-403A-879B-38D650A185FE' --MyReportName

Tags:

Wednesday, September 16th, 2015 SSRS No Comments

Accessing Member Properties on Parent-Child Hierarchies from SSRS Reorts

1) Edit MDX and add properties to DIMENSION PROPERTIES of the axis as below…..

SELECT	non empty {[Measures].[PnL],[Measures].[PL Limit]} ON COLUMNS , 
		NON EMPTY [Organization].[MyTree].members
   DIMENSION PROPERTIES PARENT_UNIQUE_NAME,[Organization].[MyTree].[My Level Name] ON ROWS  
   FROM [MYCUBE] 
   WHERE ([Date].[Date].&[20141210]) 

[Organization].[MyTree].[My Level Name] is a property of the MyTree at any given level.

2) SSRS query designer will automatically add a field (“My_Level_Name”) to the dataset, but it doesn’t show any data when we add the field to the Table (only in this case of Parent-Child Hierarchy, it works fine in other cases), so it is of no use to us, we might as well bin it.

3) When we need to access Member properties of the tree, we can use expression something like this….
=Fields!MyTree(“My Level Name”)

(Same as retrieving Cell Properties….=Fields!FieldName(“FORMAT_STRING”))

Tags: , , ,

Monday, December 15th, 2014 SSRS No Comments

Generating large numbers of SSAS partitions using Excel

Link to Chris Webb’s BI Blog page:

http://cwebbbi.wordpress.com/2008/08/27/generating-large-numbers-of-partitions-using-excel/

Tags: , ,

Friday, March 2nd, 2012 ssas No Comments

Read Object variable inside SSIS Script Component


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent

Public Overrides Sub CreateNewOutputRows()
Dim olead As New Data.OleDb.OleDbDataAdapter
Dim dt As New Data.DataTable
Dim row As System.Data.DataRow

olead.Fill(dt, Me.Variables.ObjTradeFeedData)

For Each row In dt.Rows
With Output0Buffer
.AddRow()
.TradeID = row("TradeID").ToString
.BookName = row("TradeBook").ToString
.PortfolioID = row("TradePortfolioID").ToString
.IssuerSDSID = row("UnderlyingSDS").ToString
.CreditRiskCurveId = row("CreditRiskCurveId").ToString
.Currency = row("Currency").ToString
.ValuationDate = row("ValuationDate").ToString
End With
Next
End Sub
End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments

Index fragmentation query – sql server

what is the level of fragmentation on my indexes?

SELECT TOP 50 s.NAME SchemaName
,t.name TABLENAME
,i.name AS TableIndexName
,i.[type_desc]
,p.avg_fragmentation_in_percent
,i.[fill_factor]
FROM sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,NULL) p --'DETAILED'
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
inner join sys.tables t on i.object_id = t.object_id
inner join sys.schemas s on t.schema_id = s.schema_id

WHERE p.avg_fragmentation_in_percent > 1
AND s.NAME ='mySchema'
ORDER BY avg_fragmentation_in_percent desc

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments

Increment a Variable Value in SSIS ScriptComponent


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain
Inherits UserComponent
Dim Counter As Integer
Public Sub New()
Counter = 0
End Sub
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Counter += 1
Row.NewTenorID = Counter
End Sub
Public Overrides Sub PreExecute()
MyBase.PreExecute()
Counter = Variables.MaxTenorID
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments

Delete records in a batch


WHILE 1 = 1
BEGIN
DELETE TOP(50000) [mySchema].[DimStatic] WITH (XLOCK)
WHERE [PKDimStaticID] NOT IN (SELECT [FKDimStaticID] FROM [#tt])
IF @@rowcount < 1 BREAK; END

Tags: , ,

Friday, March 2nd, 2012 SQL Server No Comments

Delete Dupes using CTE

for my own reference:
delete dupe records, keeping one entry for each

;WITH mycte (PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,myrank)
AS
(
SELECT
PKFACTMarketDataId,PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId,
RANK() OVER (PARTITION BY PKDIMBusinessDateId, PKDIMMarketDataTypeId, PKDIMDataObjectId,PKDIMCurveCurrency, PKDIMTenorId
ORDER BY PKFACTMarketDataId) AS myrank
FROM DCRMCube.FACTMarketData
)

DELETE FROM mycte
WHERE MYRANK >1

Tags: ,

Friday, March 2nd, 2012 SQL Server No Comments

Process SSAS Measure Groups (Fact tables) using SSIS

for my own reference:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain
Public Sub Main()
Dim strFactNames As String = Dts.Variables("FactNames").Value.ToString
Dim strCubeName As String = Dts.Variables("CubeName").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager

oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Fact Tables to process: ", strFactNames, "", -1, True)
Dts.Log("Fact Tables to process:" + strFactNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)
Dim oCube As Cube = oDB.Cubes.FindByName(strCubeName)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull

If oDB Is Nothing Or oCube Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else

Dim oFact As MeasureGroup

oServer.CaptureXml() = True ' Start capturing XML.
For Each oFact In oCube.MeasureGroups
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If InStr(1, strFactNames, oFact.Name.Replace(" ", "")) > 1 Then
oFact.Process(ProcessType)
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Log("Processing Facts Now", 0, bt)
Dts.Events.FireInformation(-1, "Process Facts Script: ", "Processing Facts Now", "", -1, True)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Error while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Error while processing facts:", oMessage.Description, "", -1, True)
'The processing failed
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warning while processing facts:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warning while processing facts:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 ssas, SSIS No Comments

Process SSAS Dimensions using SSIS

for my own reference:

populate DimensionNames var from a exec sql task:

Select CAST(REPLACE(STUFF(
(SELECT ',' + a.tablenamecube AS [text()]
from config.BusinessprocessTables a
WHERE TableType ='D' AND BusinessProcess = 'Metrics' for xml PATH('')),1,1,'' ),' ','') AS VARCHAR(1000)) AS DimensionNames


Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.AnalysisServices

Public Class ScriptMain
Public Sub Main()
Dim strDimensionNames As String = Dts.Variables("DimensionNames").Value.ToString
Dim bt(0) As Byte
Dim ExecutionSuccessfull As Boolean = True
Dim oConnection As ConnectionManager
oConnection = Dts.Connections("SSAS")
Dim sServer As String = CStr(oConnection.Properties("ServerName").GetValue(oConnection))
Dim sDatabaseID As String = CStr(oConnection.Properties("InitialCatalog").GetValue(oConnection))

Dts.Events.FireInformation(-1, "Dimensions to Process:", strDimensionNames, "", -1, True)
Dts.Log("Dimensions to Process:" + strDimensionNames, 0, bt)

Dim oServer As New Server
oServer.Connect(sServer) ' connect to the server and start scanning down the object hierarchy
Dim oDB As Database = oServer.Databases.FindByName(sDatabaseID)

Dim ProcessType As ProcessType
ProcessType = ProcessType.ProcessFull 'ProcessUpdate

If oDB Is Nothing Then
ExecutionSuccessfull = False
GoTo Done
Else
Dim oDim As Dimension

oServer.CaptureXml() = True ' Start capturing XML.
For Each oDim In oDB.Dimensions
' This will generate XMLA, but because CaptureXML is True, will not execute it!
If (oDim.MiningModel Is Nothing) Then
If InStr(1, strDimensionNames, oDim.Name.Replace(" ", "")) > 1 Then
oDim.Process(ProcessType)
End If
End If
Next
oServer.CaptureXml() = False ' Stop capturing XML

' Execute captured XML. First parameter Transactional, second parameter Parallel, third optional parameter: processAffected
Dim oResults As XmlaResultCollection
Dim oResult As XmlaResult

Dts.Events.FireInformation(-1, "Process Dimensions script", "Processing Dimensions ow", "", -1, True)
Dts.Log("Processing Dimensions Now", 0, bt)
oResults = oServer.ExecuteCaptureLog(True, True, True)

Dim oMessage As XmlaMessage

'Log the errors and warnings
For Each oResult In oResults
For Each oMessage In oResult.Messages
If oMessage.GetType.Name = "XmlaError" Then
Dts.Log("XMLA Errors while processing Dimensions:" + oMessage.Description, 0, bt)
'The processing failed
Dts.Events.FireInformation(-1, "XMLA Errors while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = False
Else
'It's just a warning.
Dts.Log("XMLA Warnings while processing Dimensions:" + oMessage.Description, 0, bt)
Dts.Events.FireInformation(-1, "XMLA Warnings while processing Dimensions:", oMessage.Description, "", -1, True)
ExecutionSuccessfull = True ' if you want to fail on warning, change this to False
End If
Next oMessage
Next oResult
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done

If ExecutionSuccessfull Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If
End Sub

End Class

Tags: , ,

Friday, March 2nd, 2012 SSIS No Comments