distributed transaction: fact

Showing posts with label fact. Show all posts

Thursday, March 22, 2012

Distinct count with where clause

Hi there

I've little experience with MDX and would appreciate any help. I have a distinct count of a fact table of customer_id but I want a second measure where the distinct count has a where clause, where revenue(measure) >0. Both of the fields are in the fact table.

Thanks in advance.

Derek

Assuming you're using AS 2005, a more efficient option may be to add a named query as a 2nd fact table, based on the SQl where clause: revenue >0. Then the 2nd measure (in its own measure group) can directly be a distinct count of customer_id on the 2nd fact table.|||

Hey Deepak

Thanks for the reply. Yes I'm using 2005. Wow I wouldn't have ever thought of that so are you suggesting that I have just the player_id date and revenue in this second table, would I remove revenue from the first table? Also how will this affect performance seeing that I'm almost doubling the amount of fact rows.

Cheers,

Derek

|||

Derek,

you could create just named query, not additional table. The query should contain all necessary dimension links as the original one (or do you have just date dim?). You're right, amount of data in cube will increase, but in sake of speed. I'm using same thing for distinct invoices and I have separate named queries for different filters. Performance is really good with this approach.

Radim Hampel

|||

I supose you dont want to display the 0 value, then try this:

for example

1.- Create a count measure ME1

2.- Create your distinct count measure ME2

both of (customer_id)

3.- Create a calculated member

iif (isempty([Measures].[ME1]),([Measures].[ME1]),([Measures].[ME2]))

then when your distinctcount is 0 it is going to pick the empty value of the count measure and when is >0 it is going to pick the value of the distinct count. Then you arent going to have 0 in your results

it really works for me

Jose

|||

Thanks Guys

You are correct performance seems pretty good, and as you mentioned also Radim, I actually have more then one extra distinct count I want to do so am including field on which to perform distinct count and all foreign keys to dimension tables in each of these seperate queries. Who would of thought?

Cheers,

Derek

|||

Hey Jose

this sounds a bit like what I was expecting for an answer originally, looks interesting I'l give it a try and let you know how I got on.

Thanks

Derek

|||The above sounds good

Distinct count with where clause

Hi there

Thanks in advance.

Derek

Hey Deepak

Cheers,

Derek

|||

Derek,

Radim Hampel

|||

I supose you dont want to display the 0 value, then try this:

for example

1.- Create a count measure ME1

2.- Create your distinct count measure ME2

both of (customer_id)

3.- Create a calculated member

iif (isempty([Measures].[ME1]),([Measures].[ME1]),([Measures].[ME2]))

it really works for me

Jose

|||

Thanks Guys

Cheers,

Derek

|||

Hey Jose

this sounds a bit like what I was expecting for an answer originally, looks interesting I'l give it a try and let you know how I got on.

Thanks

Derek

|||The above sounds good

Distinct count problem

Hi,

I'm trying to create a measure (distinct count) for counting the number of stores which sell product. I used to fact table to count store_key but I've just realized this measure returns a count including stores which do not sell the product.

Daily Sales fact : store_key, net sales dollars .....

How should I define the calc in this case? I'm thinking conditional statements but not clear yet.

Please give me some comments. Thanks.

We do something similar - a count of invoices that have a coupon applied to them. There are two ways (that I know of) to do this:

1) at the fact level. Have a measure in your fact table that contains a 1 or 0 for each store that sells the product. Not a very efficient way, but it will get the job done. Do a sum on this measure. Depending on how you want to count your products, you could have a measure group that is store_key, product_key, sells_product (int). This will work, but definitely not efficient.

2) The way we do it is to make the item you are measuring an attribute of the dimension itself. In our case we have an invoice dimension which contains a "has_coupon" attribute. As part of the ETL we determine if the value of has_coupon shoud be "Yes" or "No". You then can query according to the values of this attribute. We then can do [Invoice].[Has Coupon].Children.Count, or any other derivation of it.

I would recommend option #2, but it all depends on your situation. If anyone else knows of other ways to model, I'm curious too.

Good luck,

John Hennesey

|||Not totally clear, but is it counting a NULL value when you don't want it to?

Just create another fact table in the DSV based off of the fact table where the key is not null:

select *
from fact
where
key is not null

And create the DISTINCT COUNT measure based of this new new table.
|||

thanks.

it's easy and it works fine

sql

Distinct Count Problem

I am using a standard star schema structure with a sales fact table and dimension tables joined by surrogate foreign keys. My product's dimension contains a hierarchy for SKU (the lowest level) and product class. I created a distinct count measure for "SKU Count" using the product key in the sales fact table. This works fine with no problem. Now I need to create a second distinct count measure for product class. I cannot get this to work. Product class exists as an attribute in the product dimension. I have tried several ways to get this to work but each time it results in an error. For example, I created an MDX calculation using both COUNT and DISTINCT COUNT and none of these worked. I also added the product class attribute into the fact table to see if this could work by creating a new distinct count against this column. This resulted in an error message.

