Tag Archives: dimension

Quick Guide to BIDS Helper 1.4.3.0 for SSAS

BIDS Helper is an amazing free add-in for Business Intelligence Development Studio (BIDS).  You can obtain the add-in from the Codeplex website.  The purpose of this posting is to provide a quick guide to version 1.4.3.0 Analysis Services (SSAS) functions.  The add-in also has other functions, but this post only lists the context-sensitive SSAS features. 

I believe this organization structure will encourage people to explore this product.  Setup is through a regular setup menu, but the actual features visually appear in context-senstive areas throughout BIDS:

  • BIDS Configuration — Tips for setup
  • BIDS Helper — Features by Hierarchy:  Keyed to the BIDS environment for SSAS, with links to the BIDS Helper website
  • BIDS Helper — Features by Hierarchy (Visual):  What to look for on the screen

BIDS Configuration

After installation, BIDS helper adds itself to many of the BIDS menus.  Configuration is through the tools menu.

bh01 Quick Guide to BIDS Helper 1.4.3.0 for SSAS

The menu defaults to the “Features” pane, Continue reading “Quick Guide to BIDS Helper 1.4.3.0 for SSAS” »

Securing Dimension Data

51s1nMjautL. SL160 Securing Dimension DataMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 36

The book’s diagrams on pages 733 and 734 illustrate what dimension and cell security do:

  • Dimension security removes details and removes amounts from aggregations
  • Cell security removes details, but does NOT remove amounts from aggregations

As I commented in the last chapter review, the Analysis Services access security may not behave as many people believe, and therefore it may make sense to make another cube which has only the appropriate information for larger groups.  Continue reading “Securing Dimension Data” »

Aggregation Design and Usage-Based Optimization

510cHjGCsUL. SL160 Aggregation Design and Usage Based OptimizationMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 23

This chapter discusses managing and optimizing the aggregations. Again, the “cube” in SSAS is comprised of partitions within which are aggregations. The chapter starts with another discussion of dimensions. A relational reporting-style dimension (pages 420-422) is one which does not have a natural hierarchy, and many such dimensions would cause more calculations. As I mentioned in my earlier discussions of dimensions and SSAS, these dimensions typically have a relationship among them, and a hierarchy is one type of relationship. The name relational reporting-style implies that the dimension comes directly from a relational source. Perhaps a better name is nonhierarchical dimension since the source may or may not be relational.

The next few screen shots use the Visualize Attribute Lattice option from BIDS Helper (free add-in from codeplex.com). To perform the visualization, you right click the dimension, and choose the option on the menu. First, a hierarchical dimension, Product, and then a relational reporting-style dimension (I suggest nonhierarchical dimension), Department.

AS2008 2301 Aggregation Design and Usage Based Optimization

AS2008 2302 Aggregation Design and Usage Based Optimization

Continue reading “Aggregation Design and Usage-Based Optimization” »

Using SQL Server Integration Services to Load Data

510cHjGCsUL. SL160 Using SQL Server Integration Services to Load DataMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 22

This chapter discusses leveraging SQL Server Integration Services to load data into a cube. If you have Analysis Services you also have Integration Services, no additional licensing required. However, you may have to install this software.

As I covered in the previous chapter, Analysis Services has some native abilities and default choices through its own default processing technology. This default technology works best with standard relational sources, simplex schemas, and SQL Server in particular. In general, anyone could use SSIS as a good alternative when departing from these simpler conditions, and also to leverage additional SSIS ETL capabilities as part of the loading process. Loading is made possible by two SSIS data flow destinations, one for dimensions and one for partitions:

AS2008 2201 Using SQL Server Integration Services to Load Data

Again, there is no cube destination, since data are stored in partitions.

Continue reading “Using SQL Server Integration Services to Load Data” »

Dimension and Partition Processing

510cHjGCsUL. SL160 Dimension and Partition ProcessingMicrosoft SQL Server 2008 Analysis Services Unleashed Review Chapter 21

As this chapter claims at the outset, SSAS only stores information in two locations: dimension files and partition files. Chapter 20 had discussed the static description of these files, and this chapter now discusses the dynamic construction of these files.

A note on page 382 made me think about the processing sequence. We have way to force Analysis Services to issue a single SQL query to process all the dimension attributes. Continue reading “Dimension and Partition Processing” »

