Skip to main content

MDX Query Returns Duplicate Values

When someone is querying a new Analysis Services cube, there is one mistake that I often see made: trying to use a dimension that does not relate to the desired measure group. For example, when looking at the Adventure Works cube, if you try to use any of the Reseller measures with the Customer dimension, you will receive the same value across the Customer members. Because there is no relationship defined in the cube, the MDX query will use the default member for that particular attribute, which, in this case, is the ‘All’ member. It would look something like this:

This type of situation typically goes away once a user has learned the new data model, but Management Studio 2008 provides a neat dropdown list to help initially avoid the rogue query. On the Metadata tab, under the label Measure Group, you can select the measure group you are targeting. The GUI refreshes to only show the dimensions and measures that are associated with that measure group. If we select the ‘Reseller Sales’ measure group, we can see the way the new display looks, including no Customer dimension!
A user can still use the inappropriate dimension, but hopefully this will prevent the issue right from the start. Happy querying!

Version: SQL Server 2008 RTM

Labels:

Comments

Anonymous said…
This feature also exists in SS 2005.
December 5, 2008 at 4:27 AM
Jessica M. Moss said…
Hi Anonymous,

Thank you for your comment. It appears that I do not have the Measure Group drop down on my version of SSMS 2005. Do you know what version you're using or if you had to turn on a specific option to enable this? I think it would be very helpful for me and others to know!

Thank you,
Jessica
December 8, 2008 at 12:43 PM
Anonymous said…
This comment has been removed by a blog administrator.
May 21, 2009 at 12:43 AM
Post a Comment

Popular posts from this blog

SQL Server 2016 versus 2014 Business Intelligence Features

Hello, SQL Server 2016 Yesterday, Microsoft announced the release of SQL Server 2016 on June 1st of this year: https://blogs.technet.microsoft.com/dataplatforminsider/2016/05/02/get-ready-sql-server-2016-coming-on-june-1st/ .  Along with performance benchmarks and a description of the new functionality, came the announcement of editions and features for the next release. Good-bye, Business Intelligence Edition The biggest surprise to me was the removal of the Business Intelligence edition that was initially introduced in SQL Server 2012.  Truthfully, it never seemed to fit in the environments where I worked, so I guess it makes sense.  Hopefully, fewer licensing options will make it easier for people to understand their licensing and pick the edition that works best for them. Feature Comparison Overall, the business intelligence services features included with each edition for SQL Server 2016 are fairly similar to SQL Server 2014.  Nothing has been "...
12 comments
Read more

Is Data Science a Buzzword? aka: My first Coursera Course

Data science and data scientists are all the rage right now in the information technology space. Every company wants one; every job candidate touts they are one. But what actually does that mean to companies and potential employees? I decided to take a course on data science to see if I could find out! My co-worker, Gabriella Melki, recommended the Coursera Data Science specialization by John Hopkins Bloomberg School of Public Health. The entire specialization contains a set of 9 courses, but you can take each one individually. I started with the first course, called "The Data Scientist's Toolbox". Over the four week timeframe, I was able to view lectures and perform the assignments at my own pace. I've listed below my thoughts on the course and what I learned about data science. Week 1: Introduction to Data Science Data science is about data , specifically about answering questions, and science , following a method to discover an answer. A data scientist is the ...
5 comments
Read more

Manipulating Excel Spreadsheets in SSIS

Tom, an attendee at last weekend’s SQLSaturday Olympia , asked me how to refresh a spreadsheet from within SQL Server Integration Services. My first thought was to turn on the connection’s “Refresh data when opening the file” option in the spreadsheet itself and avoid the situation entirely; however, this may not always be a viable solution. Here are the steps to perform the refresh from within an SSIS package. First, ensure that Microsoft.Office.Interop.Excel is registered in the GAC. If not, install the 2007 Microsoft Office system Primary Interop Assemblies . This will need to be done on any machine where you plan on running this package. Next, create a script task in your SSIS package that contains the following code (include your spreadsheet name): Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Runtime Imports Microsoft.Office.Interop.Excel Public Class ScriptMain Public Sub Main() Dts.TaskResult = Dts.Results.Success Dim excel...
52 comments
Read more