Customer Count

Hello,

I have a Fact table containing sales information, and is linked to a customer dimension through a CustomerKey and to a product dimension. The Customer Dimension is a slowly changing dimension having CustomerKey as primary key and CustomerPIN as BusinessKey. A customer can have historical records in the Customer Dimension.

I want to count the number of clients per product. If I create a measure Customer Count as Distinct Count on the CustomerKey, my results are not quite correct because 2 different Customer Keys might belong to the same Customer since the latter is an SCD. So what I want is actually Distinct Count on CustomerPIN, but I am not able to create this measure correctly. It is being created as a measure group under Customer but is not linked to the sales.

I am not sure if I'm doing the right way or if I'm missing something.

Appreciate your help,

Grace

Hi Grace,

One possibility is to use a Named Query for the fact table, where the original fact table is joined to the Customer table, so as to add a Customer PIN field on which a distinct count measure can be created.

|||

Thanks Deepak,

I was able to create a Named Query having SalesKey as Logical Key with Person PIN according to the Person Key of each Sales transaction. And I added a relation between SalesKey from the New Named Query and the Fact table. Then, I was able to create a measure as Distinct Count on Person PIN and use it correctly.

However I was wondering if maybe since I can modify my underlying fact table to originally include this Customer PIN in addition to the Customer Key, and fill both from the SSIS, wouldn't that be faster and less complex? What do you suggest?

Thanks again,

Grace

|||

Grace,

My own inclination would be avoid loading redundant data, unless the cube processing performance with the Named Query is a problem. But I can't say that I've dealt frequently with this scenario, so maybe others on this Forum could comment as well?

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.

Custom user field in database

Is there a SQL 2005 table containing meta data about my database where I can
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql