Many-to-many attributes for Customers and DW design

View previous topic View next topic Go down

Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 10:18 am

Hello,

first of all, let me tell you that I've been asking this question in many different places, including directly at Microsoft, but I haven't received any good suggestions except for "custom-code it" and "it's not possible".

So here's the issue:

This specific scenario has Customer as a dimension. This dimension relates to many different facts, which are not important for our case. Let's just assume there is one fact CustomerSales.

Now the tricky part.

There are around 500 000 Customers.

There is a table called Attribute. It contains different attributes that can be assigned to a customer: IsVIP, IsActive, IsPotential, CustomerManager and so on.

Then there is a table AttributeValue which lists valid values for each Attribute: IsVip=Yes, IsVip=No, CustomerManager=John, etc.

Then there is a table CustomerAttributeAssignment, which has Date, CustomerKey, AttributeKey, AttributeValueKey, which lists which values were valid on a given date for a given Customer.

All this is based on MSSQL DW, SSAS Cubes. The Client uses Excel for frontend.

Now the tricky part: the client wants to be able to filter all the facts (eg Sales), which are related to Customers, which:

1) Have selected attribute values.
2) Have some of the attribute values (e.g. IsVIP=Yes), and do not have any value assigned to other attributes (e.g. CustomerManager=unassigned).

Basicly, any combination of AND'ed if's among Attributes.

Now, I know that normally each attribute should become a separate dimension. However, there are many of them, and they claim the flexibility that they give them is very important.

What would be the proper way to implement this?

Thank you for any ideas.

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  BoxesAndLines on Mon Oct 03, 2011 10:39 am

Pivot all the name/value pairs to tables and columns (facts and dimensions). Yes these data structures are very flexible. You can add values without ever changing the data structure. They are a pain to query and maintain. That was easy (even for Microsoft).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 10:55 am

Can you please clarify?

The number of Attributes changes.

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  BoxesAndLines on Mon Oct 03, 2011 11:00 am

At this point today, there are a finite set of name value pairs. Take all of those name value pairs and model them dimensionally. When they add new name value pairs, you will need to enhance your dimensional model. This is not a magical solution. It will be hard work as what seemingly works well for the application can be a royal pain for everyone else.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 11:08 am

well yes, that's how we do it now, but client insists that they would like to have flexibility to add another attributes whenever he wishes without further changes..

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  BoxesAndLines on Mon Oct 03, 2011 11:24 am

Yes, I am sure he would love to have that flexibility. Good luck!
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  VHF on Mon Oct 03, 2011 11:42 am

Let's see, would it work to have an Attributes dimension, and then have a factless fact table or a bridge table to relate Customers to Attributes? I'm thinking there would be a separate row in the Attribute dimension for each value of an attribute (which sounds like your attribute source table.) To make it easy to search on unassigned values, I think you would also need an 'unassigned' row for each attribute in your dimension, and make sure to populate the bridge table for unassigned values for each customer... It might work... mock it up and try it in SSAS!



VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 2:17 pm

Hi,

that's what we've initially tried. To relate Attribute to other facts throug many-to-many and intermediate assignment table. However, many-to-many dimension, when you check multiple values works in a "OR" way, that is, matches customers having ANY of the attributes selected, not ALL of them.

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  ngalemmo on Mon Oct 03, 2011 2:27 pm

Things is, a name/value arragement is not easily queryed using SQL and not suitable for end-user use.

Typically, when something like this is done, it occurs in intermediate storeage area not accessable by users. You then implement a metadata driven process that builds a flattened table (i.e. pivot) with the desired attributes. Done right, the process can easily adapt to new attributes. Its not clear, however, if the downstream BI tool would as well.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 2:28 pm

uhm, this has to be do-able in SSAS + Excel..

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  VHF on Mon Oct 03, 2011 2:44 pm

In SQL I have handled this situation by building up a delimited searchable string. For example:

|IsVip=Yes|CustomerManager=John|RecentSale=No|

This allows searching for any combination of attribute-values:

WHERE SearchString LIKE ‘%|IsVip=Yes|%’
AND SearchString LIKE ‘%|RecentSale=No|%’

