Thomas LeBlanc

Thomas LeBlanc

Time Intelligence in Analysis Services (SSAS) Tabular Models

March 20, 2018 by

In the analytical world, time is an important slicer. The ability to view data over time helps analyze measures in actionable steps. An Analysis Service (SSAS) database can help accomplish this important step. Business users are going to want the ability to see measures a like year to date as well as the period over period comparisons. SSAS enables Time Intelligence with 2 features: the date table and DAX functions.

Read more »

How to create Intermediate Measures in Analysis Services (SSAS)

February 19, 2018 by

The whole premise of Analysis Services (SSAS) is to place business logic into a central repository (a database). This central repository should be easy to understand from the average reporting user. Simplicity for SSAS databases starts with the Dimension and Fact tables in a data mart or data warehouse. These tables need to have as little additional computed columns as necessary. With this design, adding additional Measures to the SSAS database, both Multidimensional Cubes, and Tabular Models, can help with performance monitoring of the company’s indicators.

Read more »

Managing A Slowly Changing Dimension in SQL Server Integration Services

October 10, 2017 by

A data warehouse has to be historically correct. This becomes an issue when data like the Product List Price for a previous year needs to be saved historically. Dimensional Modeling methodologies provide a solution for the situation. The Slowly Changing method integrated with components from SQL Server Integration Services solves the issue. This article will look at updating a product dimension table using the Slowly Changing Type 2 Dimension while maintaining the Type 1 columns.

Read more »

Using Many-to-Many Relationships in SQL Server Analysis Services (SSAS) 2016

September 7, 2017 by

The Multidimensional Cube option of Analysis Services has handled many-to-many relationships with ease for many versions before 2016. The Tabular had a work around using DAX formulas until the release of SQL Server 2016. There are still some limitations to many-to many in Tabular but of course, there are some “tricks” to overcome the limitations. But, the many-to-many relationship will be in businesses data for many years to come. A solution has to be provided when it comes to Analysis Service databases.

Read more »

Analysis Services (SSAS) Tabular Models – Attributes and Measures

August 14, 2017 by

In this article, a demonstration of the tabular model will try to related multidimensional cube design to the path forward Microsoft is giving used s with new versions of Analysis Services. Even though the descriptive names are changing, the tabular model still accomplishes the centralized business rules multidimensional cubes gave the business intelligence world. This is enforced by speed obtained from in-memory technology and simplification of the interface for the masses.

Read more »

Analysis Services (SSAS) Cubes – Dimension Attributes and Hierarchies

July 5, 2017 by

In Data Warehouse language, slicing and dicing is done with Dimension Attributes. Sometime a developer feels the need to provide everything to end users, whereas seasoned Business Intelligence Architects understand to provide only the attributes from the requirements. It is a hard path to follow and the seasoned architect’s experience might be the better option.

Read more »

Initial Attributes and Measures in SSAS Multidimensional Cubes

June 2, 2017 by

Connecting to tables in SQL Server Analysis Service (SSAS) databases is easy. Maybe too easy. The learning curve for developing in a Multidimensional Cube is steep, although a Tabular Model has lessened this curve. The power of encapsulating the business logic of aggregations and analytics into an Analysis Service database is too great not to embrace Analysis Services in an enterprise environment.

Read more »

Connecting to Data Source(s) with SQL Server Analysis Services

April 18, 2017 by

For someone new to SQL Server Analysis Services, a decision must be made on whether to use a Multidimensional Cube or Tabular Model. Both types can be installed on the same server, but they must be different instances. That could mean additional license costs. But, with the developer’s edition, both can be installed and reviewed before a production decision is made. Data Source connections are the first step to review with both versions.

Read more »

SQL Server Data Warehouse design best practice for Analysis Services (SSAS)

April 4, 2017 by

Before jumping into creating a cube or tabular model in Analysis Service, the database used as source data should be well structured using best practices for data modeling. Some might say use Dimensional Modeling or Inmon’s data warehouse concepts while others say go with the future, Data Vault. No matter what conceptual path is taken, the tables can be well structured with the proper data types, sizes and constraints.

Read more »

How to execute a Deployed Package from the SSIS Catalog with various options

March 21, 2017 by

In my previous two articles on SQL Server integration Services (SSIS), Parameterizing Database Connection in SSIS and Deploying Packages to SSIS Catalog (SSISDB), packages were developed, deployed and configured in the SSIS Catalog. Now, it is time to execute the packages with various options. There are a couple of ways to do this, but we need to be able to change the parameter values as well as monitor for failures or successes.

Read more »

Deploying Packages to SQL Server Integration Services Catalog (SSISDB)

January 16, 2017 by

Starting with SQL Server 2012, Integration Services (SSIS) packages can now be deployed to a single source for managing execution in multiple environments. The SSIS Catalog is a single database container for all deployed packages. The configuration files are replaced with Environments. Deployed versions are tracked historically and a package can be reverted to a previous deployment. On top of these features, internal reports with a dashboard help when debugging errors or examining performance over time.

Read more »

Parameterizing Database Connection in SQL Server Integration Services

January 12, 2017 by

SQL Server Integration Services (SSIS) has been growing into an enterprise solution over the last three releases. Variables were a step in that direction, but with Project deployments to the SSIS Catalog in SQL Server 2012 Microsoft has stepped it up, even more. Project parameters have become the go to solution for changing variable values in packages at run time. This article is going to dive into an example that parameterizes the Server Name and Initial Catalog (database name) in a connection string for packages in a project.

Read more »