Distinct Count on non-numeric column AS 2000

I have a cube with a fact table containing figures as movements within months, therefore duplicating the associated references.

I need to be able to count the distinct references (text column), but in AS 2000 I cannot get a correct answer.

If I do a distinctcount on the Members of the dimension I get a lower count than there actually is.

I have tried placing the distinct references in a separate table joined to the fact table on the reference and then counting them, but that just gives me the total figure all the time. If I use distinctcount I get the same answer as using distinct count on the dimension, i.e. wrong!

This is very frustrating - does anyone have any ideas?

I know it would be fine in As2005 but we cannot upgrade just yet.

Thank you

Try to create a table in SQL with an identity column for each reference.

Change the way the fact tavle is loaded to the cube (may be using a view) and do the DistinctCount over the newly created column.

Hope it helps.

|||

Thanks for your suggestion.

I already have a table containing all the distinct references, which I build from the fact table (which is a proper table, not a view - I have encountered problems with counts when using views before!) link to the fact table by the reference and then create a hidden dimension with one level, the reference; it is the DistinctCount of these members which is coming out wrong, although when you look at the count of the dimension level it is correct.

Therefore I added an id column to this table and a corresponding level to the dimension and tried a DistinctCount of the Descendants of the dimension at the id level, but am still getting the incorrect count. I have looked at examples of the individual references that it is failing to count but cannot see any reason why.

|||

One question: are you doing the DistinctCount in the relational or in the OLAP?

If you do the DistinctCount in the relational and then feed it to the OLAP you must be aware that the sum of DistinctCounts is different from DistincCount of the sum.

I use views 99.9% of the time without problems.

My suggestion is to create a view for the facts to be able to return the id, the other metrics columns and all dimenstion columns. Then in the cube create the DistinctCount measure over the id column.

|||

I am now completely baffled by AS's inability to do a count.

As recommended I have added a numeric id column to the fact table relating to the reference I need to count, populated it, then created another cube as a copy of my original one but with only 1 measure, the DistinctCount of the ID column.

Even before I merge these 2 cubes into a virtual cube I can see that the count is STILL wrong - it shows in AS as 25538 whereas checking via SQL (i.e. select count(distinct ID) from FactCube) gives the correct answer of 25995.

This was the same incorrect number I was getting before when I tried a calculated member - where have the other 457 rows gone? I know of no other way to do this and really need some advice as to why AS appears unable to do a proper count. It is vital to the cube that this functionality is available.

We use Sybase as our relational database, in case this has any bearing on the matter.

Thank you

Rachel

|||

Hi,

I use Sybase IQ and little Sybase ASE as the source without problems. I think I had a problem with group by in IQ 12.6. However, it should not be relevant here.

Make sure the Relational query and OLAP query are comparable. Are you sure you do not have any other fact data, besides that query?

Get the select that AS2000 send to Sybase and analyze it. Then run it to compare the data.

DistinctCount is a very slow process, but in my experience accurate.

|||Thank you for that advice - I went through the SQL statement and eventually tracked down the problem to some dodgy data in one of the dimensions. Thank goodness for that!