Interval/Bucket Dimension

I am trying to find the most efficient way of handelling discreet buckets over continuous/discreet set of values.

In english I am looking to have a dimension that says if age is between 0 and 5 they are an infant, 6 to 18 child and > 18 adult. I know I can do this in a case statement but I have similar senarios that require multiple levels of nesting and the ability to easily change the banding criteria with many fact table sharing the same banding information.

I have done this in the past by preprocessing the fact table and updating the key value based on between joins, but this is very inefficient on large datasets (upwards of 30 million rows).

I also need to be able to do this across continuous values such a monetary amounts.

Can anyone help

Philip Coupar

Dear Philip,

Sorry for interrupting in. Actually, I'm facing the same problem as you did. Have you solved this by any mean? Please share with us if you do. Thanks!

Regards,
Alex|||

Take a look at the DiscretizationMethod property of an attribute

http://msdn2.microsoft.com/en-US/library/ms174810.aspx

Try and build a new attribute that discetizes the coninuous set of values.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Dear Edward,

I've tried looking into the DiscretizationMethod before. However, it only got 3 modes: automatic, EqualAreas and Clusters only. On the other hand, what I am trying to do is using a customizable bucket, said 0~49, 50~99, 100~149 and so on. I can't find any method to control the DiscretizationMethod in this way. Thanks!

Regards,
Alex|||

Your custom buckets are fitting into EqualAreas schenario :)

On the other hand you can create column in the relational database and create custom mapping yourself. Alternatively you can create a named calculation in DSV and use Case statement to map into the values you'd like. http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Dear Edward,

I don't think the EqualAreas can do what I want. The Areas range are calculated by SSAS and I have no control over it. So, it's impossible to group in my way. It'll make sense to me if it provide an option for me to specify the size of each area.

By the way, I'm currently using the CASE method to handle my problem. The drawbacks are a long SQL as I need to break down from 0 to 1000 with an interval of 50. Moreover, I need to insert some dummy records into the Fact table. Otherwise, I will get a discontinuous list if there are not such value in the Fact table. I don't want my user to see the range jumping from 0~49 to 500~549.

Regards,
Alex|||

I agree it is possible to solve this issue as a case statement. It is also possible to solve this problem by using a table that specifies the start and end point of each bucket and allocate the surrogate key neccessary to have a nice dimension table.

However if we take the example where the fact table contains someone's age at the time of an event, we can see that we may first of all break this down by Adult/Child, then have further age bandings under this. The descritization functionality does not accomodate this as it will not build multiple levels, and you cannot assert the start and end ages for each bucket, required for this type of analysis.

I do not really want to find the surrogate key by doing a between join on a dimension table as this would lead to something quite inefficient over very large fact tables. It also does not make sense to me, in terms of performance, to have the case statement in the DSV as this will require a large amount of processing over a degenerate dimension attribute, a significant issue if you need to reprocess 250 Million fact rows.

The original post was asking for ideas on the most efficient ways of achieving this. Edward's posts may help some people who have different decretization requirements, or are handling smaller datasets. Alex is looking at the same issue I referred to in my original post. It may well be that there are no more effcient ways of doing this at the moment, if anyone else has any ideas of how to improve on this kind of requirement I am sure we are all looking forward to hearing from you.

|||

Check out my articles doing aging buckets this way:

... In SSAS with Named Calculations:

http://www.databasejournal.com/features/mssql/article.php/10894_3590866_7

... And a slightly different approach in MSAS 2000 (same logic applies in SSAS 2005):

http://www.databasejournal.com/features/mssql/article.php/3525516

I've done it other ways for clients with specific needs, too. Let me know if you wish further amplification, etc.

Good Luck!

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

I like the idea of adding the aging logic to the time dimension, this combines the best of the surrogate key approach with the simplicity of the embeded case statements, and would overcome the performance issues around large fact tables.

However this has a very limited application, as within a single UDM model there may be many different custom range requirements, so we may have both transaction aging and the person's age each of which would be banded very differently. and this approach would mean exposing both aging structures on every use of the time dimension.

