comparing facts with different granularity

Hi,
I have a sale fact connected to a few dimensions like time(month),
product, customer, area.
I have also a budget fact connected to time(year), customer_group.
Customer_group is an itermediate level in customer hierarchy (Sector,
Segment, Customer_Group, Customer).
I was thinking of building a virtual cube, but I don't know how to
connect the two facts at differents level dimensions in dimensions
time and customer.
Can anybody help me?
Thank you
Roberto BottoYou can solve this through disabled property.
First of all, you normalize the time dimension table according to the sale
and budget fact tables.
In your budegt cube, set the disabled property of the levels, below year
level of time dimension, to Yes.
Do the same steps for customer dimension.
Create a virtual cube based on sale and budget cubes.
Ohjoo Kwon
"Roberto Botto" <orion48@.aruba.it> wrote in message
news:a6c31b2d.0503240516.2342ee7@.posting.google.com...
> Hi,
> I have a sale fact connected to a few dimensions like time(month),
> product, customer, area.
> I have also a budget fact connected to time(year), customer_group.
> Customer_group is an itermediate level in customer hierarchy (Sector,
> Segment, Customer_Group, Customer).
> I was thinking of building a virtual cube, but I don't know how to
> connect the two facts at differents level dimensions in dimensions
> time and customer.
> Can anybody help me?
> Thank you
> Roberto Botto|||Thank you for the suggestion, but I can't make it work.
The problem is in the join between the customer dimension and the budget
fact.
The budget fact foreign key is not the customer, which is primay key in
the customer dimension, but an higher level code (the customer group).
Hence the join between the fact budget and the customer dimension,
through the customer group, multiplies the the budget records. This is
my problem, not just hiding the lower dimension levels.
Thank you
Roberto Botto
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Why don't you normalize the customer dimension table according to the sales
and budget fact tables.
For example, Customer_Group(Sector, Segment, Customer_Group) and
Customer(Customer).
Ohjoo
"Roberto Botto" <orion48@.aruba.it> wrote in message
news:O$XMioRMFHA.580@.TK2MSFTNGP15.phx.gbl...
> Thank you for the suggestion, but I can't make it work.
> The problem is in the join between the customer dimension and the budget
> fact.
> The budget fact foreign key is not the customer, which is primay key in
> the customer dimension, but an higher level code (the customer group).
> Hence the join between the fact budget and the customer dimension,
> through the customer group, multiplies the the budget records. This is
> my problem, not just hiding the lower dimension levels.
> Thank you
>
> Roberto Botto
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sqlsql

Comparing demographic information

