SSRS - Read images from the SQL Server database

SQL Server Reporting Services has a feature to read images data from the SQL Server database. To render the images on the report, we use the special report item called Image that contains a reference to an image that is embedded in the report, stored in a database, stored on the report server, or stored elsewhere on the Web. An image can be a picture that is repeated with rows of data. An image can be used as a background for certain report items also.

What are External, Embedded, and Data-Bound Images?

On the SSRS report, we can use the following source of the Images as -

1. External Images – In this case, the image item contains a path that points to an image on the report server or wherever it exists on the Web.
2. Embedded Images – In this case, the image data is stored within the report definition and does not exist as a separate file.  They ensure that the images are always available to the report, but they cannot be shared.
3. Data-Bound Images – In this case, Data-bound images can also be displayed from binary data stored in a database. Server-based images work well for logos and static pictures that are shared among several reports or Web pages.

Image.MIME Type Property

The MIME type is derived automatically when the image is imported. Every Image must be one of the following:

• image/bmp,
• image/jpeg,
• image/x-png,
• image/png,
• image/gif,

Report Builder and SSRS - add a Data-Bound Image

A report can include a reference to an image that is stored in a database. Such an image is known as a data-bound image. The pictures that appear alongside product names in a product list are examples of data-bound images.

Steps to add a data-bound image on the report

Step A: Load Images into SQL Database

In report design view, create a table with a data source connection and a dataset with a field that contains binary image data.