Dimension-based MDX Calculations

510cHjGCsUL. SL160 Dimension based MDX CalculationsMicrosoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 13

In the previous chapter, the book outlines a highest pass wins rule for determining the order of cube calculations. This chapter introduces dimension-based calculations, which will override a cube calculation. The chapter does not start with this argument (it’s on page 233) but I believe this fact is a good segue in the flow of the book.

The normative (default) role of aggregations is summation. Analysis Services has a series of unary operators, mathematical symbols or measure names which indicate how to perform rollup calculations. The default unary symbol is the addition sign + but other symbols are possible.

Continue reading “Dimension-based MDX Calculations” »

Measures and Multidimensional Analysis

510cHjGCsUL. SL160 Measures and Multidimensional AnalysisMicrosoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 7

This chapter, from some viewpoints, covers the heart of what people want from Analysis Services: measures. As the chapter mentions, the measure is the simplest dimension with only one attribute, Measure. This attribute, by design, is not aggregatable (a property typical of other types of dimensions). In statistical terms, a measure is the dependent variable, and the attributes within other types of dimensions are the independent variables.

Within Analysis Services, measures have certain properties which are optimized for calculation performance:
Continue reading “Measures and Multidimensional Analysis” »

Cubes and Multidimensional Analysis

510cHjGCsUL. SL160 Cubes and Multidimensional AnalysisMicrosoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 6

As I mentioned in the earlier discussion of multidimensional space, the terms used in Analysis Services are common to the industry and do not map to spacial dimensions as a physicist might expect. Specifically, the goal of Analysis Services is to limit the spacial features to a predefined subset of all possible combinations, even for the data loaded into a cube. A geometric cube mimics the universe in having theoretically infinite space, but the data in an Analysis Services cube has predefined boundaries, starting and ending points, specific attributes within dimensions, and measures. Designing a cube essentially reflects business intelligence mapped onto infinite space. I do not believe this mapping happens automatically but that the intelligent structure needs to be designed based on current and past information, and best estimates of the future.

As with chapter five, this chapter also documents sample DDL (data definition language) for a cube. These examples extend what is available in the free SQL Server Books Online. Important to this chapter is not just the cube, but also the perspective, a subset of the cube. The statement on page 72 is important for perspectives, that they do NOT act a security device to prevent access to other parts of a cube. Instead, the perspectives have a simpler task, namely to reduce the visual combination clutter presented to users through applications.

SQL Server Books Online has a good overall discussion of Planning and Architecture. This specific chapter focuses on the heart of the technology, the linkage between cubes and dimensions. As mentioned earlier, these attributes will help form the tuples to help determine a unique coordinate within the cube. Again, this book covers the architecture of Analysis Services, so if you were expecting a book which has to-do exercises, you might be disappointed. I, however, value the builder documentation which complements other literature on how to accomplish projects. This book not only describes the Analysis Services technology but also includes key terms and performance tips on making the technology work well in production.

Gorbach, I., Berger, A., & Melomed, E. (2009). Microsoft SQL Server 2008 Analysis Services Unleashed. Indianapolis, IN: Pearson Education Inc.
ISBN: 0-672-33001-6

Dimensions in the Conceptual Model

510cHjGCsUL. SL160 Dimensions in the Conceptual ModelMicrosoft SQL Server 2008 Analysis Services Unleashed Book Review Chapter 5

Chapter five starts with a table which maps domains (a limited list of values) from relational technology to multidimensional analysis technology. I have not used the word domain in my experience, probably because I come from a statistical and developer background, and not the DBA theory background. The word domain should mean something to a DBA.

More specifically, the discussion on page 44 claims that “implementations of relational databases essentially ignore this definition”. I am not sure all of what this claim means, but my mind quickly goes to a challenge in all relational transactional systems, namely how to define what a domain is especially when the core database methodology is built on keeping all transactions. Microsoft has acquired new technology for managing domains, called Master Data Services (I will not be commenting on what this technology does since it is beyond scope of reviewing Analysis Services). I will say now that I can imagine some complicated future design linkages between Master Data Services and Analysis Services when SQL Server is the data source.

Continue reading “Dimensions in the Conceptual Model” »