Materialized reference dimensions

I have a cube that has a chain ofreferenced dimensions in it. Analysis Services says all the dimensions in the chain must be "materialized". In fact, it demands it. But when I set them to "materialized", the cube won't process. I get this error message:

Errors in the OLAP storage engine: An error occurred while processing the '2004Trx' partition of the 'Membership Counts' measure group for the 'Memberships' cube from the DimensionsOnly database.

I'm stuck. Anyone have any ideas on how to get around this?

Thanks.

Are there more messages you are getting along with one you mention above?

Is there missing key or something like that?

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

matching users profiles using sql

Hi folks

I have a section in my networking site where people can write things about themselves, such as likes dislikes, hobbies etc for their profile.

I am looking to provide a very basic profile matching service, and have done a bit of searching to find out what the best way to approach this feature is. I have looked at using the sql LIKE clause, but since the profile information can be any length, there seem to be too many variables to account for. I have also looked at identifying keywords in profile blurbs to do the matching too, but I am struggling to see how this could be done with relative ease.

My reason for posting this is simply to get some experienced knowledge about where to start the problem, of even some resources to look at that I may not have found yet.

If anyone could give me some advice on where to start i would be very grateful

I am develping in vb, with an sql server 2005 back-end

Hello my friend,

You are right about the SQL LIKE clause not being so useful when dealing with large amounts of text. I have worked on something similar before. The best thing to do is to have as many closed questions as possible regarding the profiles. And when they want to be more specific, add sub categories, and sub sub categories and so on. Then the SQL is easy. Most of the big guys do this. If you ever try and create a new hotmail account for example, they throw loads of checkboxes at you and throw the appropriate adverts at you every so often.

Kind regards

Scotty

|||

Hi Scotty

Thanks for the advice. I'm going to begin the development of this feature very soon so i will document what i have come up with here.

Matching Transactions and Duplicates

Can anyone suggest a good approach to this problem?
I need to match transactions from two tables on several columns. When
I have a match, I need to set a match flag and a match number in each
table. There can be duplicate transactions in either table. If I have
two transactions in one table that match three in the other, I want to
set the match flag and match number on two transactions in each table
and leave the third one blank.
Results:
table 1
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
5 5 Y 3
5 5
table 2
--
col A col B match_flag match_number
1 2 Y 1
1 2 Y 2
1 2
5 5 Y 3>> Can anyone suggest a good approach to this problem?
Please post your table structures & expected results along with clear
explanation of what you are trying to do. For details, refer to :
www.aspfaq.com/5006
Anith|||I have two temp tables loaded with the transactions I want to match
with the other table. They need to match on mid, tran_date, amount,
and card. When there's a match, I want to set the match_flag to 'AM'
and set the match_number to a unique number given to each matching
transaction. It's possible that there can be duplicate transactions,
as in the samples below, where there isn't a corresponding match in the
other table. If there are three in one table and two in the other that
match, I want to set the match_flag and match_number on two from each
table and leave the third from one table null to match on a possible
future load. I'll be matching on several thousands of transactions.
Let me know if you need more information. Thanks.
create table #setl (
mid char(6) not null,
load_number int not null,
detail_number int not null,
batch_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
create table #debit (
mid char(6) not null,
load_number int not null,
detail_number int not null,
tran_date datetime not null,
amount decimal(8,2) not null,
card char(4) not null,
match_flag char(4) null,
match_number int null )
Data:
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 null null
2005-09-30 .01 4444 null null
desired results:
#setl
--
mid load_number detail_number batch_number
543684 23712 1 877
543684 23712 2 877
543684 23712 3 877
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2
2005-09-30 .01 4444 null null
#debit
--
mid load_number batch_number
543684 658 1
543684 658 1
tran_date amount card match_flag match_number
2005-09-30 .01 4444 AM 1
2005-09-30 .01 4444 AM 2|||First all, in databases, duplicates nullify logic. In other words, without a
column or set of column to uniquely identify a row in a table, it is
impossible to logically manipulate the data in those tables.
Given the data in your tables, it is impossible to tell if the duplicate
rows represent actually duplicated transactions or simply erroneous entries.
So rather than trying to work with meaningless data, you should consider
eliminating the duplicates in the first place. For starters refer to:
http://support.microsoft.com/kb/139444/en-us
If you are looking for a short term workaround to please your boss, write up
a cursor to loop through the rows and assign the match_flag and match_number
values. However, such a solution will do no good since you will still be
left with redundant data with no keys and constraints.
Anith

Matching Strings In Different Tables Of Same Database

