Could anyone please advice how to create a Customer dimension when my
Fact table has Account Numbers that are not present in a Customer
table itself?

My Fact table among other fields has

Invoice Number
Invoice Date
Account Number
Sales Amount
Impressions

My Customer table has fields

Account Number
Customer Name

One Customer may have many accounts.

The trouble is my Customer table doesn't have all Account Numbers that
Fact table has (many accounts were deleted from the Customer table as
they are no longer our customers)
But I really have to show those accounts otherwise totals for the
previous years wouldn't be correct.

I am creating Customer dimension with levels

Customer Name
Account Number
Invoice Number

But no matter from which table I take Account Number (Customer or Fact
table) and what I do, I cannot get those deleted accounts to appear.

Is there any way to display all accounts, not present in Customers
table under Customer Name called "Closed Accounts", for example? I am
sure there should be a way to do it other then to create a view from
Fact table + Customer with distinct Account Number and "Closed
accounts" for those that are not fount in Customer table.

Any help is greatly appreciated.

You have a couple of options.

1. You could create a Non-Active member in the customer dimension and
assign all of the fact records that don't have a corresponding member in the
customer table to be equal to that "non-active" member.

2. You either need to create a view that joins the two tables together and
source your dimesnion from that.

3. Create a dedicated dimension table for your customers. You may decide to
incorporate method "1" into that table.

It really depends on what you need to show in that dimension. This is
really what ETL is all about. You have to cleanse the data from the source
systems and create tables that are dedicated to data warehousing and
addressing the analytical questions at hand. While you could create a view,
it's probably not the best way to approach it. The best way to handle it
would be to create a dimension table for your customers that tracks them
over time, including when their accounts are deleted or set to non-active.
Your ETL process will determine the appropriate status of the customer, and
that might be partly indicated by records that are in the fact table.

Sean


--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.


"A" <aresnik (AT) tph (DOT) ca> wrote

Thanks a lot for your reply. In my case the first option is a great
solution, but I don't know how to implement it. A couple days ago I
tried to assign Customer Name to the member property of the Account
Number and could see it in property window OK while moving the cursor
over the Account.
But how to display it among dimension levels?

If I cannot display the Customer Name as a separate column, is it at
least possible to add the value of member property to an Account Number
in its Member Name Column property? What the syntax would be like?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

It really depends on the client tool you are using to look at the data.
Some tools expose member properties better than others. If you want to just
concatenate the member name and the account into the member name property
you could just change the source column of the member name to be equal to
"dbo"."customer" + "dbo"."account_number" or whatever your field names are.

However, I think the more important issue is what to include in your
dimension table, which is what I thought your initial question was referring
to. If you have some accounts in the fact table that are not in the current
customer dimension table, you need to decide what you will do to track the
history of those accounts. I simply wouldn't rely on a set of tables that
were not designed specifically to address the needs of your application.
That might mean getting some changes or views created to the existing
tables, or having to build your own.


--
Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"Alla Resnik" <aresnik (AT) tph (DOT) ca> wrote

Sean,

Changing the source column of the member name to be equal to
"dbo"."customer" + "dbo"."account_number" does’t work.

I changed my dimension to start with

Account Number level with a member property Customer

Account Number has
Member Key(and Name) Column: "dbo"."FactTable"."AccountNumber"

and source column for Customer member:
"dbo"."tblCompanies"."CompanyName"+"dbo"."FactTabl e"."AccountNumber"

gives me an error: the member key column is not valid


Also is it possible in AS Manager to create a Customer Dimension with
levels

Customer
Account Number...
Invoice

where Customer is a top level so while building a dimension, all Account
Numbers from the Fact table, that are not found in a Customer table
would be classified as a "Closed Accounts" Customer (I am not interested
in tracing those closed accounts).

I understand I have to add a record to the Customer table
Account Number-000 (for example)
Customer name - 'Closed Accounts'

And then in a Member Name Column (or Member Key Column?) property for
Account Number level I should create some expression, saying something
like: if AccountNumber is not found in Customer table, switch it to
“000”. Is it possible at all?
I tried case…when and iif… but it didn’t work

Same “the member key column is not valid” message.

If no one can give me a proper syntax to do it, may be you can
recommend some article to read?

Thank you.



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

The best solution here is to build a single table that stores your
customers. Your example has references to two tables. So you either need to
create a view and build the dimension from that or build a new table that
stores your customers.

You can enter the following for example as a member name.

"customer"."fname" & "customer"."account_num" as a member name. I just
created a new dimension in the Foodmart database and it worked.

You can definitely build a dimension with the following levels

Customer
Account Number...
Invoice

You can build a snowflake dimension with a customer table that has a one to
many relationship to the account table, which has a one to many relationship
with the invoice table. You probably won't need to create an invoice level
though, that is probably too granular for a cube. Not that you can't do it,
but you typically just don't see it.

Sean



"Alla Resnik" <aresnik (AT) tph (DOT) ca> wrote