In SQL Server Reporting Services (SSRS), there are several default types available that can be used as a data source. In this article I want to show a list and a little explanation of the individual options.
| Data Source Type | Description |
|---|---|
| Microsoft SQL Azure | SQL Azure is a cloud-based service, where your database(s) resides on a Microsoft-hosted, scalable solution. SQL Azure allows a user to create T-SQL queries, searches, analysis or synchronization against data residing in the cloud. SQL Azure is a perfect soltuion in case you either do not have the means to invest in your own hardware, if data access volumes can fluctuate greatly (and thus there might be a need to quickly scale up (in case of large amounts of traffic) or down (to save money when the period of high activity calms down). |
| Microsoft SQL Server Parallel Data Warehouse | Parallel Data Warehousing is a combined effort where Microsoft acts as the Software vendor, and either HP or Dell as the hardware supplier. It consists of powerful, secifically constructed machines that allow corporations to deploy scalable and highly performing enterprise-level data warehouses of many terabytes. |
| OLE DB | OLE (Object Linked Embedding) DB is a Microsoft-desgined set of APIs to accessing relational data from a number of sources in an uniform manner. Ole DB is intended to be scaled out, though, so while it provides you with a way to connect to other data sources, keep in mind this will not be an option in the near future anymore. SQL Server 2012 will most likely be the last version of SQL Server to support OLE DB. |
| Microsoft SQL Server Analysis Services (SSAS) | SSAS allows you to transform transactional data into data cubes. A cube basically is relational data that is restructured into Dimensions (descriptive data) and Facts (numbers that can be attributed to the dimensions, and aggregated), the combinations of which will be pre-recomputed thus allowwing end-users to analyze data from different perspectives. It allows for data comparisons that are extremely difficult to accomplish using traditional databases (YTD calculations, or comparing quarters of several consecutive years, which typically require you to perform these calculations upfront, and which will not allow you to then drill down and find explanations for differences. Cubes can provide you with such answers). |
| Oracle | Oracle, like SQL Server, is a relational database, and a direct competitor of Microsoft SQL Server. SSRS allows you to report on Oracle as well, in case your business uses Oracle as a data storage, but you would still like to tap into the powers offered by SSRS. |
| ODBC | ODBC is short for Open DataBase Connectivity, is the predecessor of OLE DB (discussed above), and an older method to access different data sources. It offers less functionality than OLE DB, and because of that it often performs faster. than OLE DB |
| XML | XML (Extensible Markup Language) is a way to represent hierarchical data in text-format, using markup to define entities. This means that XML files can be an extremely efficient way to send data across networks (or the Internet). There are numerous ways to incorporate data validation (to ensure the data meets specific requirements or standards), and XMLis a widely used and accepted way to transform data into documents that are readable by both humans and machines. The reports generated using SSRS are actually little more than large XML files that are interpreted by SSRS to figure out what data to grab from which location and how and where to present the data. Using this you can also report on data from web services residing on the Internet. |
| Report Server Model | Report Server Model allows you to access pre-defined report model data sources from SSRS. These will be set up in SSRS advance, and can then be used to build reports on. This can be useful in case an organization wants to offer a default data set (or sub set ), and then allow the users to build reports on these models. Users can then leave out the fields not relevant to them. |
| Microsoft SharePoint List | Microsoft SharePoint List allows you to build reports based on lists of data created in SharePoint. |
| SAP NetWeaver BI | NetWeaver BI is SAP’s interpretation of a Data Warehousing implementation. SSRS can still act as the report provider for this platform. |
| Hyperion Essbase | Hyperion Essbase is yet another business intelligence product. It offers similar functionality as Analysis Services, and again, can act as a data provider for SSRS. Hyperion is sold by Oracle. |
| Teradata | Teradata also offers an Business Intelligence platform, and SSRS can report on that, too. |