Building a report based on an Analysis Services cube using Reporting Services 2014

No Comments

Written by Luis Miguel – Originally Published on Jan. 25, 2015

I built a cube based on the data in my fictitious auto sales data mart. The cube is structured as a star schema, with a fact table called fact_sales, and 3 dimensions, dim_buyer, dim_model, and dim_location. I want to build a report that shows the total sales by make per region. Make is an attribute of dim_model and Region is an attribute of dim_location. For this report I will not use any attributes of dim_buyer. If I needed to see sales data by Gender or Income Level, I would use those attributes of dim_buyer in my SSRS project.

The automobile sales cube

Step-by-Step Instructions

Open SQL Server Data Tools for Visual Studio 2013 and add a new project, then add a new report to the project.

A new Reporting Services project with a new report

Add a Microsoft SQL Server Analysis Services data source to the report, using the Edit button of the new data source wizard select the server and cube where the source data resides.

new data source

Add a Dataset to the report; it will be based on the Data Source you added in the previous step. Click Query Designer and drag the measure and dimension attributes into the large empty area at the bottom right of the dataset designer. In this case I’m using the measure “Fact Sales Count” and the Make and Region dimension attributes.

Setting up a new dataset

The new dataset query deisgner

Add a table to the body of the report

A new table

Drag the fields from the dataset into the Data area of the table

Adding fields from the dataset to the new table

Apply grouping by Make to the table

Add group - Parent group

Group by Make

When you apply grouping to a tablix column, a new (grouped) column is added but the existing column is not removed. The report now has two Make columns, one with grouping applied to it and another one without grouping. Remove the extra column left in the table without grouping.

Deleting the extra column left after the parent group was added

Format the column names, rename Fact Sales Count to Total Sales, make the font bold and centered

Improving the apperance of the report

Click the Preview tab to see how the report looks so far

Previewing the report

Sort the data by Total Sales so that the Regions are ordered by sales with the largest quantities on top

Open the Tablix properties

Add sorting by fact_sales_count

Set the Target Server URL to your project

From the menu select: Project - Properties

Deploy the project

From the menu select: Build - Deploy

View the report

Fireup a web browser and enjoy the report

Dynamic Flat File Source – SQL Server Integration Services 2012

No Comments

Written by Luis Miguel – Originally Published on Jan. 13, 2015

Requiremts: Import/load data from a CSV file into SQL Server on a daily basis, the CSV file has a file name pattern that consists of the name of the target table and a date stamp with the format of yyyymmdd, for example “product_20150111.csv”.

The CSV file


Step by step instructions

1 – Create an OLE DB connection to the destination table

OLE DB connection

2 – Add a string variable that will hold Flat File Source name

String variable to build the dynamic file name

3 – Create a Flat File Connection to the source CSV file

Flat file connection

3a – Select the Flat File Connection to retrieve its properties
3b – Click the ellipses button next to Expressions
3c – Set the property Connection String to the expression @[User::product_yyyymmdd]

Set the Connection String property to an expression

4 – Add an Execute SQL Task that will build the Flat File Source name

4a – Set its properties as follows
4ai – General – SQL Statement: SELECT ‘c:\data\product_’ + CONVERT(VARCHAR(10), GETDATE(), 112) + ‘.csv’
4aii – General – Result Set – Single Row
4aiii – Result Set – Result Name: 0 – Variable name: User::product_yyyymmdd

Execute SQL task

5 – Add a Data Flow Task

Add a Data Flow task

5a – Add a Flat File Source to the data flow task and point its connection manager to the flat file source created previously

Flat File source

6 – Add an OLEDB Destination

OLE DB destination

The Data Flow task

The Data Flow task

A successful execution

Package execution result

The table after the data from the CSV file was imported

SELECT * FROM products

Powered by Netfirms