Many-to-many attributes for Customers and DW design
5 posters
Page 1 of 1
Many-to-many attributes for Customers and DW design
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.
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
Re: Many-to-many attributes for Customers and DW design
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).
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Many-to-many attributes for Customers and DW design
Can you please clarify?
The number of Attributes changes.
The number of Attributes changes.
algkep- Posts : 8
Join date : 2011-10-03
Re: Many-to-many attributes for Customers and DW design
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Many-to-many attributes for Customers and DW design
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
Re: Many-to-many attributes for Customers and DW design
Yes, I am sure he would love to have that flexibility. Good luck!
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Many-to-many attributes for Customers and DW design
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
Re: Many-to-many attributes for Customers and DW design
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.
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
Re: Many-to-many attributes for Customers and DW design
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.
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.
Re: Many-to-many attributes for Customers and DW design
uhm, this has to be do-able in SSAS + Excel..
algkep- Posts : 8
Join date : 2011-10-03
Re: Many-to-many attributes for Customers and DW design
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.
|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
Re: Many-to-many attributes for Customers and DW design
indeed, that's the biggest issue - gui within excel is pretty limited.
algkep- Posts : 8
Join date : 2011-10-03
Re: Many-to-many attributes for Customers and DW design
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.
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
Revolution!
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
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
Re: Many-to-many attributes for Customers and DW design
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.
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
Re: Many-to-many attributes for Customers and DW design
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
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
Re: Many-to-many attributes for Customers and DW design
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?
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
Similar topics
» dimension table design question for around 100 attributes and higher level calculated attributes
» Design Standards - Numeric Type 2 Dimension Attributes
» Design Tip #142 - Building Bridges Dilemma with Diagnosis - additional account specific attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Customers from 2 sources on different granulaties
» Design Standards - Numeric Type 2 Dimension Attributes
» Design Tip #142 - Building Bridges Dilemma with Diagnosis - additional account specific attributes
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Customers from 2 sources on different granulaties
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|