Tag Archives: SSRS

3-D’s of Business Intelligence

A friend of mine aspiring to become a BI professional asked me what it takes to become a Business Intelligence professional?

Since the answer to the question is not a one liner I thought of answering it in a post as it will helps others.Before we get in detail let me define BI first,”Business Intelligence is the process of getting useful  and simpler analytics about business from its raw data sources“.

With that being said, it takes one to master 3-D’s (Data Building. Dashboards and Decision Making) to become a successful BI professional.

Data Building: Building your data from raw data sources is fundamental and crucial part of Business Intelligence. One should not use the data directly from real time systems for analytics – the reason is most of the time a company’s data systems are designed in the form of numerical values for easy computing and the numerical data doesn’t makes sense unless Attributes are added to it. Data building is like laying a foundation to your house, so extensive care and research should be taken at this step to avoid future costs.

Depending on the organization size your data building specs might change.Say If you’re a small company, I wouldn’t suggest you for a data warehousing and you can build one Master table from your Work orders or invoices and add multiple metrics to it.

Say If your company is dealing with lot of media files, I would advise you to have a non relational Hadoop kind of database like Azure.

Say if you’re running a massive Billing and Support Call center and  has to deal with tons of invoices and call volumes, a good approach is to have a datawarehouse  and track everything.You can have a combination of Type1 and Type2.

Key Tools and Technologies: SqlServer,Oracle,Teradata,Apache Hadoop,Azure

Dashboards: Once your data is built, the next step is to derive useful information from data in form of reports. By saying reports, I don’t mean long tabular ones- Why do I say that? Working with executives made me learn this trick,let me say it out for you. “Data doesn’t lie at detail and summary level“. You don’t need to have long detailed tabular reports to understand your business you can have them summarized by multiple metrics in a single dashboard. What if you don’t have one?try to combine as many reports as possible and summarize them. Dashboards allows us to view the business through different angles and gives deeper insights in a single view.

Key Tools and Technologies: Power BI, Tableau, SSRS, Business Objects and Excel

Decision Making: One may think a BI professional’s job concludes by creating dashboards. I wouldn’t agree with it, go ahead and analyse the dashboard and try making decisions yourself as if its your own company. This will embark a business mind in you, you may not become a business person overnight but with time it will increase your learning curve and establish yourself as valuable asset to your organization.

Would’t it be nice if you hand over a deck to your supervisor and say “I looked at it and our sales are dropping due to less price offers by our competitors”

Key Tools and Technologies: Your own Brain and curiosity.

Hope it helps:)

Creating a SSRS report to show SSIS package run time statistics

In most companies developers are restricted from accessing the MSDB database and they rarely know the performance of their packages in a production environment unless they have access to third party software tools or a friendly DBA. This happened to me once when I wanted to know how long my packages ran in a production environment and I had no access to the MSDB database to look at the sysjobs and sysschedules tables. The work around is to enable SQL Server logging in SSIS packages and to create a SSRS report from the sysssislog table.

The logic behind this solution is to enable SQL Server Logging in SSIS packages while we create/develop the package and send it for deployment.

Follow the rest of the article from here

Difference between Report Server, Report Server DB and Report Manager:

Report Server: Report Server defines the nature of installation of SQL Server Reporting Services in your organization. It comes in two modes namely native and share point mode. Native mode is the default and used in 80% of the industry and in SharePoint mode it is integrated with SharePoint. Report Server is made up of two processing engines and a group of extensions responsible for authentication, data processing, rendering and delivery options.

Report ServerDB:  Report Server database contains all properties, objects and metadata related to an SSRS unit. It can be accessed using SSMS. Most used tables from this database are Catalog, Execution Log Storage, Subscriptions and Users.

Report Manager: Report Manager is a web based tool built using ASP.NET application to view/access SSRS reports. It’s not available in SharePoint mode.

