Monday, August 31, 2015

SSRS Report Subscription Important Points to remember.

There are two option to create report subscription in Report Manager.

1- New Subscription
2- Data Driven Subscription


New Subscription 

-Report Subscription will not work with Window Authentication.
-Report Subscription will work with Store Database Credentials.
-SQL Server Agent must be running to create Report Subscription.
-Once Report Subscription is created, a SQL Server Agent will be created to run the report on scheduled time.
-Path to share report will be \\MachineName\Users\UserName\Documents
-Once subscription is created, you can find it in MY Subscription list in Report Manager.
-You can create Subscription Schedule for Once or Monthly, Weekly, Day, Hourly basis.
-If subscription is deleted from Report Manager, SQL Server Agent job to run the report will also be deleted.
-If SMTP connection connection is not created, then you will not get option to send report through email.
-If report has parameter and parameter default value is not set then you can't create subscription. You have to define it at the subscription creation time.


Data Driven Subscription

-Report can be shared via email and windows file sahre.
-Null Delivery Provider, will be used for report caching. In this mode, report will not be rendered but will be stored in the report server temporary database.
-Steps to create Data Driven Subscription
Step 1 - Specify how recipients are notified:, Specify a data source that contains recipient information:
Step 2 - Create connection to source which holds data driven subscription information.
Step 3 - Write SQL query to return FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 4 - Specify delivery extension settings for Report Server FileShare, assign values for FILENAME, PATH, RENDER_FORMAT, WRITEMODE, FILEEXTN, USERNAME, PASSWORD
Step 5 - Specify Report Parameters default value
Step 6 - Specify when subscription will be processed

Sunday, August 30, 2015

OLTP VS OLAP Systems

OLTP stands for Online Transaction Processing, OLTP System deals with operational data. Operational Data is business transaction or activities which happen every day in the business i.e. In a banking system, you withdraw amount from your bank account, then withdrawal amount, available balance, account number, transaction number etc is operational data. In OLTP system, data is frequently inserted, updated and queried. OLTP system is most optimized to perform DML operation.


OLAP stands for Online Analytical Processing, OLTP System deals with historical data or archival data. OLAP systems are highly optimized for read operation. OLAP systems collects data over a period of time and store it in a very large database called Data Warehouse. This historical data is used for analysis purpose and to understand the trend of important KPI i.e. if we collect last 5 years of flight reservation data, then data can give us much meaningful information such as trends in reservation, peak reservation time, peak booking hours etc.