Can anyone help with this? Basically, I am looking to create a distinct count measure against the product key in the fact table (which works fine) and than I want to create a second distinct count measure against the product class attribute that comes from the product dimension but is based off of the unique occurences of each product class in the fact table (referenced through the product class key).

David

Both these approaches should work, the MDX Calc would not perform as well as adding the product class attribute to the fact table. Can you tell us exact what errors you were getting.

With the fact table approach I think you will need to create a second measure group (based on the same fact table) as you can only have one distinct count measure in each measure group.

|||

Darren,

In order to create a second fact group based on the same table, would I need to create an alias table in the dsv first. This approach seems like it would be more resource intensive since I'm technically adding another 700,000 records with the "new" fact table.

David

|||

Yes, that's right you would have to scan through the fact table again, but each distinct count measure you create means another scan through the fact table. It depends how often you are processing your cube as this sort of volume should only take minutes. Technically you don't need to create an alias table, but I think it is the only way to do this through the UI, through an XMLA script you can create multiple measure groups off the one dsv object.

The alternative is to use the DistinctCount() function in a calculated measure, you would have to test it and see, but it can be quite slow depending on the size of your queries.

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!

Distinct Count Custom Rollup

I have a vehicleID in my fact table. I want to create a measure called VehicleCount. This would be a distinct count of vehicles per day, but would be a sum of the per day counts for months, years, etc. If I just use distinct count on the VehicleID field will that give me the per day count and how do I make it so that the rollup is a sum instead of doing a distinct count for the higher levels (month, year, etc?)

I'm using SSAS2005.

Thanks for any help.

One approach would be to create a "distinct count" measure like [DistinctVehicles] on the VehicleID field, then sum its daily values in [VehicleCount] measure:

Create [VehicleCount] as a "count" measure on the fact table, and [DistinctVehicles] as a "distinct count" measure on the VehicleID field. Add an assignment to the cube MDX script like: ([Measures].[VehicleCount], [Date].[Date].[Date]) = [Measures].[DistinctVehicles];|||

Thanks for the response.

I'm a slightly confused though. You said, "Create [VehicleCount] as a "count" measure on the fact table." Shouldn't this be a "Sum" measure? Otherwise wouldn't you just end up with the count of days?

Thanks for your help.

|||Should work either way (I did test with a "count" measure), because the [Day] level is overwrtten by the "distinct count" assignment; and those daily values should get summed up at higher [Date] levels ...sql

Monday, March 19, 2012

Displaying selected rows from a Fact table

I have a fact table which stores data ( customer name, document type, editing start time, editing end time, editor, revision id etc) for each revision of a document.

While displaying data however i need to take into account only the last revision of each document.

What is the best way of doing this? Do I need to create a separate dimension table with the document id and max revision id as fields or is there a better way of doing it?

One idea would be to mark Revision dimension as of type Time, and use semiadditive measure LastNonEmptyChild - this will show data for the last revision only.|||

I also need to create calculated members based on the lastnonemptychild. How do I do that?

Eg: for last nonemptychild ie. last revision I need to count the number of records that are of type 'S'

I also need to calculate percentage of records of last revision that are greater than target time and less than target time.....

|||This is very easy to do. Assuming you have attribute called RecordType, you can create calculated measure with|||

In the previous post you mentioned mark Revision dimension of type time. How do I do tht?

Does this also mean tht I should hv a separate dimension for revisions with attributes being documentid and revid and the hierarchy being documentid -> revid ? For the lastnonemptychild aggregation to work? That would mean tht the dimension table would contain as many records as the fact table isnt it?

|||

You don't need to change anything about your revision dimension. I imagine, that it has key attribute having values of 1,2,3,... up to whatever largest revision you think you will have in few years. I don't see the reason to include document id into this dimension - different documents can have same revision - there is no problem with it.

In the dimension editor, simply go to the properties of dimension, and choose the value Time for the property Type.

|||After changing revision dimension's property type to time, how do I use the semiadditive measure last child to sum only the records with the last revision id for the Measure InTAT ( where InTAT is either 1 or 0) ?|||You need to change Aggregation Function for this measure from Sum to LastNonEmptyChild.|||For a calculated measure how do I use the LastNonempty measure and get the sum of records with last revid?|||It is not a calculated measure. It is a real measure. Marking it as LastNonEmpty will cause returning sum of records with last revid.|||I have some calculated measures called TAT Factor, Half TAT etc for which too I need to be able to sum on the lastrevid. How do I do that?|||Make them a real measures, and move whatever expressions you use for them to the Leaves(Revision) inside MDX Script.|||I am new to analysis services. Could you explain what you mean by moving the expressions to the leaves? Should I make calculated columns in the view?