Commentary: I faced this question in one of my interview and confused the Report Server and Report Manager terminologies. Technically we will be deploying the .rdl file to the Report Manager and what’s the use of Report Server then? After digging into it came to know Report Server is implemented as a Windows Service and will run in the background as SQL Server Reporting Services. With regards to Report Server DB, Had an opportunity to work with it for an audit purpose and it has tons of data related to SSRS and we can generate useful insights from it.For further reading on this topic visit the MSDN site.

Hope it helps someone:)


Subversion in Visual Studio 2013 using TortoiseSVN and AnkhSVN:

I would highly recommend a developer to understand the concept of Subversion before accessing any projects stored in a repository.

Subversion is a generic term and developers uses this technology to store their source code, .proj, .soln,web pages and other project related files in a repository. Developers working in a team will be able to download the files and upload the files to the repository with any code related changes thereby providing a single point of access. Subversion belongs to Software Configuration and Change Management field. Different subversion products are available in the market like ApacheSVN, TortoiseSVN, etc. We would use TortoiseSVN for our below illustration.

TortoiseSVN is an open source subversion product developed by Collabnet. We create our repository using TortoiseSVN software. We use the AnkhSVN plugin to access the repository through Visual Studio. In other words, AnkhSVN is an add-on used to integrate Microsoft Visual Studio and Tortoise SVN.

First download and install the latest version of TortoiseSVN 1.8.7 and AnkhSVN 2.5.124751. The installation is straight forward process.

You can verify the installation of TortoiseSVN by right clicking your mouse on any Windows Explorer and you will be able to see options for SVNCheckout and TortoiseSVN in it. The AnkhSVN installation can be verified by opening the Visual Studio and click File, you should see an option for Subversion.

In most real time scenarios a repository will be already existing, so let’s jump on How to access an existing repository.

Copy a Read only Solution from Repository:

If you want to get a read only copy of a solution file,     

Step 1: Create an empty folder at your desired location. Let’s call it SQLCASTReadCopy.

Step 2: Right Click the folder, Select TortoiseSVN and Click Export

Export a Project File from Repository using TortoiseSVN

Step 3: Enter the location of the Solution/Project file in the URL Repository drop down   and the location of the above created folder SQLCASTReadCopy in the Export Directory field and click OK, you will have a copy downloaded from the repository to the above folder.

Export an SSIS Solution from Repository using TortoiseSVN

Now you can access the downloaded copy from your local machine and you will not be able to write any changes to the repository.

Copy a Read and Write Solution from Repository:

Step 1: Create an empty folder at your desired location and call it SQLCASTWriteCopy

Step 2: Open the Visual Studio 2013, Click File -> Open -> Open from Subversion

Step 3: Enter the location of the solution/project file at the repository in the From field of the dialog box and enter the location of above created local folder location SQLCASTWriteCopy from Step1 in the To field and Click OK.

Now you will have a read/write copy downloaded to the above location.

Your changes can be written to the repository by clicking COMMIT and UPDATE in the Pending Changes window of Visual Studio 2013.

In any of the above process if the dialog box prompts for your NTlogin and password, enter it and it will validate with the Active Directory.

Hope it Helps:-)


Top 5 features of SQL Server 2014:

Microsoft has officially launched its SQL SERVER 2014 this April at an event held in San Francisco, CA. Microsoft adds some new features and updates some of its existing features in every other SQL Server version. Sooner or later all our systems will be updated with this version.

So what does it means to a developer?

Is it a good sign or bad sign to a developer?

Did Microsoft made any ground breaking changes to its product?

Should I have to reinvigorate my T-SQL skills to work with 2014?

A Developer mind is bombarded with so many questions like above when we hear about a version upgrade. Don’t worry folks, I have got your back. I was excited enough like every other developer to get the 2014 version within first few weeks of its launch and done my review of it. I did learn a lot and my goal is to share some of its valuable insights a SQL Server developer must know to work with 2014. I will be posting my top 5 favorite features of SQL Server 2014 as a series over next few days. Please keep tuned.


-Junaith Haja