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-

Data Modeling for a Data Warehouse

A data warehouse is the biggest investment in the solution which implements the process to access heterogeneous data sources; clean, filter, and transform the data; and store the data in a structure that is easy to access, understand, and use. Data warehouse provides the base for the powerful data analysis techniques such as data mining and multidimensional analysis, as well as the more traditional query and reporting.  Data analysis programs are often required to scan vast amounts of that data, which could result in a negative impact on operational applications, which are more performance sensitive.

How will you structure the data in your data warehouse?

Data warehouse modeling is a process that produces abstract data models for one or more database components of the data warehouse. It is one part of the overall data warehouse development process, which is comprised of other major processes such as data warehouse architecture, design, and construction. 

A data warehouse modeling process to consist of all tasks related to requirements gathering, analysis, validation, and modeling. The need to model data warehouse databases in a way that differs from modeling operational databases has been promoted as dimensional data modeling, or fact/dimension modeling.

There are many types of data warehouse applications for which traditional Entity-Relationship (ER) modeling is not well suited, especially those that address the needs of a well-identified community of data analysts interested primarily in analyzing their business measures in their business context.

Why Data Modeling Is Important?

To receive benefits faster, the implementation approach of choice has become bottom up with data marts. Implementing in these small increments of small scope provides a larger return-on-investment in a short amount of time. There are three basic reasons to implement the data models such as –

1. Visualization of the business world
2. The essence of the data warehouse architecture
3. Different approaches of data modeling

Actually, data is simply a record of all business activities, resources, and results of the organization and a well- defined data model is a well-organized abstraction of that data.

Implementing data marts does not preclude the implementation of a global data warehouse.

Dimensional modeling gives us an improved capability to visualize the very abstract questions that the business end users are required to answer. Utilizing dimensional modeling, end users can easily understand and navigate the data structure and fully exploit the data.

What are Data Modeling Techniques?

Two most common data modeling techniques that are relevant in a data warehousing environment are ER modeling and dimensional modeling.

1. Entity-Relationship (ER) modeling produces a data model of the specific area of interest, using two basic concepts: entities and the relationships between those entities. Detailed ER models also contain attributes, which can be properties of either the entities or the relationships. The ER model is an abstraction tool because it can be used to understand and simplify the ambiguous data relationships in the business world and complex systems environments.
2. Dimensional modeling uses three basic concepts: measures, facts, and dimensions. Dimensional modeling is powerful in representing the requirements of the business user in the context of database tables.

Visualization of a Dimensional Model

The most popular way of visualizing a dimensional model is to draw a cube. We can represent a three-dimensional model using a cube. Usually a dimensional model consists of more than three dimensions and is referred to as a hyper-cube. However, a hyper-cube is difficult to visualize, so a cube is the more commonly used term.

ER Modeling and Dimensional Modeling

The two techniques for data modeling in a data warehouse environment sometimes look very different from each other, but they have many similarities such as -

1. Dimensional modeling can use the same notation, such as entity, relationship, attribute, and primary key.
2. Actually, a fact table is just an entity in which the primary key is a combination of foreign keys, and the foreign keys reference the dimensions. Therefore, we could say that dimensional modeling is a special form of ER modeling.
3. An ER model provides the structure and content definition of the informational needs of the corporation, which is the base for designing the data warehouse.

Conclusion

Data Modeling in the warehouse data is the process of translating requirements into a picture along with the supporting metadata that represents those requirements.   A data model cannot truly be considered complete until the remainder of the metadata is identified and documented during the design phase. The purpose of a data model is to represent a set of requirements for data in a clear and concise manner.