Interval/Bucket Dimension

I am trying to find the most efficient way of handelling discreet buckets over continuous/discreet set of values.

In english I am looking to have a dimension that says if age is between 0 and 5 they are an infant, 6 to 18 child and > 18 adult. I know I can do this in a case statement but I have similar senarios that require multiple levels of nesting and the ability to easily change the banding criteria with many fact table sharing the same banding information.

I have done this in the past by preprocessing the fact table and updating the key value based on between joins, but this is very inefficient on large datasets (upwards of 30 million rows).

I also need to be able to do this across continuous values such a monetary amounts.

Can anyone help

Philip Coupar

Dear Philip,

Sorry for interrupting in. Actually, I'm facing the same problem as you did. Have you solved this by any mean? Please share with us if you do. Thanks!

Regards,
Alex|||

Take a look at the DiscretizationMethod property of an attribute

http://msdn2.microsoft.com/en-US/library/ms174810.aspx

Try and build a new attribute that discetizes the coninuous set of values.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Dear Edward,

I've tried looking into the DiscretizationMethod before. However, it only got 3 modes: automatic, EqualAreas and Clusters only. On the other hand, what I am trying to do is using a customizable bucket, said 0~49, 50~99, 100~149 and so on. I can't find any method to control the DiscretizationMethod in this way. Thanks!

Regards,
Alex|||

Your custom buckets are fitting into EqualAreas schenario :)

On the other hand you can create column in the relational database and create custom mapping yourself. Alternatively you can create a named calculation in DSV and use Case statement to map into the values you'd like. http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||Dear Edward,

I don't think the EqualAreas can do what I want. The Areas range are calculated by SSAS and I have no control over it. So, it's impossible to group in my way. It'll make sense to me if it provide an option for me to specify the size of each area.

By the way, I'm currently using the CASE method to handle my problem. The drawbacks are a long SQL as I need to break down from 0 to 1000 with an interval of 50. Moreover, I need to insert some dummy records into the Fact table. Otherwise, I will get a discontinuous list if there are not such value in the Fact table. I don't want my user to see the range jumping from 0~49 to 500~549.

Regards,
Alex|||

I agree it is possible to solve this issue as a case statement. It is also possible to solve this problem by using a table that specifies the start and end point of each bucket and allocate the surrogate key neccessary to have a nice dimension table.

However if we take the example where the fact table contains someone's age at the time of an event, we can see that we may first of all break this down by Adult/Child, then have further age bandings under this. The descritization functionality does not accomodate this as it will not build multiple levels, and you cannot assert the start and end ages for each bucket, required for this type of analysis.

I do not really want to find the surrogate key by doing a between join on a dimension table as this would lead to something quite inefficient over very large fact tables. It also does not make sense to me, in terms of performance, to have the case statement in the DSV as this will require a large amount of processing over a degenerate dimension attribute, a significant issue if you need to reprocess 250 Million fact rows.

The original post was asking for ideas on the most efficient ways of achieving this. Edward's posts may help some people who have different decretization requirements, or are handling smaller datasets. Alex is looking at the same issue I referred to in my original post. It may well be that there are no more effcient ways of doing this at the moment, if anyone else has any ideas of how to improve on this kind of requirement I am sure we are all looking forward to hearing from you.

|||

Check out my articles doing aging buckets this way:

... In SSAS with Named Calculations:

http://www.databasejournal.com/features/mssql/article.php/10894_3590866_7

... And a slightly different approach in MSAS 2000 (same logic applies in SSAS 2005):

http://www.databasejournal.com/features/mssql/article.php/3525516

I've done it other ways for clients with specific needs, too. Let me know if you wish further amplification, etc.

Good Luck!

William E. Pearson III
CPA, CMA, CIA, MCSE, MCDBA
Island Technologies Inc.
931 Monroe Drive
Suite 102-321
Atlanta, GA 30308

404.872.5972 Office

wep3@.islandtechnologies.com
wep3@.msas-architect.com

www.msas-architect.com
-- -- --

Publisher Sites:

http://www.databasejournal.com/article.php/1459531