I have a situation where I want to pull strings from one table of a SQL 2000
database and find matches for it in other tables of the same database and
have those values returned. i.e. In one table I have prospects and I want
to match their names to a table that stores the names of prospects turned
into customers. I want to write a query that looks through every entry and
returns a match for each corresponding value (from prospects to customers).
So if "Smith" is found in prospects I want SQL to return "Smith" in
customers with full contact info.

Any pointers on getting started on this is greatly appreciated. Or if you
could just point me to a reference. Obviously, I need to do some kind of
parsing. I just need to be pointed in the right direction.

Thx."Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon|||"Smith" <gsmith@.tbanet.org.nospam> wrote in message
news:RPWdc.410622$B81.6621293@.twister.tampabay.rr. com...
> I have a situation where I want to pull strings from one table of a SQL
2000
> database and find matches for it in other tables of the same database and
> have those values returned. i.e. In one table I have prospects and I
want
> to match their names to a table that stores the names of prospects turned
> into customers. I want to write a query that looks through every entry
and
> returns a match for each corresponding value (from prospects to
customers).
> So if "Smith" is found in prospects I want SQL to return "Smith" in
> customers with full contact info.
> Any pointers on getting started on this is greatly appreciated. Or if you
> could just point me to a reference. Obviously, I need to do some kind of
> parsing. I just need to be pointed in the right direction.
> Thx.

If you are matching name columns, then this may be in the right direction:

select
c.CustomerID,
c.LastName,
c.CompanyName,
c.ContactPhone,
...
from
dbo.Customers c
join dbo.Prospects p
on c.LastName = p.LastName
where
p.LastName = 'Smith'

If this isn't what you're looking for, it would be helpful if you could post
CREATE TABLE statements for the tables, pluse INSERT statements for some
sample data, and the results you expect.

Simon

matching saved searches to newly inserted record

Hello SQL wizards,
I'm trying to match saved searches to a newly inserted "job", and send
an email for matching searches. get about 20 job postings a day, and
have about 150k saved searches. want to do this as quickly as possible.
Please advise...
here's what I need to do:
1. job sers create saved searches, with criteria such as location
and some keywords
2. job is posted by a employer and inserted in job table
3. find all saved searches that match the newly inserted job
4. send emails to job sers with matching searches
right now, i'm doing the following:
1. using insert trigger on job table
2. put matching searches into a cursor (except by keyword search as I
can't figure out how to match by keyword using full text index all in
one statement)
select savedSearchId,...from savedSearches where (location='' OR
location=@.JobLocation) AND (duration='' or duration=@.jobDuration)...
3. loop throught cursor, doing
if(savedSearch has keywords)
select count(*) from jobtable where jobid=@.newlyInsertedJobId and
CONTAINS(*, keywords)
4. send email if matches keywords
this takes a while. there are about 150k saved searches. filtering on
non keywords returns about 3000 records to the cursor. the CONTAINS
search takes a long time.
Questions:
1. possible to do an asynchronous insert using ADO.net 1.1?
2. should i find the matching saved searches, put them in a table, and
do the keyword search/email later? if so, how?
3. how would you do it differently?
4. how to send email? xpsendmail or external component?
Thanks in advance!
Neilfound some problems myself:
1. full text index doesn't contain the new posting as it was just
inserted. should i do an incremental catalog population on insert?
2. contains() returns all rows that match the keywords, and THEN it's
filtered by jobid, so that's why it's slow...
any advice would be greatly appreciated.|||(neilmcguigan@.gmail.com) writes:
> 1. possible to do an asynchronous insert using ADO.net 1.1?
No and yes. There is no such thing as an asynchrounous insert, but
in your INSERT trigger just write a row to an alert table, and have
a job to run from SQL Agent (or scheduled by your own app) once a minute
or how often you see fit, to check for new entries.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||For instant propagation of changes to the FTI, you should use change trackin
g
and background propagation.
Look up sp_fulltext_table in Books Online.
ML|||Yes, both are true for SQL Server 2000...
For #1 you should enabled "Change Tracking" with "Update Index in
Background". The first initial setting of the CT with UIiB will
automatically run either a Full or Incremental population depending upon a
timestamp column in the table and if the FT Catalog is already populated.
For #2 you may want to use more sophisticated filtering with pre- and post-
processing as I once worked with a client in Europe who had a similar
requirement, except they were using FTS with a custom new clipping service
where the newspaper publishers were the employers and the newspaper reader
was the job ser. If you're interested, I may be able to put you in touch
with them. Feel free to email me directly if you want.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<neilmcguigan@.gmail.com> wrote in message
news:1131222002.331389.73460@.g49g2000cwa.googlegroups.com...
> found some problems myself:
> 1. full text index doesn't contain the new posting as it was just
> inserted. should i do an incremental catalog population on insert?
> 2. contains() returns all rows that match the keywords, and THEN it's
> filtered by jobid, so that's why it's slow...
> any advice would be greatly appreciated.
>

