SSAS (SQL Server Analysis Services) Competitors

Above information has been received from:
link

SSIS (SQL Server Integration Services) Competitors

Do you want to know who are Microsoft SSIS 2008 competitors with their features? Following is the details:

Above information has been received from following white paper:
White Paper URL

Add your existing report as report template in SSRS

This is very intersting stuff..

My requirement is: I have one report and I want to add this report as report template in list of SSRS templates, so that I neither need to rewrite my report nor need to add sub reports.

Answer is: Just create your standard template report and Once your are done with your report, just copy and paste your standard report at following location:

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

You are all set.. You will see your report as standard template in BIDS(Business Intelligence Development Studio)

How to develop Standard and Data driven subscription?

Creating standard subscription is fairly simple

• Go to report manager
• Open report properties
• Select subscription
• Create new subscription
• Select your delivery method (Windows Share/Email/Sharepoint library)
• Define required parameters
• Define schedule (Custom/Shared)
• Click OK.

You are all set!!!

Creating Data driven subscription is somewhat tricky,

• First of define one table in your database for your subscription paramter (e.g. tblSubParameter), like

- Email To

- Subject

- Render Format (PDF/Excel etc)

- Include report link etc...

• Once you are ready with your report table, go to report manager
• Select report and open report properties
• Go to Subscription
• Create a new data driven subscription
• Give some name for subscription
• Select datasource (Shared/Custom) where your subscription parameter table exists.
• Click Next
• Write the query to fetch the parameters (E.g. select * from tblSubParameter)
• Validate your query
• On successful validation, click Next
• In place of giving static values for each parameter like, Email to, render format. etc... ; Select parameter which is coming from database
• Select all required database parameters, click next
• Select report level parameters, click next
• Select schedule, click FINISH.

You are all set!! now at scheduled time, subscription event will fire and it will bring subscription parameter information from database and based on your table information, report server will send the report subscription...

Different Delivery Extensions for Subscription in SSRS

Following are the different type of delivery extension for SSRS report subscription(Scheduling)

Avaliable with SSRS Native and Sharepoint Integrated mode:

• Windows file share: Delivers report to predefined share location. The share location name should be in UNC(Universal Naming Convention)  format.
• Email: Delivers report as an email attachment or URL link

Avaliable only with Sharepoint Integrated Mode:

• Sharepoint library:Delivers report as an static application file to sharepoint library.

Subscription in SSRS

A subscription that delivers a specific report to one or more e-mail accounts or to a designated network file share on a scheduled basis.

There are two types of subscription in SSRS.

• Standard Subscription: Standard subscriptions are created and managed by individual users. A standard subscription consists of static values that cannot be varied during subscription processing. For each standard subscription, there is exactly one set of report presentation options, delivery options, and report parameters.

• Data Driven Subscription:Data-driven subscriptions get subscription information at run time by querying an external data source that provides values used to specify a recipient, report parameters, or application format. You might use data-driven subscriptions if you have a very large recipient list or if you want to vary report output for each recipient.

It is better to go for data driven subscription, when you have

• Large no of recipients   OR
• Different recipient has different file format requirement OR
  
• Each recipient need reports execution based on different parameters

So decide what is the best for your enterprise, and implement it!! keep learning!

View SSRS Report in Sharepoint Web Part

In order to view SSRS report in Sharepoint Web part follow the steps:

1. Complete all the steps as per last post- for integrating SSRS with MOSS 2007

2. On successful completion of integration, 

• Go to your Sharepoint site settings.
  
• Under Library -> click on Report Library
• Upload your SSRS report rdl file under this report library folder
• Once report is uploaded, right click report and click on Data sources
• Update datasources with proper credential information.
• On completing this stuff, your SSRS report will be ready to deploy on sharepoint site.

3. Now it is time to view report on sharepoint site.

• Open sharepoint site
• Edit your page
• Click on add new web part
• Add a web part called, SQL Server Reporting Services Report Viewer
• Open Report viewer web part properties
• Select Report location using report browser. (Note: Your reports will be under your report library)

You are all set!! Sharepoint web part will show the report under sharepoint context.  

SSRS 2008 integration with sharepoint

In Simple, if you want to integrate SSRS 2008 with Sharepoint, follow the steps:

1. Download and install Reporting services Addin (http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=200fd7b5-db7c-4b8c-a7dc-5efee6e19005) to sharepoint Web Front End server (WFE) in simple on machine where sharepoint is installed

2. Activate a site collection-scoped feature called Report Server Integration Feature on the Central Administration site.
This feature has two different behaviors when gets activated on Central administration site than other sites. When activated on the Central administration site , the feature does all of the things it does for other type of sites , plus it adds a section called Reporting Services under the Application Management. This section must be used to make sure SharePoint is aware of my SSRS instance existence

3.Configure Reporting Services
- Go to Central administration site -> application management
There are three options in this section:

• Grant Database Access: First you need to specify the server which hosts reporting services database, whether it is on a default or named instances. Essentially what happens here is that the Report Server endpoint and Windows service accounts for that instance (named or default) will be granted required access to the SharePoint databases. During this process, the Report Server service will be restarted. This is an essential step in integration.
• Manage integration settings : You need to specify Report server URL and the authentication. Pretty straightforward.
• Set Server Defaults :You set all of your basic defaults. This page contains all of the things you’d normally use Reporting Services Configuration tool to configure them, but they are now managed via SharePoint tier. For example making sure that all data sources use integrated security, so on and so forth. Ad-hoc reporting is also a powerful feature which can be set and controlled from here.

Once configuration completes.. then comes adding of content types..

4. Once you activate the feature , the following things will be added to your site collection:

