SSAS: Utility to check you have the correct data types and sizes in your cube definition

Written by Dr. John Tunnicliffe. Posted in SSAS Scripts

This blog describes a tool I developed which allows you to compare the data types and data sizes found in the cube’s data source view with the data types/sizes of the corresponding dimensional attribute.

Why is this important? Well when creating named queries in a cube’s data source view, it is often necessary to use the SQL CAST or CONVERT operation to change the data type to something more appropriate for SSAS. This is particularly important when your cube is based on an Oracle data source or using custom SQL queries rather than views in the relational database. The problem with BIDS is that if you change the underlying SQL query, then the size of the data type in the dimension does not update automatically. This then causes problems during deployment whereby processing the dimension fails because the data in the relational database is wider than that allowed by the dimensional attribute.