http://www.sql-server-performance.com/bill_pearson.asp

http://www.informit.com/authors/bio.asp?a=862acd62-4662-49ae-879d-541c8b4d656f

http://www.2000trainers.com/section.aspx?sectionID=17

|||

I like the idea of adding the aging logic to the time dimension, this combines the best of the surrogate key approach with the simplicity of the embeded case statements, and would overcome the performance issues around large fact tables.

However this has a very limited application, as within a single UDM model there may be many different custom range requirements, so we may have both transaction aging and the person's age each of which would be banded very differently. and this approach would mean exposing both aging structures on every use of the time dimension.

Internet merge pull subscription server name problems

I recently did a test-run of continuous merge pull replication on my
intranet and it worked wonderfully. Now I am trying to make it work
over the net, and I just can't get server names to ever work
correctly! The server is listening on a non-standard port and this is
open from the firewall.. a test connection and telnet connect just
fine through the firewall.
If someone could please give me a list of everything that must be in
place for the remote server to be able to reference the
publisher/distributor by host NAME not IP, I would greatly appreciate
it.
I assume I need a server alias on the subscriber? Does the servername
here include the instance name? How do i reference this alias in my
sp_addmergepullsubscription and sp_addmergepullsubscription_agent
calls? What about the non-standard port?
The publisher/distributor has a localname, say SERVER, so in EM it
shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
"SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
server?
The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
we can't be sure of the IP, but we know it's always at
server.domain.com
MSDE is semi-disconnected, so this will be a PULL anonymous
subscription. I will be creating it through the above referenced
stored procedures.
Thanks in advance!
you must use SQL client network utilty to talk to the instance. Open up Client Network Utility, add a name for the alias. Then for servername add the name of the server and its instance, ie ServerName\InstanceName.
Then edit your hosts file for the ip address of the server, ie
ServerName xxx.xxx.xxx.xxx
The server name does not have to be a FQDN, ie SERVER.DOMAIN.COM\INSTANCENAME, just a name that has an ip address. For logistical purposes you should call it after your Publisher or Subscriber name, but you don't have to.
Ping this server from your publisher/subscriber to make sure it can resolve correctly and you have connectivity. you may need to use tracert to find out where your connectivity breaks.
Then its a matter of enabling your subscriber. register this server in Enterprise Manager, and then go to tools, replication, configure distributor, publishers, and subscribers, locate your subscriber, click on the check box to the left of the servername,
click on the browse button and enter the sa account (or an account in the sysadmin role), and its password. To replicate over the internet you must use sql authentication. Your publication should be set up for anonymous pull using FTP.
In your procs sp_addmergepullsubscription and sp_addmergepullsubscription_agent you will be referencing the publisher by whatever name you have assigned to this subscriber as its alias in Client Network Utiltiy.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Josh" wrote:

> I recently did a test-run of continuous merge pull replication on my
> intranet and it worked wonderfully. Now I am trying to make it work
> over the net, and I just can't get server names to ever work
> correctly! The server is listening on a non-standard port and this is
> open from the firewall.. a test connection and telnet connect just
> fine through the firewall.
> If someone could please give me a list of everything that must be in
> place for the remote server to be able to reference the
> publisher/distributor by host NAME not IP, I would greatly appreciate
> it.
> I assume I need a server alias on the subscriber? Does the servername
> here include the instance name? How do i reference this alias in my
> sp_addmergepullsubscription and sp_addmergepullsubscription_agent
> calls? What about the non-standard port?
> The publisher/distributor has a localname, say SERVER, so in EM it
> shows up as "SERVER\INSTANCENAME"... shouldn't it show up as
> "SERVER.DOMAIN.COM\INSTANCENAME" ? Do I need another alias on the
> server?
> The setup is: SQL Server 2000 publisher/distributor at a fixed domain,
> we can't be sure of the IP, but we know it's always at
> server.domain.com
> MSDE is semi-disconnected, so this will be a PULL anonymous
> subscription. I will be creating it through the above referenced
> stored procedures.
> Thanks in advance!
>