…or missing values:

WHERE SearchString LIKE ‘%|IsVip=Yes|%’
AND SearchString LIKE ‘%|RecentSale=No|%’
AND SearchString NOT LIKE ‘%|CustomerManager=%’

But this approach only works when you are handcoding SQL. I don’t think there is any BI tool that directly supports this, although some might allow building this a filter on the SearchString. You could pull the SearchString into SSAS as an attribute on the customer dimension, and I’m pretty sure you could do the same type of logic using MDX, but I suspect it would not be easily doable by an end user from within Excel.

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Mon Oct 03, 2011 2:45 pm

indeed, that's the biggest issue - gui within excel is pretty limited.

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  VHF on Mon Oct 03, 2011 2:50 pm

The overall challenge is that from a dimensional modeling standpoint the correct design is one that would add new dimensional attributes as needed. That is where the metadata resides that the tools understand—in the dimensional model and thus in the SSAS cube structure. For the tools (such as Excel against an SSAS cube) to work properly, attributes need to exist as attributes!

In theory, you should be able to programmatically update the SSAS cube structure based on the source data so that new attribute-values would be dynamically added to the cube as dimension attributes, but that would be a pretty advanced SSAS programming challenge requiring a lot more under-the-hood experience than I have!

On a more realistic note, to provide for (limited) future growth, you could provide several “user defined” attributes in addition to those that currently exist. Not the prettiest solution, but might be the most practical way to meet the customer’s requirements:

DimCustomer
------------
CustomerKey
CustomerNo
CustomerName
...
IsVip
CustomerManager
...
UserDefinedAttribute1
UserDefinedAttribute2
UserDefinedAttribute3
UserDefinedAttribute4
UserDefinedAttribute5

That should allow the users to query against any combination of customer attributes--including a few they define in the future--from Excel.


Last edited by VHF on Mon Oct 03, 2011 3:07 pm; edited 3 times in total (Reason for editing : added example)

VHF

Posts : 236
Join date : 2009-04-28
Location : Wisconsin, US

View user profile

Back to top Go down

Revolution!

Post  Mike Honey on Tue Oct 04, 2011 2:55 am

Hi algkep

I'm thinking you can meet this requirement using the "Survey" scenario outlined by the SQLBI guys in their outstanding "Many-to-Many Revolution" whitepaper:
http://www.sqlbi.com/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

Essentially you would feed your CustomerAttributeAssignment data into your SSAS model as multiple Measure Groups without visible measures (aka "bridge tables", "factless facts"). In your dimension, Attributes would play the role of Survey questions and Attribute Values would play the role of Answers. You'd then relate a "cube dimension" instance of your dimension to each Measure Group.

If you feed the data in twice (2 Measure Groups with 2 Cube Dimensions), you can query for "X and Y". Feed it in three times and you can query for "X and Y and Z". Each X/Y/Z can be a multi-select of any Attribute Values.

Good luck!
Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Tue Oct 04, 2011 3:06 am

did not understand it fully yet, but sounds like something that could work. However, the link you gave doesn't load.

And also, the trick here, is that the client wants to filter not the actual list of Customers, but instead, filter all the other fact data, by Customers matching the attributes.

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  Mike Honey on Tue Oct 04, 2011 3:12 am

Hi algkep

I've double checked that link and it's OK - maybe copy & paste the URL or just browse under Projects from www.sqlbi.com?

I believe it will let you filter any data you can relate to Customer, by your Attribute Values.

Mike
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  algkep on Tue Oct 04, 2011 3:23 am

works now, strange.

i will definitely read it, don't understand the concept yet.

it doesnt exactly solve the "universal" part of the issue, but might give me ideas on what to do.

what we're doing now is the suggestion before - we have many configurable Dimensions [Att1, Att2, Att3, etc.], where user can specify which attribute goes to which dim. You're still limited to number of dimensions this way though.

btw, does anyone know any way to programatically add new dimensions to the SSAS cube, and/or change existing dimension names, translations?

algkep

Posts : 8
Join date : 2011-10-03

View user profile

Back to top Go down

Re: Many-to-many attributes for Customers and DW design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum