There are many business cases where reports are needed based on schedule. The report is canned report with complicated format delivered with PDF, or excel. Also, interactive report is not needed, especially for data discovery or ad-hoc report. Microsoft BI stack provides perfect solution in this case against Tableau and Cognos:
Tableau is not good for canned report based on following reasons:
- SSRS is best suitable for canned report like the PDF mock-up, while Tableau is very good with visualization, interactive report and data discovery.
- SSRS support multiple page support, such as such as bookmark in PDF, page number, while Tableau doesn’t support, or very difficult to such a functionality.
- SSRS is free, while tableau needs $999 license fee yearly for desktop
- SSRS is mature product; there are almost no difference between SQL 2012 and SQL 2008, while tableau is new generation of BI.
Cognos can do such complicated PDF report; however, it is really an overkill solution. Cognos in this case is too complicated and too expensive.
As known, along with SQL Server, Microsoft also releases SQL Server Express editions. SQL Server Express is a free edition of SQL Server, and it’s used as the internal database. The main facts are listed below:
- SQL Server 2012 Express editions are limited to the lesser of one socket or four cores. The maximum memory for the database engine is limited to 1GB and the maximum relational database size is 10GB.
- It supports multiple databases where each database can be up to 10GB. At least two databases will be created: GBX database holds original data from machine; AAR (data mart) database holds data mart data.
- SQL Server 2012 Express edition does NOT support SSIS, however, the whole SSIS logic can be converted into SQL scripts to execute
- SQL Server 2012 Express edition does support SSRS. To make sure SSRS are available, SQL Server 2012 Express edition advanced services is needed to setup.
- It is possible to execute ETL and report directly to PDF under two conditions:
- SSRS is needed to setup (advanced services)
- Customized code is required to develop to generate report and save in a defined folder.
This article is intended to explore a most cost effective and innovative BI solution using SQL server express edition. The main advantages are:
- it will be very cost effective, as software are officially FREE;
- It is easy to deploy BI package compare with separated report server or separated PC, as communication becomes no issue because data resides in same machine.
- Especially, it is perfect for case when data used for report may not be allowed to level company site
Solution overview
The solution is to package the whole ETL and Report as Reporting Appliance to deploy it to existing server, where data source resides. There will be four major items to implement:
- Create data mart database, including staging and data mart with star schema
- Transform data to data mart for report
- Deploy SSRS definition files (.RDL) into report server
- Run SSRS report to generate PDF to a folder
All these 4 items should be packaged together as simple setup, and item 2 and 4 should be scheduled. All four items can be done with SQL 2012 express edition, meaning that all software components are FREE.
Implementation
Comparing with standard edition, SQL express version doesn’t provide SQL agent, and SSIS. Meaning that we can’t schedule job with SQL server, and we can’t use SSIS. However, we can use Window scheduler to schedule command, and we can use SQL script to somehow mimic SSIS. Two main facilities from SQL server are used.
SQLCMD
Sample code below:
set datefmt=%date%
set datefmt=%DATEFMT:~6,4%%DATEFMT:~3,2%%DATEFMT:~0,2%
set timefmt=%time%
set timefmt=%TIMEFMT:~0,2%%TIMEFMT:~3,2%%TIMEFMT:~6,2%
set timefmt=%TIMEFMT: =0%
set timestamp=%datefmt%%timefmt%
sqlcmd -E -i C:\BAR\bin\DDLAndData.sql -o C:\BAR\log\DDLAndData_%timestamp%.log
sqlcmd -E -i C:\BAR\bin\ETL.sql -o C:\BAR\log\ETL_%timestamp%.log
schtasks /create /tn "BAR" /tr c:\BAR\runBAR.bat /sc monthly /d 4 /st 12:00
(the real job is to develop sql script for DDLAndData and ETL)
RS.exe
Sample code below:
rs -i c:\BAR\bin\DeployReports.rss -s http://localhost/reportserver
rs -i c:\BAR\bin\ExportReports.rss -s http://localhost/reportserver -v FILENAME="C:\BAR\ReportOutput\Prismaflex.pdf" -v REPORTSERVER_FOLDER="/Prismaflex/Prismaflex_Main" -t -v FORMAT="PDF" -e exec2005
DeployReport.rss
Dim parentFolder As String = "FolderName"
Dim reportParentPath As String = "/FolderName"
Dim reportPath As String = "C:\BAR\Report\FolderName\"
Public Sub Main()
'Initialize the reporting services credentials
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
'Create parent folder
Try
rs.CreateFolder(parentFolder, "/", Nothing)
Console.WriteLine("Parent folder created: {0}", parentFolder)
Catch e As Exception
Console.WriteLine(e.Message)
End Try
' Pass our data source information to the create data source function
CreateDataSource("DB_BAR", "SQL", "data source=(local);initial catalog=BAR")
' Pass our report name to the deploy report function
DeployReport("FolderName_Main")
DeployReport("FolderName_Q1")
DeployReport("FolderName_Q1_DTL")
DeployReport("FolderName_Q1a")
DeployReport("FolderName_Q1a_1")
DeployReport("FolderName_Q1a_2")
DeployReport("FolderName_Q1a_3")
DeployReport("FolderName_Overview")
DeployReport("FolderName_Q2")
DeployReport("FolderName_Q3")
DeployReport("FolderName_Q4")
DeployReport("FolderName_Q5")
End Sub
Public Sub CreateDataSource(name As String, extension As String,connectionString As String)
' Define the data source definition.
Dim dataSourceDefinition As New DataSourceDefinition()
dataSourceDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
dataSourceDefinition.ConnectString = connectionString
dataSourceDefinition.Enabled = True
dataSourceDefinition.EnabledSpecified = True
dataSourceDefinition.Extension = extension
dataSourceDefinition.ImpersonateUser = False
dataSourceDefinition.ImpersonateUserSpecified = True
'Use the default prompt string.
dataSourceDefinition.Prompt = Nothing
dataSourceDefinition.WindowsCredentials = False
Try
' Create the data source through the Web service method
rs.CreateDataSource(name, reportParentPath,True, dataSourceDefinition, Nothing)
' Display success message on creation
Console.WriteLine("Data source {0} created successfully", name)
Catch e As Exception
' If the creation failed, catch execption and display the result
Console.WriteLine(e.Message)
End Try
End Sub
Public Sub DeployReport(ByVal reportName As String)
Dim definition As [Byte]() = Nothing
Dim warnings As Warning() = Nothing
Try
' Attempt to open the report as a file stream to read report definition
' information
Dim stream As FileStream = File.OpenRead(reportPath + reportName + ".rdl")
definition = New [Byte](stream.Length-1) {}
stream.Read(definition, 0, CInt(stream.Length))
stream.Close()
Try
' Attempt to deploy the report via the Web service
warnings = rs.CreateReport(reportName, reportParentPath, True, definition, Nothing)
If Not (warnings Is Nothing) Then
Dim warning As Warning
For Each warning In warnings
Console.WriteLine(warning.Message)
Next warning
Else
Console.WriteLine("Report: {0} published successfully with no warnings", reportName)
End If
Catch e As Exception
Console.WriteLine(e.Message)
End Try
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
ExportReports.rss
Public Sub Main()
TRY
DIM historyID as string = Nothing
DIM deviceInfo as string = Nothing
DIM extension as string = Nothing
DIM encoding as string
DIM mimeType as string = "application/PDF"
DIM warnings() AS Warning = Nothing
DIM streamIDs() as string = Nothing
DIM results() as Byte
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
rs.LoadReport(REPORTSERVER_FOLDER, historyID)
results = rs.Render(FORMAT, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)
DIM stream As FileStream = File.OpenWrite(FILENAME)
stream.Write(results, 0, results.Length)
stream.Close()
Catch e As IOException
Console.WriteLine(e.Message)
End Try
End Sub
No comments:
Post a Comment