I.Required content types:

• Report builder model
• Report builder report
• Report builder Data Source

II.Report viewer web part in Web part gallery

III. A section in the Site Settings for managing shared schedules

Obviously if you want to be able to store your reports in a Report Library and your data sources in Data Connection Library , you need to enable another web-scoped “Office SharePoint Server Enterprise Site features” feature to get Report Library and Data Connection Library in the create page. This has nothing to do with SSRS Add-in though !

That’s all about it! I hope this blog post can help you verify your SSRS 2008 installation in integration mode.

SSRS 2008 doesnot require IIS

It sometimes seems impossible to keep everyone happy all the time. The Reporting Services predict team thought it would be a good idea to remove the dependency on IIS in SQL Server 2008, to make it easier to configure and to reduce the attack surface of the report server.

However, there have been some concerns raised about this, e.g. is it secure, how do I know how it's configured etc.

SQL Server 2008 uses http.sys to allow you to have total control of the of the URL's to be used. (http://msdn2.microsoft.com/en-us/library/aa364698.aspx).
http.sys respect the same security registry settings as IIS6/7 (http://support.microsoft.com/kb/820129) .
The authentication supported is a subset of what's available in IIS6/7 - except that anonymous authentication and authentication filters are not supported.
ASP .Net security is essentially the same.

Reporting Services 2008 Visualizations

I have been doing alot of research as of late in the Microsoft SQL Server Reporting Services (SSRS) 2008 visualizations. With the arrival of SSRS 2008 users will now gain out-of-the-box access to the Dundas visualizations platform. If your curious, the initial Dundas press release can be found here. SSRS 2008 (as of the February 2008 CTP - [Community Technology Preview] build) contains both Dundas Gauge and Dundas Chart products. In addition, according to the prior mentioned Dundas press release it states that Dundas Calendar will also be included into SSRS 2008.
This fairly recent Microsoft purchase of the Dundas source code for integration into SSRS 2008 is a great move as the Dundas suite of SSRS add-ons have become the premier choice for such advanced visualization needs. By including the Dundas technologies into SSRS 2008, Reporting Services customers will not only gain access to a much improved Report Server Architecture (without requiring IIS) but also an enhanced Visualization platform. There are literally about three times as many chart types in SSRS 2008 as compared to SSRS 2005. Some of the brand new chart types include the Funnel, Range, Pyramid, and Polar. In addition to the added chart types customers will also gain access to the Dundas Gauge capabilities via a new Gauge Data Region. As of the latest SQL Server 2008 CTP there are two main gauge types: Radial and Linear. Finally, we get a few other ‘goodies’ with the inclusion of the Dundas suite including:
Secondary Axes
Runtime Calculated Series
WYSIWYG Chart Editor (design-time)

Report subscription in SSRS 2008

In SSRS 2008, Report can be subscribed and sent to and UNC file share and also by email. By default only UNC file share location will be availble.

In order to subscribe mail delivery, follow the steps:

1. Go to report configuration manager
2. Click on Email settings
3. Provide from address and smtp server name
4. Save the changes.
5. Go to you report manager
6. open your report and go to subscription section
7. Create new subscription and provide the mail and schedule details.

You are all set!!!

Launching report builder from command prompt @SSRS

Many of you may question, whether we can launch report builder from command line. While it is in fact possible, it is an unsupported feature in SQL Server 2005. That said, here's how to do it. You just need to specify what report server to connect to, like this:
ReportBuilder.exe /s=http://mybox/reportserver
Other supported URL parameters are also recognized when passed on the command line, like this:
ReportBuilder.exe /s=http://localhost/reportserver "/My Favorite Report"
ReportBuilder.exe /s=http://localhost/reportserver "/model=/Models/Adventure Works"

Working with Transaction Cotainers for Jet Oledb provider @SSIS

Issue: If you try to user transaction container, and if your data flow task contains Jet oledb provider then transactions will not work.

Solution: User Excel or other source in place of Jet oledb provider.

Decimal points issue while working with Excel Source @SSIS

Issue: In SSIS if you use Excel source in Data flow and if you have column in your excel file with decimal points then sometime during SSIS import fetched decimal values from excel are different from actual decimal values.

E.g In Excel, Amount column has 19.31 then while doing SSIS import, it will fetch 19.30000009.

Solution:

-Try to use Jet Oledb provider in place of Excel source

- Also provide EXTEND PROPERTIES value as "EXCEL 8.0;IMEX=1" in Jet Oledb provider.

Which are the Business Intelligence Softwares?

Microsoft business intelligence tools are well up to market requirements and they are :

• SQL Server Reporting Services (SSRS)
• SQL Server Analysis Services (SSAS)
• SQL Server Integration Services (SSIS)

Why do we need BI tools?

• Facts of survey done by IDC (a sister company to CIO's publisher).

• According to the survey:
• The amount of data is growing rapidly. 
• Accessing unstructured data is difficult but increasingly necessary to decision making
• To manage the avalanches of data , business intelligence tools are becoming more widespread.
• Timely business intelligence has become mission critical to many enterprises

Solutions for all of the above listed problems is Business Intelligence tools.

What is Business Intelligence?

• Business intelligence (BI) refers to skills, knowledge, technologies, applications, quality, risks, security issues and practices used to help a business to acquire a better understanding of market behavior and commercial context.

• Business Intelligence (BI) is about getting the right information, to the right decision makers, at the right time.

• Simple words.. Convert Data into Information
• Data: Collection of facts collected as a result of some activity
• Information: Data that are processed to be useful. 
• and the tools that helps you to achieve that is called Business Intelligence Tools.