Discrepancy in datasets derived from Dimension and Cube

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
PeterFrbWeb development, Java scripting, Python TrainingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rob_farleyCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
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
rob_farleyCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
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
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Thanks for the great info!  I still have a lot to learn, but you've clearly pointed in the right direction.  ~Peter
0
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
I just figured out how to create an MDX query.  Well done, and thanks.
~Peter
0
rob_farleyCommented:
:) 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
PeterFrbWeb development, Java scripting, Python TrainingAuthor Commented:
Good stuff!  A friend actually loaned me that very book, and I've started reviewing it.  

Best, ~Peter Ferber
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.