Hello,
For security reasons within our organization, I need to compare our =
customer data with a "black list" that we are getting from an outside =
source. I need to get potential matches and get this information to the =
appropiate parties. =20
The problem is, the information is coming in a variety of formats, and =
so the name/address information doesn't always match up. I'm sure I can =
use full-text search in this situation somehow, but I'm not exactly sure =
where to proceed. Can anyone offer ideas to get me started?
Thanks!
--Michael
Raterus,
While you may certainly try to use Full-Text Search (FTS) for this
application and get some level of functionality, especially using the
Forms(Inflectional), for finding word generational terms such as searching
for all products with words of the form dry: dried, drying, for example:
USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
GO
I suspect that you will be un-satisfied with this solution as I've
extensively researched this for a book project. What you should research is
string similarity functions, such as levenshtein edit distance & approx
string matching functions.
Regards,
John
"Raterus" <moc.liamtoh@.suretar.reverse> wrote in message
news:#iW1KxxeEHA.3792@.TK2MSFTNGP09.phx.gbl...
Hello,
For security reasons within our organization, I need to compare our customer
data with a "black list" that we are getting from an outside source. I need
to get potential matches and get this information to the appropiate parties.
The problem is, the information is coming in a variety of formats, and so
the name/address information doesn't always match up. I'm sure I can use
full-text search in this situation somehow, but I'm not exactly sure where
to proceed. Can anyone offer ideas to get me started?
Thanks!
--Michael
|||Thank-you very much!
"John Kane" <jt-kane@.comcast.net> wrote in message =
news:e5RFEG2eEHA.3612@.TK2MSFTNGP12.phx.gbl...
> Raterus,
> While you may certainly try to use Full-Text Search (FTS) for this
> application and get some level of functionality, especially using the
> Forms(Inflectional), for finding word generational terms such as =
searching
> for all products with words of the form dry: dried, drying, for =
example:
>=20
> USE Northwind
> GO
> SELECT ProductName
> FROM Products
> WHERE CONTAINS(ProductName, ' FORMSOF (INFLECTIONAL, dry) ')
> GO
> I suspect that you will be un-satisfied with this solution as I've
> extensively researched this for a book project. What you should =
research is
> string similarity functions, such as levenshtein edit distance & =
approx
> string matching functions.
>=20
> Regards,
> John
>=20
>=20
>=20
> "Raterus" <moc.liamtoh@.suretar.reverse> wrote in message
> news:#iW1KxxeEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Hello,
>=20
> For security reasons within our organization, I need to compare our =
customer
> data with a "black list" that we are getting from an outside source. =
I need
> to get potential matches and get this information to the appropiate =
parties.
>=20
> The problem is, the information is coming in a variety of formats, and =
so
> the name/address information doesn't always match up. I'm sure I can =
use
> full-text search in this situation somehow, but I'm not exactly sure =
where
> to proceed. Can anyone offer ideas to get me started?
>=20
> Thanks!
> --Michael
>=20
>
|||I'm trying to devise a method of doing about the same thing by matching inbound records with the most likely candidate(s) for pre-existing records. Things like name, license number, title etc if they exist in either the existing or inbound records, would
provide the criteria for matching.
I'm scanning the net this morning as a result of testing that I did yesterday. I devised my own algorithm, but I was interested in looking at the character distance apart alternatives. My algorithm low-cases, eliminates vowels uses a couple of numbers s
imilar to ss and 2 dates, and then concatenates everything into one index. The order of the index is such that it narrows from most restrictive to least restrictive. For example explorer:ford:suv : 4 wheels
then I am querying starting at the far end and working my way back mostly to see how it behaves. Thats where I left it yesterday.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||John,
While, this should also be possible in SQL Sever 2000, you might want to
checkout the following new functionality of SQL Server 2005:
Fuzzy Lookup and Fuzzy Grouping in Data Transformation Services for SQL
Server 2005
http://msdn.microsoft.com/library/de...FzDTSSQL05.asp
Regards,
John
"John Reid" <jkreid@.frontiernet.net> wrote in message
news:e4Q3v8chEHA.3536@.TK2MSFTNGP12.phx.gbl...
> I'm trying to devise a method of doing about the same thing by matching
inbound records with the most likely candidate(s) for pre-existing records.
Things like name, license number, title etc if they exist in either the
existing or inbound records, would provide the criteria for matching.
> I'm scanning the net this morning as a result of testing that I did
yesterday. I devised my own algorithm, but I was interested in looking at
the character distance apart alternatives. My algorithm low-cases,
eliminates vowels uses a couple of numbers similar to ss and 2 dates, and
then concatenates everything into one index. The order of the index is such
that it narrows from most restrictive to least restrictive. For example
explorer:ford:suv : 4 wheels
> then I am querying starting at the far end and working my way back mostly
to see how it behaves. Thats where I left it yesterday.
>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
ASP.NET resources...

Comparing data in table with validation table

Hi all

I have a customer table with a postcode and a suburb fields and cutomer info which is manually entered by data entry people...

I am trying to compare the entries against a postcode table with the correct postcodes which have fields postcode and suburb and based on the postcode entered in the customer table it should be the same as the suburb in the postcode table, if they are not the same output them to a table for manual checking..How would I go about this

thanks

You could do something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode)

etc.

As an aside, I would probably consider having a transition phase where you have a table for data entry, then a table that houses the final data, so you never have bad values in your actual OLTP database, just in the transition data, which you would delete in the process of putting it in the main table.

|||Would the above work if the postcode table contains all possible postcodes but the customer table may contain 1000 off the possible 16000 postcodes.|||

Well, yeah, but you need to be able to form the set of 1000 possible rows that are legal and use it in the EXISTS rather than all rows. So something like:

select *

from yourtable

where not exists (select *

from postCode

where postCode.postCode = yourTable.postCode

and meets1000criteria = 1)

Or you could build a view for the valid postalCodes.