USE [DEMO] GO /****** Object:  Table [dbo].[TB_Image]    Script Date: 7/27/2016 2:00:09 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO ---- Create table to store images in binary format CREATE TABLE [dbo].[TB_Image] (                 [Id] [int] IDENTITY(1,1) NOT NULL,                 [Functional Name] [varchar](50) NULL,                 [Picture] [varbinary](max) NULL,                 [MIME Type] [varchar](20) NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO

To understand the functionality, I have the following images to store into the database and display on the SSRS report from the database table.

Load images in our SQL database in Image data table as given below:

USE [DEMO] GO ----- Image Data Table INSERT INTO dbo.TB_Image([Functional Name],[Picture],[MIME Type]) SELECT 'SQL Data Warehouse' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\sdw.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'Azure SQL Data Warehouse' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\asdw.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'SQL Server Integration Services' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\ssis_hdr.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'SQL Server Reporting Services' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\ssrs_hdr.gif', SINGLE_BLOB) AS IMAGE ---Pull data from the image table SELECT  [Id]       ,[Functional Name]       ,[Picture]       ,[MIME Type]   FROM [DEMO].[dbo].[TB_Image]

Step B: Display Images on SSRS Report from database

In the report development environment, add Demo report after making a database connection to our database. Now, we have to add dataset for the report as given below:

After clicking on the OK button, our report has the dataset as given below:

Now, we have to add the table on the report. Set dataset to the table and insert Image report item in the table column as given below:

Right click on the Image icon and choose Image Properties as given below:

On the General page of the Image Properties dialog box-

• In Select the image source, select Database,
• In Use this field, set as [Picture] because this is the column is containing the binary data of the picture
• In Use this MIME type, select the MIME type from the database field. 

Render Images on the report

After doing all the above stuffs, click OK button and browse the report as given below:

So, by using the above steps, we can render the requested images on the SSRS report from the database. Users can change these images in the data table without making any change on the report.

Tutorial Demo Video - 

DW - Hybrid OLAP Servers

OLAP is a powerful analysis tool for forecasting, statistical computations, aggregations and involves more than just the multidimensional display of information. OLAP tools also must be able to extract and summarise requested data according to the needs of an end user, and there are two approaches for this data extraction that need to be discussed.

OLAP's multidimensional data model and data aggregation techniques organize and summarize large amounts of data so it can be evaluated quickly using online analysis and graphical tools. The answer to a query into historical data often leads to subsequent queries as the analyst searches for answers or explores possibilities. OLAP systems provide the speed and flexibility to support the analyst in real time.

Types of OLAP Servers

Cubes in a data warehouse are stored in three different modes and we can have four types of OLAP servers which are given below:

• Relational OLAP (ROLAP) servers
• Multidimensional OLAP (MOLAP) servers
• Hybrid OLAP (HOLAP) servers
• Specialized SQL Servers

Hybrid OLAP Servers: They are combination of ROLAP (Relational OLAP) and MOLAP (Multidimensional OLAP) which are other possible implementations of OLAP. HOLAP allows storing part of the data in a MOLAP store and another part of the data in a ROLAP store, allowing a tradeoff of the advantages of each. 
The degree of control that the cube designer has over this partitioning varies from product to product.

HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance. When detail information is needed, HOLAP can "drill through" from the cube into the underlying relational data. 
For example, a HOLAP server may allow large volumes of detail data to be stored in a relational database, while aggregations are kept in a separate MOLAP store. The Microsoft SQL Server 7.0 OLAP Services supports a hybrid OLAP server.

DW - Relational OLAP (ROLAP) Servers

OLAP is a powerful analysis tool for forecasting, statistical computations, aggregations and involves more than just the multidimensional display of information. OLAP tools also must be able to extract and summarise requested data according to the needs of an end user, and there are two approaches for this data extraction that need to be discussed.

Types of OLAP Servers

Cubes in a data warehouse are stored in three different modes and we can have four types of OLAP servers which are given below:

• Relational OLAP (ROLAP) servers
• Multidimensional OLAP (MOLAP) servers
• Hybrid OLAP (HOLAP) servers
• Specialized SQL Servers

Relational OLAP (ROLAP) Servers: These are the intermediate servers that stand in between a relational back-end server and client front-end tools. They use a relational or extended-relational DBMS to store and manage warehouse data, and OLAP middle-ware to support missing pieces. ROLAP servers include optimization for each DBMS back end, implementation of aggregation navigation logic, and additional tools and services. ROLAP technology tends to have greater scalability than MOLAP technology. 

The ROLAP storage mode causes the aggregations of the partition to be stored in indexed views in the relational database that was specified in the partition's data source.

Advantages of ROLAP

1. ROLAP is considered to be more scalable in handling large data volumes, especially models with dimensions with very high cardinality (i.e., millions of members).
2. With a variety of data loading tools available, and the ability to fine-tune the ETL code to the particular data model, load times are generally much shorter than with the automated MOLAP loads.
3. The data are stored in a standard relational database and can be accessed by any SQL reporting tool (the tool does not have to be an OLAP tool).
4. ROLAP tools are better at handling non-aggregable facts (e.g., textual descriptions). MOLAP tools tend to suffer from slow performance when querying these elements.
5. By decoupling the data storage from the multi-dimensional model, it is possible to successfully model data that would not otherwise fit into a strict dimensional model.
6. The ROLAP approach can leverage database authorization controls such as row-level security, whereby the query results are filtered depending on preset criteria applied, for example, to a given user or group of users (SQL WHERE clause).

Disadvantages of ROLAP

1. There is a consensus in the industry that ROLAP tools have slower performance than MOLAP tools. However, see the discussion below about ROLAP performance.
2. The loading of aggregate tables must be managed by custom ETL code. The ROLAP tools do not help with this task. This means additional development time and more code to support.
3. When the step of creating aggregate tables is skipped, the query performance then suffers because the larger detailed tables must be queried. This can be partially remedied by adding additional aggregate tables, however it is still not practical to create aggregate tables for all combinations of dimensions/attributes.
4. ROLAP relies on the general purpose database for querying and caching, and therefore several special techniques employed by MOLAP tools are not available (such as special hierarchical indexing). However, modern ROLAP tools take advantage of latest improvements in SQL language such as CUBE and ROLLUP operators, DB2 Cube Views, as well as other SQL OLAP extensions. These SQL improvements can mitigate the benefits of the MOLAP tools.
5. Since ROLAP tools rely on SQL for all of the computations, they are not suitable when the model is heavy on calculations which don't translate well into SQL. Examples of such models include budgeting, allocations, financial reporting and other scenarios.

DW - Multidimensional OLAP

Data warehousing depended on the performance of Meta data, OLTP and OLAP performance. In the data warehousing move toward, information is requested, processed, and merged continuously, so the information is readily available for direct querying OLAP and analysis at the warehouse. 
OLAP is a powerful analysis tool for forecasting, statistical computations, aggregations and involves more than just the multidimensional display of information. OLAP tools also must be able to extract and summarise requested data according to the needs of an end user, and there are two approaches for this data extraction that need to be discussed.

Types of OLAP Servers

Cubes in a data warehouse are stored in three different modes and we can have four types of OLAP servers which are given below:

• Relational OLAP (ROLAP) servers
• Multidimensional OLAP (MOLAP) servers
• Hybrid OLAP (HOLAP) servers
• Specialized SQL Servers

MOLAP stands for Multidimensional Online Analytical Processing which is associated with Data Warehouse. MOLAP analytic is designed to allow analysis of data through the use of a multidimensional data model which requires the pre-computation data. It's based on optimized multidimensional array storage. MOLAP systems are more optimized for fast query performance and retrieval of summarised data because they map multidimensional views directly to data cube array structures.

Many MOLAP servers adopt a 2-level storage representation to handle dense and sparse data sets: denser sub cubes are identified and stored as array structures, whereas sparse sub cubes employ compression technology for efficient storage utilisation.

Advantages of MOLAP - Multidimensional OLAP is generally thought of as the traditional multidimensional database (MDDB) where database structure optimized for storing facts categorised along many dimensions. Because all the calculations have already been performed, multidimensional OLAP offers astounding response times. 

1. Excellent Performance: A MOLAP cube is built for fast data retrieval, and is optimal for Slicing and Dicing operations.
2. Perform complex calculations:  All calculations have been pre-generated whenever the cube is created. Hence, complex calculations are not only feasible, but they return quickly.
3. Fast query performance due to optimized storage, multidimensional indexing and caching.
4. Smaller on-disk size of data compared to data stored in relational database due to compression techniques.
5. Array models provide natural indexing.
6. Effective data extraction achieved through the pre-structuring of aggregated data. 

Disadvantages of MOLAP

1. Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself.
2. The data in cube cannot be derived from a large amount of data. Indeed, this is possible.
3. Only summary-level information will be included in the cube itself.
4. Some MOLAP methodologies introduce data redundancy.

SSRS - Report Deployment from Report Manager

Report deployment comes after developing the report in the BIDS/ Report Builder/ SQL Data Tools without any issue. To visualize the report to the end users/business users, we need to deploy the report on the report server. So, there are lots of techniques to deploy the report on the report server. 

In this section, we will learn report deployment with the help of Report Manager.

How to open Report Manager?

For the first time, to open the Report Manager, we need to go programs list and choose SQL Server 2014 Reporting Services Configuration Manager from Microsoft SQL Server 2014 as given below-

It will launch SQL Server 2014 Reporting Services Configuration Manager and by default it will open with Reporting Services Configuration Connection window which facilitate you to choose Server name and Report Server Instance as given below:

Note: Please ensure that your reporting services should be started if not then you should restart it.

Click on the connect button to make a connection to your Report Server Instance and click on Web Services URL to launch your Web Services window as given below-

Now, we are able to view Report Server on the web page as given below-

In the configuration window, click on the Report Manager URL to launch the Report Manager window as given below-

Wow, we get the Report Manager on the web page as given below-

Great!! We have the report server and report manager on the web page. In the report manager web page, we should have some directories such as the Reports, DataSources and DataSets etc. Click on the new folder and create those one by one as given below-

Now, we have to set a data connection from the database. So, go in the DataSources folder and click on the new Data Source and set your database connection as given below-

After set your connection string, you should test it by using Test Connection button on the screen and if it shows successful message on the screen then click on OK button which will look like as given below-

Now, we have created the data connection. It's time to load your developed report on the report server. Go back to your Reports directory and click on the upload file as given below-

In Upload window, click on the Browse button to choose your report to deploy as given below-

After clicking on the Browse button, you can choose your SSRS report from your development environment as given below- 

After clicking on the Open button on the above window and you are able to view your report on the report manager as given below- 

Now, click on OK button to view your uploaded report in Report directory as given below- 

Choose Manage to set the connection as given below-

 Now, you are in the report manage section and there is no data source connection to the report as given below in red color-

To set the data connection, click on Browse button to set the data connection and choose your data connection from the DataSources directory as given below-

After set the Data Source to the report, you must click the Apply button to applicable the data source to the report as given below-

Now, you did everything with the report deployment process and your report is ready for the execution. You can execute your report by click on the report name as given below: 

After clicking on the report, it will execute and render the report on the web page as given below-

Now, you can execute your report from the report server and after creating the directories and deployment of the report.

Watch demo-