Calculation dimensions in SSAS are used to store calculations that can be applied to any measure in the cube.  For example, we can define a ‘Year to Date’ calculation in a calculation dimension and it will work for any measure.  Alternatively, we could define a new ‘Year to Date’ calculated… Read more

I recently implemented an MDX expression to count the number of weekdays in a given timeframe.  This logic is likely easier to implement in SQL, but in this instance it was necessary to implement in MDX.  I thought I’d share the MDX since it may prove to be useful to… Read more

A common request from business users is ‘Time Intelligence’ functionality in an SSAS cube.  For example, users may wish to compare current year sales with the prior year or calculate sales year-to-date.  DAX includes a wide range of time intelligence functions to perform these types of calculations.  We can create… Read more

SQL Server BCP is a command line utility that bulk copies data between instances of SQL Server and data files.  Data can be imported into SQL Server tables or exported to a data file.  The BCP utility can also be used to generate format files which specify the number and… Read more

PerformancePoint provides several options to create filters.  One of the options available is ‘Named Set’.  Within SSAS cubes we can define ‘Named Sets’, which are MDX expressions that return a set of dimension members.  These ‘Named Sets’ can be used to populate filters within PerformancePoint. 

Defining a named set within… Read more

PerformancePoint provides functionality to map several time dimensions across multiple data sources to a ‘Master Time Dimension’.  Data sources that are mapped to a ‘Master Time Dimension’ can be referenced in Time Intelligence filters within PerformancePoint. 

As a first step, you will need to launch ‘Performance Point Dashboard Designer’ and… Read more

Microsoft SharePoint Server 2010 provides several options for creating Dashboards using PerformancePoint content. 

The first option is to implement the dashboard in PerformancePoint Dashboard Designer.  In PerformancePoint Dashboard Designer, you create components such as Filters, Analytics Charts, Analytics Grids, Scorecards and KPIs.  Once you’ve built the individual components, you can… Read more

A few weeks ago, I wrote a blog post that discusses implementing KPIs in SQL Server Analysis Services Multidimensional.  For those of you who are unfamiliar with the KPI functionality in the Multidimensional model, the blog post can be found here.  I’m now going to discuss the KPI functionality… Read more

Roughly a year ago, I implemented currency conversion logic in multidimensional cubes using measure expressions (as well as using the Leaves function in MDX).  I documented the implementation in a few blog posts:

• Currency Conversion Using Measure Expressions 
• Currency Conversion Using MDX Leaves Function

I have now also implemented currency… Read more

I have been working on re-creating some logic in a tabular cube that was originally implemented in multidimensional.  This involved creating some DAX measures that return the same results as the MDX calculated member counterparts in the multidimensional cube.  I thought that some individuals who are familiar with MDX in… Read more

Key Performance Indicators (KPIs) are measurements used to gauge business performance against quantifiable goals.  SQL Server Analysis Services provides functionality to define KPI calculations and associate KPIs to measure groups in a multidimensional cube.

SQL Server Analysis Services KPIs consist of the following properties:

• Value Expression: An MDX expression that…

Read more

Over the past few weeks, I’ve been watching some videos from this year’s PASS Summit.  One of the presentations I watched is ‘Querying and Optimizing DAX’ (BIA-321-S) by Alberto Ferrari.  I took some notes during this presentation and am sharing them here.

DAX as a Query Language

• DAX queries can…

Read more

Here’s my notes from the ‘Optimizing Your BI Semantic Model for Performance and Scale (BIA-402-M)’ session at PASS Summit, presented by Akshai Mirchandani and Allan Folting of Microsoft.

Tabular Design Principles

• Speed and performance
• Optimize query performance over processing performance, focus on end-user experience.
• Accommodate changes without forcing reload, if…

Read more

When cleansing data from source systems to store in a data mart or warehouse, we often need to remove or replace characters.  I recently used the Script Component in SSIS to cleanse some ticket tracking data and thought I’d write a blog post that outlines the implementation.

The data from… Read more

Today is T-SQL Tuesday #36, hosted by Chris Yates (B | T).  The topic this month is ‘What Does the SQL Community Mean to You?’  I’m fortunate to live in an area with a SQL PASS chapter (MadPASS) and have attended many chapter meetings as… Read more

I will start off by saying, whenever I see the names Marco Russo, Alberto Ferrari and Chris Webb on a SQL Server Analysis Services book….I know it will good.  This book is no exception!  I can say with 100% honesty that this is one of the best technical books I’ve… Read more

The DAX IsFiltered function returns True when <columnName> is filtered directly and false if there is no direct filter on the column.  The syntax is IsFiltered(<columnName>).  More details regarding this function can be found on MSDN.

I recently used the IsFiltered function to create a measure that is only… Read more

1. Introducing Microsoft SQL Server 2012
2. 31 Days of SSIS with SQL Server 2008 R2
3. SQL Server Interview Questions and Answers
4. Performance Tuning with SQL Server Dynamic Management Views
5. SQL Queries Joes to Pros: SQL Query Techniques For Microsoft SQL Server 2008, Volume 2
6. SQL Architecture Basics Joes 2 Pros: Core…

Read more

An error message for one of our many SQL Azure databases was brought to my attention today.

Msg 40628, Level 16, State 1, Line 1
Failed to update database ‘UAT_US_SS02′ because the database is read-only. Please contact your Azure service owner. There may be billing related issues with your Azure…

Read more

This past weekend I attended SQL Saturday #149 in Minneapolis with my husband Adam, my brother Chris and another member of our local PASS chapter.  Adam has his pilot’s license so we flew to Minneapolis the morning of the event.  It was a beautiful and warm fall day – perfect… Read more