Dimwit MDX count problem

I have run into a small problem. I have a dimension that identifies certain types of bills. I also have a measure that reports how many bills have been issued. There are several types of bills. But 1 of these is an adjustment bill. This is like a correction to an existing bill. These are represented as "A" bills in the DB

I need to count the number of unique bills, so that's a count of all the bills other than "A" bills.

I've looked at calculated members but am struggling with MDX syntax.

Anyone have any pointers or good articles I should read?

It should be something like this:`

with member [Measures].[Num]

as 'sum(except([Bills].[Level Name].members, [Bills].[ A ]), [Measures].[NumBills])'

Hope this helps,

Santi

Dimenstion on measure

There is count measure in the cube. I need to create a dimension that should list the range of values based on that count values:
Exp:

0-99
100-199
200-399
...
...
..
and so on.
User wants to pick the any range level in the dimension and cube should show corresponding cell values of count measure which would fall into range of the dimension level value.
For example:
if user picks up level 100-199 then cube would filter cell values of measure that has a value between 100 and 199 within 12 month range. So, it would be year to date value of the measure. My problem is how go about creating Dimension that based on measure in the cube and dimension should have level with range of measure values.
Any hint would be appreciated greatly.
Thank You.you need your Range lookup table

Range_lkp
---
Range_ID (PK)
Range_Desc

containing values

1 0-99
2 100-199
3 200-399

in your Fact table data would look like:

... Measure ..... RangeID (FK)
----------
... 104.56 ..... 1
... 95.47 ..... 3
... 345.77 ..... 3

the point is you have to populate your RangeID's to your Fact table during ETL

1) populate table with RangeID = NULL
2) update table set RangeID = ... use procedure (I don't think you'll handle it by one update statement)|||Well, it would be best solution. But, in my case wont work.
Measure in fact table will be aggregated over 12 month period in the Cube.
So, in your example Range Id = 1 of 104.56
Might be in the cube something like that:

313.68
Sum(
104.56
104.56
104. 56
)

So, 313.68 is no longer Range id 1

Point here is over period of time. Not fact value of that measure and that most of the cases will be aggregations of those facts (104.56).|||OK I know what you mean...

Maybe you could create another few snapshot tables. Weekly snapshot, Monthly snapshot, Quarter snapshot, Yearly snapshot. Set up Range ID's in those tables and use them for reporting. Another option could be solve this somehow on reports level. It's hard to say how, it depends on your business intelligence tool. BTW what tool do you use? Or how you report your data?|||We use MS Analysis.
Doing something like that in report is easy. But, users want it on the cube. I believe it should be done dynamically with MDX. I thought maybe somebody else already done it then I dont have to invent the wheel.
Thanks for response anyway.

Dimension Range

I have a simple cube based on just 2 DB tables. I have 1 measure and 5 Dimensions. I need to create some ranges out of 2 numeric Dimensions. Can anyone suggest how to go about it. It may be a simple thing- but I am just a learner...I have tried to use DiscretizationMethod but my ranges are very specific so it does not help.

What are the numeric dimensions, and what are the ranges you want to create on the numeric dimensions? Your answers will point us in the direction of a solution. An example would be very helpful.

PGoldy

|||

The Ranges I want to create for the numeric Dimensions are something like this:

< 0, 0-300, 300-500, 500-520, 520-540, 540-560,....640-660, 660-680, 680-700, 700-900, >900

And for another Dimension:

<0, 0-70, 70-80, 80-90, 90-100, 100-125, > 125.

Thanks.

|||

Have you tried the TSQL CASE in the data source view.

Regards

Thomas Ivarsson

|||

Hi.

Thomas has a good suggestion (thanks Thomas). I'll give you a little more detail.

In the Data Source View (DSV) within BI Studio you can create a "named query". The named query allows you to define any valid SQL statement. Think of it like using a view in the SQL db, but it's part of the project in BI Studio so you're not bothered with maintaining a view in the SQL db. To create a named query open the DSV in Bi Studio. Right click in the layout pane and select New Named Query. The Create Named Query dialog lets you pick tables, columns, or enter your own SQL. Your choice. I recommend you enter your own SQL with a CASE statement which defines the dimension buckets you want. As an example (this code works on Adventure Works DW):

SELECT
CASE
WHEN SalesAmountQuota > 0 AND SalesAmountQuota < 5000 THEN '0-4999'
WHEN SalesAmountQuota >= 5000 AND SalesAmountQuota < 10000 THEN '5000-9,999'
WHEN SalesAmountQuota >= 10000 AND SalesAmountQuota < 20000 THEN '10000-10,999'
ELSE '20000+'
END AS SalesAmountBucket
FROM FactSalesQuota

The above statement generates the "keys" into your buckets for each record in the fact table. Don't forget to include the rest of your fact table columns in the SELECT and you get a new fact table as a named query.

The new column you've generated in the named query also serves as the attribute hierarchy for your range.

Good Luck.

PGoldy

|||

Thanks Paul for guiding with more details than in my Zen approach.

That is a good explanation of CASE.

Kind regards

Thomas Ivarsson

|||Thanks a TON to PGoldy and Thomas. Yeah, it was really that simple...can't believe it.

Dimension Range

I have a simple cube based on just 2 DB tables. I have 1 measure and 5 Dimensions. I need to create some ranges out of 2 numeric Dimensions. Can anyone suggest how to go about it. It may be a simple thing- but I am just a learner...I have tried to use DiscretizationMethod but my ranges are very specific so it does not help.

What are the numeric dimensions, and what are the ranges you want to create on the numeric dimensions? Your answers will point us in the direction of a solution. An example would be very helpful.

PGoldy

|||

The Ranges I want to create for the numeric Dimensions are something like this:

< 0, 0-300, 300-500, 500-520, 520-540, 540-560,....640-660, 660-680, 680-700, 700-900, >900

And for another Dimension:

<0, 0-70, 70-80, 80-90, 90-100, 100-125, > 125.

Thanks.

|||

Have you tried the TSQL CASE in the data source view.

Regards

Thomas Ivarsson

|||

Hi.

Thomas has a good suggestion (thanks Thomas). I'll give you a little more detail.

In the Data Source View (DSV) within BI Studio you can create a "named query". The named query allows you to define any valid SQL statement. Think of it like using a view in the SQL db, but it's part of the project in BI Studio so you're not bothered with maintaining a view in the SQL db. To create a named query open the DSV in Bi Studio. Right click in the layout pane and select New Named Query. The Create Named Query dialog lets you pick tables, columns, or enter your own SQL. Your choice. I recommend you enter your own SQL with a CASE statement which defines the dimension buckets you want. As an example (this code works on Adventure Works DW):

SELECT
CASE
WHEN SalesAmountQuota > 0 AND SalesAmountQuota < 5000 THEN '0-4999'
WHEN SalesAmountQuota >= 5000 AND SalesAmountQuota < 10000 THEN '5000-9,999'
WHEN SalesAmountQuota >= 10000 AND SalesAmountQuota < 20000 THEN '10000-10,999'
ELSE '20000+'
END AS SalesAmountBucket
FROM FactSalesQuota

The above statement generates the "keys" into your buckets for each record in the fact table. Don't forget to include the rest of your fact table columns in the SELECT and you get a new fact table as a named query.

The new column you've generated in the named query also serves as the attribute hierarchy for your range.

Good Luck.

PGoldy

|||

Thanks Paul for guiding with more details than in my Zen approach.

That is a good explanation of CASE.

Kind regards

Thomas Ivarsson

|||Thanks a TON to PGoldy and Thomas. Yeah, it was really that simple...can't believe it.sql

Dimension Displayed

Product Dimension Table

ProdID

Prod A

Prod B

Prod C

Fact Table

Key ProdID Measure

1 ProdA 100

2 ProdB 200

When I process the cube the and drag the measure and Product dim to view the result, the result show as below only ProdA and ProdB only.

The result sure correct and no error.

Just dont know why the ProdC not show as expected at SSAS2000.

I try the setting for the "Show Empty Cells" , It just for showing purpose only, not for permenant at that cube.

Anyone know why ProdC not show at the result as permenantly?

Do I need to do any setting to able ProdC show at the result.

Thanks .

This is not controlled by the cube, it is upto the client tool whether or not it includes empty cells or not. SSAS2000 was the same. By default a lot of browsers do not show empty cells and you have to explicitly turn this option on. There is nothing you can set at the database/cube level to control this.

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;

1. add an additional columnn to your fact table for price range, make it an integer

2. create a dimension for these price ranges

3. apply the case statement you have here as an update to that

fact table and change the terms ('Expensive', 'Moderate', etc.) to

integers that match the counterparts in your new price range dimension

4. add the new dimension to the cube schema, etc.

5. reprocess

Unless I am not understanding what you are going for here that should do it.

Edward R Hunter|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Dimension based on (derived from) Measure value

There is a measure in the cube called Price. Also a dimension called Product.

I need to create a Dimension that classifies each Product by a "Price Range". For example, Expensive, Moderate, Cheap.

A user can therefore choose Cheap from "Price Range" dimension and see the "Cheap" Products and the associated Measures (Price, Units Sold, Cost, etc).

To derive the classification, a Case statement can be used
CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
etc.......

But I can't figure out how to make this information a dimension.

I've tried a couple of things but have been unsuccessful. Please help!

If it was me, I would do the following;
1. add an additional columnn to your fact table for price range, make it an integer
2. create a dimension for these price ranges
3. apply the case statement you have here as an update to that fact table and change the terms ('Expensive', 'Moderate', etc.) to integers that match the counterparts in your new price range dimension
4. add the new dimension to the cube schema, etc.
5. reprocess
Unless I am not understanding what you are going for here that should do it.
Edward R Hunter
|||

Hi Edward -

Thank you for your feedback. You are right on the money with your suggestion, and that would be my first choice as I think it is the more correct way to design this. However, the relational data store is owned by a different group in my company and I have no update privileges to it, and change requests are added to a very long list of requests.

Also, I just think that this should be something that the tool should allow if needed.

If anyone reading this is interested, I did figure out how to add a dimension based on a Measure. I used the dimension wizard, selected the measure as the source, and select the option "Ordering and Uniqueness of Members" (i think this is the key). Then, in the dimension editor, add the case statement to the member key column and member name column expressions. process and there it is.

Edward's solution above is also a very good one and the one i would have used if I were designing this from the beginning. I would have also created a table that would hold the limits to the ranges. This would be updateable by administrators or power users via a simple web form. Then pass those values as variables to the case statement.

Joel B

Dimension [] and Measure [] have no relation in Excel Addin

Hi,

I am kind of new to SSAS 2005 but done some work on sql 2000 MSOLAP. There is a difference I spotted between the product which is annoying me and not sure if there is a solution for the same.

In SQL 2000 MSOLAP i created one cube which had only is related to active outlets listing of company only related to one dimension.

I added this to virtual cube which had many other cubes and many other dimensions. Based on virtual cubes I created reports and caculated measures using the active outlet listing without having to worry about it being linked to other dimensions. It always used to generate data in excel addin where many dimensions where put in page and row sections.

Now in SSAS I created it as part of standard cube related only to one dimension but if use it excel addin with other dimensions it gives me an error saying the Dimension [Year] and Measure [Active outlet listing] has no relationship.

How do I handle the above. Any quick help will be appreciated.

Thanks & Regards

Ramnath

This issue was discussed some time back in Chris Webb's blog:

"I did spot a problem that I've seen in other AS2005-enabled clients and which I hope won't turn into a trend. I found it by creating a report using Adventure Works, putting the measure [Internet Sales Amount] on columns and trying to put something from the Geography dimension on pages, which resulted in a dialog box informing me that the Geography dimension was unrelated to the [Internet Sales] measure group and stopping me from completing the operation. Now in 99% of cubes this would be a good thing to do, but I've already built a few cubes where I have dimensions that have no relation to any measure group but where selections on them do impact calculations (think solving the start/end date problem, where you might want to create an end date dimension with no relation to a measure group); of course, this client feature stops you from being able to design cubes in this way."

And there was this comment suggesting a work-around, if it helps you:

Mosha

"In the meantime, the workaround would be to create calculated measure which redirects to the physical measure ?"

December 29 12:10 AM
(http://www.mosha.com/msolap)

|||

Thanks Deepak,

I actually tried the work around before reporting this as a problem. The work around becomes very cumbersome when we have large number for hierarchies for one dimension.

I found the easier way by creating associations for all measures groups to all dimensions. The association is at the highlest level in all dimension structures.

How can we report this issue to Microsoft and get help in fixing it in the Excel addin. Does any body know if there is going to be maintenance release for this Excel addin.

The Excel addin link page also does not work so people are only able to see the FAQ section and download section of this addin. Other details of this addin are not available.

Thanks For all the support.

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

Different Results using Aggregate and Sum

I'm having trouble understanding the results I am getting from a query. The goal is to get the sum of a measure from the beginning of time (at the month level) through the current month.

Here is my query:

WITH

MEMBER [Date].[Fiscal].[ThruNow] AS

AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))

MEMBER Temp AS ([Date].[Fiscal].[ThruNow], [OO Units])

MEMBER Temp2 AS SUM([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])

SELECT

{ Temp, Temp2 } ON 0

,{ [Product].[Products].[Division].&[R-B-D].Children } ON 1

FROM [Merchandising]

WHERE ([Date].[Fiscal].[Fiscal Week].&[2007 16])

The Temp2 member is giving me the correct results. However the Temp member gives me the sum of the measure across all time. Can anyone explain this to me?

MEMBER [Date].[Fiscal].[ThruNow] AS

AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]))

Since [ThruNow] is defined on the [Date].[Fiscal] hierarchy, [Date].[Fiscal].CurrentMember is [Date].[Fiscal].[ThruNow] when the latter is computed, which may not be what you intended. On the other hand, this may work fine:

MEMBER Temp AS AGGREGATE([Date].[Fiscal].[Fiscal Period].Members(0) : ANCESTOR([Date].[Fiscal].CurrentMember, [Date].[Fiscal].[Fiscal Period]), [OO Units])

Different calculation based on dimension attribute?

Hello

I would like to do this in pseudo code for a calculated member:

if(dim.value == 1) then measure.val

else -measure.val

Anyone have a suggestion?

For AS2005, you should include the following in the MDX script for your cube (your will have to adjust for naming):

CREATE MEMBER CURRENTCUBE.[Measures].[MyCalculatedMeasure] AS

[Measures].[MyMeasure] * -1,

NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure];

SCOPE ([MyDimension].[MyAttribute].[1], [Measures].[MyCalculatedMeasure];

This = [Measures].[MyMeasure];

END SCOPE;

... and if it's a calculated member defined at run-time.

WITH MEMBER [Measures].[MyCalculatedMeasure] AS

IIF([MyDimension].[MyAttribute].currentmember IS [MyDimension].[MyAttribute].[1], [Measures].[MyMeasure], -1 * [Measures].[MyMeasure]), NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure]

SELECT .....

|||

Thanks for the quick response, I did solve my first problem.. sorta, with SQL in the integration layer. Now I have almost the same problem though. Now I am trying to do this instead:

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

I modified the query like so, but it only returns 0, never the Fact Result AMOUNT measure.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

0,

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = [Measures].[Fact Result AMOUNT];

END SCOPE;

|||

Please remove the NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT] part.

If you don't remove it - you are risking to get wrong results.

|||

It looks like your logic is reversed. If you want to achieve the following

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

Then the script should be.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

[Measures].[Fact Result AMOUNT],

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = 0;

END SCOPE;

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

|||

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

No, it is still wrong in this example, because when Fact Result AMOUNT can be NULL, the Cost won't be NULL (it will be 0). Note that in your first example, it was correct, since MyCalculatedMeasure and MyMeasure are always either together NULLs or together not NULLs (assuming that there are no other calculations in the cube).

Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.

differences in record counts

Hello all,

I have a problem concerning differences in record counts between the measure group and the fact table.
for debugging such a case in Analysis Services 2000, I would have run process on the cube, copy the SQL statement that was generated and debug it using Query Analyser. in sql 2005, I tried doing that but got the sql statement without the joins to the dimensions so no much help in that...

does anyone have a suggestion on how to debug such a case?

Thanks,

Momo

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

|||

Bryan C. Smith wrote:

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

So can anyone explain why the poster is observing dropped rows?

In my case I am finding the cubes are not showing any financials. Fact tables are populated correctly. Dimensions appear to be joined correctly - but I understand this wouldnt matter anyway?

Is there a way to see how the cube process is getting its data? Is there a way to check the integrity of a data source view? What is the consequence of incorrectly joining tables in your data source view?

Debugging in AS 2000 seemed much easier!

|||

When you process the cube, you have access to the queries submitted. Take a look at those and test the row counts for each.

B.,

differences in record counts

Hello all,

I have a problem concerning differences in record counts between the measure group and the fact table.
for debugging such a case in Analysis Services 2000, I would have run process on the cube, copy the SQL statement that was generated and debug it using Query Analyser. in sql 2005, I tried doing that but got the sql statement without the joins to the dimensions so no much help in that...

does anyone have a suggestion on how to debug such a case?

Thanks,

Momo

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

|||

Bryan C. Smith wrote:

In SSAS 2005, the fact table is not joined to the dimensions during processing unless you implemented a reference dimension and opted to materialize it. In this situation, you will see a join to the intermediate dimension table.

B.

So can anyone explain why the poster is observing dropped rows?

In my case I am finding the cubes are not showing any financials. Fact tables are populated correctly. Dimensions appear to be joined correctly - but I understand this wouldnt matter anyway?

Is there a way to see how the cube process is getting its data? Is there a way to check the integrity of a data source view? What is the consequence of incorrectly joining tables in your data source view?

Debugging in AS 2000 seemed much easier!

|||

When you process the cube, you have access to the queries submitted. Take a look at those and test the row counts for each.

B.,