Matching relational records. Is it possible using Data Minig?

Problem:
I am working on a price comparison system which matches the best prices for a purchase (or an order) from exisiting purchase data.
The order is stored in multiple tables including order details (stores major items purchased: e.g., PC) and order sub-details (optional items purchased with the major items: e.g., speakers, backup device, webcam etc.).
There could be a number of major items in an order and each major item could have multiple related sub items. The other variables that affect the price include trade-ins if any, sales going on at the time of order, number of units etc.

Now, for any new configuration (major items/related sub items), the system should be able to return a list of previous purchases made with similar configurations, and similar variables (quatities, trade-ins etc). Even if the same model is not present, similar pcs by the same vendor should be considered. etc etc.

Questions:
Is this possible using Data mining?
If yes, which algorithm is recommended?

Also, can I assign/modify any kind of weights to certain variables (if same model: .6 ; if same model not available but pcs made by same manufacturer available: .3 ; by other manufacturers: .1)?

Any help will be greatly appreciated.

Thanks,
Jojy

This seems like a reasonable problem for data mining. I would recommend decision trees, neural nets, or logistic regression. There is no way in SS2k5 to weight attributes, however, you can simulate gross weighting in NN and LR by duplicating columns. E.g. if you have a column "model" which you want to weight twice as much as other columns, you duplicate it to "model" and "model1" with the same data inside.

Matching records within same table

Hi all,

I've got a table named tLogonInfo with the following fields: fLogType, fDeskID, fUsername, fEventDate.

This table contains the logon and logoff history of an application. So for example the table could contain the following records:

1. 'Logon', '1', 'Jack', '2007-7-15 13:00:00'

2. 'Logon', '3', 'Paul', '2007-7-15 13:22:00'

3. 'Logoff', '1', 'Jack', '2007-7-15 14:00:00'

4. 'Logon', '2', 'Jack', '2007-7-16 10:30:00'

5. 'Logoff', '3', 'Paul', '2007-7-16 11:12:00'

6. 'Logoff', '2', 'Jack', '2007-7-16 17:10:00'

I want to make a query that has the fields Desk, User, Logon, Logoff and has the following result:

1. '1', 'Jack', '2007-7-15 13:00:00', '2007-7-15 14:00:00'

2. '3', 'Paul', '2007-7-15 13:22:00', '2007-7-16 11:12:00'

3. '2', 'Jack', '2007-7-16 10:30:00', '2007-7-16 17:10:00'

How can I do this?

if you use sql server 2005,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

;With Ordered as

(

Select

*

, Row_Number() Over(Partition By [User],[Mac] Order By [User],[Mac], [OperDateTime]) OpId

From

#logindata

)

Select

Logon.[User], LogOn.Mac, Logon.OperDateTime [Logon Time], Logoff.OperDateTime [Logoff Time]

from Ordered Logon

Left Outer Join Ordered Logoff On

Logon.[User] = Logoff.[User]

and Logon.[Mac] = Logoff.[Mac]

and Logon.OpId = Logoff.OpId -1

Where

Logon.Operation='Logon'

and Logoff.Operation='Logoff'

Drop table #logindata

|||

For SQL Server 2000,

Code Snippet

Create Table #logindata (

[Operation] Varchar(100) ,

[Mac] Varchar(100) ,

[User] Varchar(100) ,

[OperDateTime] Varchar(100)

);

Insert Into #logindata Values('Logon','1','Jack','2007-7-15 13:00:00');

Insert Into #logindata Values('Logon','3','Paul','2007-7-15 13:22:00');

Insert Into #logindata Values('Logoff','1','Jack','2007-7-15 14:00:00');

Insert Into #logindata Values('Logon','2','Jack','2007-7-16 10:30:00');

Insert Into #logindata Values('Logoff','3','Paul','2007-7-16 11:12:00');

Insert Into #logindata Values('Logoff','2','Jack','2007-7-16 17:10:00');

Select

[user],

[mac],

[OperDateTime] as [Logon Time],

(Select Min([OperDateTime])

From #logindata Sub Where

[Operation]='Logoff'

And Sub.[user]=Main.[user]

And Sub.[mac]=Main.[mac]) as [Logoff Time]

from #logindata Main

Where

[Operation]='Logon'

Drop table #logindata