Solved

Discrepancy in datasets derived from Dimension and Cube

Posted on 2009-07-14
8
194 Views
Last Modified: 2013-11-26
I am using Visual Studio 2008 Analysis Services and processing the AdventureWorks database to create and process a cube.   When I click on the dropdown menu for the field, I see all the fields listed, and if I choose "Show Empty Cells", I see all of the members showing up.  The peculiar thing is that every customer has at least one purchase, but many are showing up as "empty" for the given member, i.e., has no Internet Sales figure attached to that name.  These so-called "empty" members do show sales values when I independently verify the results by running a view against the table.  The members for which I do show values are almost always correct, which means that the application is selectively excluding customers from showing up as having purchases, but accurately returning calculations for the customers it finds.  

I find the entire situation quite mysterious, and your help in this matter is greatly appreciated.

Thank you, ~Peter Ferber


Dimension-Recordset.bmp
Cube-Recordset.bmp
ShowEmptyCells.bmp
0
Comment
Question by:PeterFrb
  • 5
  • 3
8 Comments
 
LVL 15

Accepted Solution

by:
rob_farley earned 500 total points
ID: 24855462
It seems to me that you may be finding records that don't have any measures in any of the measure groups - so as far as the cube is concerned, they are records that will always be empty.

If you run Profiler to see what MDX query BIDS is running, you'll probably see a NON_EMPTY in there.

Rob
0
 

Author Comment

by:PeterFrb
ID: 24856482
Many thanks, Rob.  The Profiler sounds like the tool that I need but have never used, although I did run an instance of it.  Perhaps the best thing would be a video demonstration of its use.  Does anyone know of such a thing?  

Best, ~Peter
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24856772
Hmm... try a bing search for it.

But it's quite easy really.

Run Profiler, make a new trace. Connection to Analysis Services, and look at the events. There should stuff about Queries in there. Click All Columns and quickly look to see what's in there.

Then hit Run, and see what comes up. Run your query in the Cube Browser, and see what appears in Profiler.

Then grab the queries and run them in Management Studio (connected to Analysis Services again).

Rob
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:PeterFrb
ID: 24860888
Well, this is just fascinating.  I see the MDX query that I created when I populated the crosstab.  I then ran the SQL Server Management Studio for Analysis Services and, for the first time, saw the cubes I've been developing in Visual Studio.  You've really helped me to put a number of pieces together were mysterious, and I thank you for this.  I have one final question: can you actually run a query from the Analysis Services side of Management Studio (I didn't see a way to do this), or can the query be run from the Database Engine?  
Thank you for expanding my horizons.  ~Peter
0
 

Author Closing Comment

by:PeterFrb
ID: 31603427
Thanks for the great info!  I still have a lot to learn, but you've clearly pointed in the right direction.  ~Peter
0
 

Author Comment

by:PeterFrb
ID: 24861006
I just figured out how to create an MDX query.  Well done, and thanks.
~Peter
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24865738
:) You've already answered your own question I see. Terrific!

Now go and buy MDX Solutions by George Spofford (and others), to learn how to write better MDX.

Rob
0
 

Author Comment

by:PeterFrb
ID: 24866386
Good stuff!  A friend actually loaned me that very book, and I've started reviewing it.  

Best, ~Peter Ferber
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question