Hi,
I have a product basket scenario in where I have to recommend contracts to a customer based on the product and the quantity he/she buys. Product Quantity is an important factor which administers the user in the purchase of a particular contract
I have the following tables with me.
Customer product transaction table, Customer Contract transaction table but there is no direct relationship between contract and product in the database. The only way the two can be linked is through the customer.
If I create a mining structure with Customer-Product information as the nested table and Customer-Contract information as the nested table with customer being the link between the two, the model is showing some irreverent contract recommendations.
what is the solution for the above problem? Is it because the is no direct relationship between the product and the contract?
How can I overcome this problem?
I got 2 webservers (2003) using NLB. I want some kind of common data store (SQL Server). I need this data store to be fault tolerant. I don't have any more machines. I don't have an enterprise licence for windows.
I have a company with 5 offices connected with a dedicated line (500+ employees).
They have five databases, one in each office.
Now they want a new application which will replace the old (VB6 on MySQL will now be VB.NET 2.0 with MS SQL Server 2005).
The want all the data availiable in all the offices. We will still have 5 databases in 5 offices, but all with the same data.
Sometimes the dedicated line is lost.
Now I have a few questions:
Will the database be still running while a line is down? Will the database be still running if replication is of type Merge (instead of continuesly). Is it good practice to use unique identifiers versus Numeric keys? Is it good practice when the application uses the "master" database to save global things like "Suppliers" and the local database to add and change Orders? Do you have some recommended reads?
For High Protection mode (2servers, no witness, FULL safety), in the event of a catastrophic failure of the principal, can a remote (but well connected) mirror be forced to assume the principal role? The MS manual "Database Mirroring in SQL Server 2005" on p2 says the failover type for HP mode is manual only (as opposed to forced). Elsewhere it seems to seems suggest otherwise but there is no clear and unambiguous discussion describing the exact procedure.
I currently have a workflow "like" application which uses msmq to pass messages to each step transactionally. I'd like to take advantage to something like SSB to make the app scale. Here's how it works today:
Start Workflow WebService puts message on queue. Windows Service has threaded listeners on each queue for the workflow. The listeners do a transactional Receive off the queue. By doing this is in a transaction, we can enforce that only one listener will pick up and process the message. Once they have a message they call a .Net Assembly to perform some work. Once the work is performed succesfully, the listeners put the work context on the next queue using the same transaction as the receive and commit the transaction effectively removing the message from the receive queue permanently. If a fatal error occured during processing, the work context is moved to an error queue transactionaly. If the process blew up due to other reasons, the transaction is rolled back and the message reappears on the original queue so that another instance of the service can pick it up. Currently all this is running on one instance of the Windows service and cannot span more than one host machine since MSMQ did not support transactional reads off public queues without using DTC.
I saw the external activator sample and it looks great but I have a few questions. First, I'd have to modify it to call assemblies instead of external processes. No biggie there. One other requirement that one and only one receiver should process the message and that when the next message is sent to the next queue, it's done within the same transaction as the receive. I hope that this can be done without any deadlocks. I also saw mentioned somewhere that this messaging could advantage of the local SQLExpress instances so that even if the main SQL Server instance is down, the messages can go out. Last requirement is that our message is a blob (serialized .Net object) so how do we define that kind of message?
Hello, Let€™s say (for simplicity), in my site you can do one of two things €“ look at products and buy products. I want to build an association structure between my products based on those two actions, but(!) when a user looks at two products it creates less important association than when the user actually bought those two products. So basically, I want to give a different factor base on different actions occurred on my products. How do I build my structure? How do I query it?
I'm trying to figure out how to build a personalization engine.
If my structure is built with users as case, and products as nested - I€™d like to predict best products per user (rather than associated products), and If possible, ignoring products he already bought.
When i use the MS association rules ,i don't know how it is worked on the background .I stuy the Fp-Growth algorithm , but there're some questions , I don't kown what's the meaning of transcation database. who can give me one example ? thanks .I know we can store the data in relation database,but in basket Analysis ,how a transaction stroed in relation database?
I am entering to administration of SS2005 SP1 (Windows 2003) having files mdf, ndf, ldf in C:Program FilesMicrosoft sql serverMSSQLData This dir also has two *.cer files.
Apparently no encryption is used
How can I get known what these *.cer files are for?
I'm wondering if anyone can give me some help with an association model I'd like to setup. It's a typical market-basket analysis, but rather than grouping by individual customers, I'd like to group by customer grouping. (In our database, customers are grouped into categories like: large, small, medium) If this is possible, I'd like to generate the most popular items (so just querying the most probable itemsets), for each customer grouping (I'll refer to this as 'segments' from here on out), and then create a listing of customers in each segment which do not have the most popular items for their segment. I know for this last part I can use reporting services to tackle that problem, however, I'm not really sure how I can really do the rest of this with an association model in SSAS.
Our table structure looks like this:
Code Snippet
CustomerTable PurchasesTable
------------- --------------
CustomerName(key) CustomerName
CustomerGroup PurchasedProduct
And the data is arranged in this fashion:
Code SnippetCustomer Table: CustomerName CustomerGroup ------------- ------------- A large B large C small
Purchases Table: CustomerName PurchasedProduct ------------ ---------------- A ProductA A ProductB B ProductA C ProductC C ProductD
I know this is a lot of information but any help you guys may be able to offer would be great! Thanks!
Table1: WriterID, WriterName, WriterSurname Table2: BookID, WriterID, BookName, Category Table3: CategoryID, CategoryName There will be one form. Which way i should use to enter data from one form to multiple tables with same WriterID. 1.) Should i use SqlCommand class and a Stored Procedure? ( Like that: http://www.aspnet101.com/aspnet101/tutorials.aspx?id=13 ) 2.) Is there another way without a stored procedure? I can't imagine how to insert same form to multiple tables. In real scenario there are lots of table and all contains a column that holds same value such as WriterID.
Hi, I want to know the a solution for my Synchronization Scenario
I have a several client databses which are SQL Server 2005 Express and i have a master database which is SQL Server 2000 containing all the individual Client databases. All the individual client databases are kept seperately at the master location. I need to Synchronization the client database with its copy at the master database (something like Merge replication). Both the Client Copy as well as Master Copy could be Publishers & Subscribers.
Now the problem is Because of security & firewall issues, only the Client should have the ability to schedule & initiate the synchronization process with the master copy. Unfortunately SQL Server 2005 Express has only subscriber agent and not a publisher agent.
Any help on how to achieve this would be appreciated . Thank You
Hello All .. This is the scenario I'm having : -- I'm a beginner so bear the way I'm putting it ... sorry !
* I have a database with tables - company: CompanyID, CompanyName - Person: PersonID, PersonName, CompanyID (fk) - Supplier: SupplierID, SupplierCode, SupplierName, CompanyID (fk)
In the Stored Procedures associated (insertCompany, insertPerson, insertSupplier), I want to check the existance of SupplierID .. which should be the 'Output' ...
There could be different ways to do it like: 1) - In the supplier stored procedure I can read the ID (SELECT) and :
if it exists (I save the existing SupplierID - to 'return' it at the end). if it doesn't (I insert the Company, the Person and save the new SupplierID - to 'return' it at the end) ------------------------------------ 2) - Other way is by doing multiple stored procedures, . one SP that checks, . another SP that do inserts . and a main SP that calls the check SP and gets the values and base the results according to conditions (if - else)
3) it could be done (maybe) using Functions in SQL SERVER...
There should be some reasons why I need to go for one of the methods or another method ! I want to know the best practice for this scenario in terms of performance and other issues - consider a similar big scenario ..... !!!
I'll appreciate your help ... Thanks in Advance . ! .
How can I be able to use "NewReceivedTime" as a variable? Since having to create a new column using CASE statement in SQL would mean that user will not be able to use this new column name and having to receive error such as "Invalid Column Name: NewReceivedTime "
(case when <value> else <value> end) as NewReceivedTime
I'm asking this because, I would want to use "NewReceivedTime" I've created to equate to another Time Column like NewReceivedTime = LogDateTime something like that.
i hav a text file in this format.. currency,exchangerate(INR),date dollar,45,20/04/2006 dollar,46,22/04/2006 britishpound,65,20/04/06 dirham,12,20/04/06..etc..
now,i want that using this as source.. 2 tables should be created and filled with appropriate data.. CurrencyMaster..Currencyid(PK),Currencyname CurrencyDailyRate..ID,Currencyid(FK),rate,date
What I'd like to do is use SQL Express as a back-end for an existing Access 2003 application. The application is currently a single-file Access solution. I'm just investigating options to separate the front-end from the back-end so that the users can access a single data store from multiple PCs. We're only talking about maybe 5 users total, and really no more than 2 accessing it simultaneously at any given time (although the same setup exists in several offices). Right now they are just opening the Access db from a fileshare. This has resulted in corruption on a few occasions, which is part of the reason for wanting to replace the current solution with something that will be a little more robust. I'm wondering if there is a way to deploy a SQL Express db on a fileshare so that it can be connected to by the Access front-end. While we can install things on their desktops, we can't install anything on the file server, we can just put files there. Is there any way that I could make that solution work, or should I just stick with separating the Access front-end and back-end?
I have a huge replication task I need to perform. The source table has over 250,000,000 records and everyday approximately 400,000 records are added to the system regularly.
Currently, I am running snapshot replication and it's taking 10 to 11 hours to complete (The internet connection between the production and the report server is slow). The reason I am using this method is because the source table does not have a timestamp column (so I can run an incremental replication) and I cannot modify that table because it belongs to a third party software. It does have a field which is linked to another table holding the timestamp.
Here is the source table and the other table's structure for reference:
DataLog
Name Datatype Size
DataLogStampID int 4 Value float 8 QuantityID smallint 2
DataLogStamp
ID (Which is foreign key to DataLogStampID field in the above table) int 4
SourceID smallint 2
TimestampSoruceLT datatime 8
What I would like to know is, what is the best practice to handle such replication with MSSQL Server 2000.
I am thinking of developing a procedure which follows the following step using a temp table:
This procedure will run (say) every night.
The temp table will contain a timestamp field.
For one time only, I will run a snapshot replication, so that I can have the existing data into the destination db.
I will record this timestamp into a repl_status table which holds the timestamp of the last replication.
The procedure will read the record from repl_status and select all records from the source table which were added since this date and put them into a new temp table (this table will have the same structure as the source and destination tables) and then a snapshot replication will "add" these new records only every night to the dest. table. By using this method, I will only transfer the records which have been added since the last replication (last night - less records).
I am faced with a scenario where I have to predict the time period that will required to solve a particular problem depending on various factors like Problem Type, Severity Level, Resource Availability etc.
I would like to know which algorithm is best suited for the above scenario and the reasons for the same.
I'm about to implement a DB Mirroring on my production server.and i'm wondering about the scenario what will happen if. 1. if my primary server suddenly die.how i can operate the mirror DB without losing data? 2. when the primary server comes to life, how i make the primary server to lead again with the DB Mirroring?(do i need to recreate all procedures of DB Mirroring again) 3. how can i be sure that the DB mirroring is indeed mirrored on the drp server? 4. what about alerting - can i be notified via email if the DB Mirroring isn't sync? or if the drp server is down and other issues that can be occur .
We have Asynchronous DB Mirroring established for our Production database
which runs on SQL Server 2005.
For the DR Test we plan to do the following during the period of low activity :
1. Pause the mirroring on the Principal Server.
2. Break the mirror on the Mirror Server
3. Take on offline backup on the Mirror.
4. Bring the Mirror Database up.
5. Run the DR Tests on the Mirrored Database
6. Restore the Offline Backup taken in step 3.
7. Reestablish the mirror from the Principal to Mirror.
8. Resume Mirroring on the Principal Server.
9. Verify Mirroring is happening on the Mirror Server
Can u please let me know if this plan is feasible and if there should be any modifications to the plan that are required. Any other suggestions/input is appreciated.
we have a pretty vast t-sql statement that queries out production db and inserts results into a reporting table on the same db. the problem we're having is that during this process there are several locking processes engaged (key, table, schema, etc). i thought by moving this entire process to a seperate (reporting) database i could bypass this locking problem. however, the same problem exist in the reporting database. is there a way to disable all locking (this is just a reporting database so im not worried about the data integrity. any suggestions to disable all locking? by the way its a sql 2000 server.
example: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED //Select cnttrtydayspast insert into tampa_pmr ( hprop, sCategory, sSubCategory1, sSubCategory2, sSubCategory3, dtMonth, sMonth, sYear, iValue, iSort1, iSort2, iSort3, iSort4, iLevel) (select d.hprop, d.sCategory, d.sSubCategory1, d.sSubCategory2, d.sSubCategory3, d.dtMonth, d.sMonth, d.sYear, d.iValue, d.iSort1, d.iSort2, d.iSort3, d.iSort4, d.iLevel from (select p.hmy hProp, 'Avg Days For Outstanding Receivable' sCategory, 'Number of Households' SSUBCATEGORY1, 'z' SSUBCATEGORY2, 'z' SSUBCATEGORY3, '#begMonth#' DTMONTH, month('#begMonth#') SMONTH, year('#begMonth#') SYEAR, isnull(SUM(isnull(cnt.cntHmy,0)), 0) IVALUE, 9 ISORT1, 80 ISORT2, 0 ISORT3, 0 ISORT4, 2 ILEVEL from property p left join (select amt.property_hmy phmy, count (distinct amt.thmy) cntHmy from ( select p.hmy property_hmy ,t.hmyperson thmy ,tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end)) remain_amount from property p inner join trans tr on(p.hmy = tr.hprop and tr.hAccrualAcct is not null) inner join tenant t on(t.hmyperson = tr.hperson and tr.upostdate < DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))) left outer join detail d on (d.hchkorchg = tr.hmy )
left outer join trans trr on (trr.hmy = d.hinvorrec ) where tr.itype = 7
and datediff("dd",tr.sdateoccurred,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))) <=30 #Conditions# group by p.hmy ,t.hmyperson ,tr.hmy-700000000 ,tr.stotalamount having tr.stotalamount <> sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate)) when 1 then d.samount when 0 then d.samount else 0 end ) AND SIGN (tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end)) - ISNULL(( SELECT /* available prepays */ sum(isnull(d.samount,0)) tran_amountpaid from property p inner join tenant t1 on t1.hproperty = p.hmy inner join unit u on u.hmy = t1.hUnit inner join trans tr on tr.hperson = t1.hmyperson and tr.hmy between 600000000 and 699999999 and tr.upostDate <= '#begmonth#' inner join detail d on d.hinvorrec = tr.hmy inner join acct a on d.hacct = a.hmy and d.hAcct IS NOT NULL left outer join trans trc on trc.hmy = d.hchkorchg Where 1=1 and exists (select detail.hmy from detail left outer join trans trc on trc.hmy = hchkorchg where hinvorrec = tr.hmy and (hchkorchg is null or trc.upostdate > '#begmonth#')) and (d.hchkorchg is null or trc.upostdate > '#begmonth#') AND T1.HMYPERSON = T.HMYPERSON),0)) = 1 ) amt where amt.thmy not in ( select isnull(t.hmyperson,0) thmy from property p inner join trans tr on(p.hmy = tr.hprop and tr.hAccrualAcct is not null) inner join tenant t on(t.hmyperson = tr.hperson and tr.upostdate < DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))) left outer join detail d on (d.hchkorchg = tr.hmy )
left outer join trans trr on (trr.hmy = d.hinvorrec ) where tr.itype = 7
and datediff("dd",tr.sdateoccurred,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))) > 30 #Conditions# group by p.hmy ,t.hmyperson ,tr.hmy-700000000 ,tr.stotalamount having tr.stotalamount <> sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begMonth#')+1, 0))-trr.upostdate)) when 1 then d.samount when 0 then d.samount else 0 end ) AND SIGN (tr.stotalamount - (sum(case sign(convert(INTEGER,DATEAdd(day, -1, DATEAdd(month, DATEDiff(month, 0, '#begmonth#')+1, 0))-trr.upostdate))when 1 then d.samount when 0 then d.samount else 0 end)) - ISNULL(( SELECT /* available prepays */ sum(isnull(d.samount,0)) tran_amountpaid from property p inner join tenant t1 on t1.hproperty = p.hmy inner join unit u on u.hmy = t1.hUnit inner join trans tr on tr.hperson = t1.hmyperson and tr.hmy between 600000000 and 699999999 and tr.upostDate <= '#begmonth#' inner join detail d on d.hinvorrec = tr.hmy inner join acct a on d.hacct = a.hmy and d.hAcct IS NOT NULL left outer join trans trc on trc.hmy = d.hchkorchg Where 1=1 and exists (select detail.hmy from detail left outer join trans trc on trc.hmy = hchkorchg where hinvorrec = tr.hmy and (hchkorchg is null or trc.upostdate > '#begmonth#')) and (d.hchkorchg is null or trc.upostdate > '#begmonth#') AND T1.HMYPERSON = T.HMYPERSON),0)) = 1 ) and amt.remain_amount > 0 group by amt.property_hmy )cnt on cnt.pHmy = p.hmy where 1=1 and p.itype = 3 #Conditions# Group By p.hmy ) d where 1=1) //End cnttrtydayspast
We are about to start a project that will involve three websites, each of which will have its own public domain name and two of which will act as e-commerce applications. Both e-commerce applications will only contain relatively small amounts of data (few thousand products, up to 15000 customer records with basic order history) and will be synchronised with a central data server at another site that will be responsible for handling payments, order processing, invoicing, etc.
The sites will take approx 500 hits every 24 hours and we will need to manage the DNS on the hosting server.
My question is, would we be ok to host these sites on a Web Edition of Windows 2003 Server with SQL Express, or is there any clear reason that we should go with the Standard editions of Windows Server and/or SQL?
Hi there, it has been a long i'm trying to execute Microsoft Association Rules on my database.
I solved memory leak problem now, but i still can't understand output rules.
Database contain all the italian student who took a degree last year. Here in Italy, they have to compile a summary where they speak about universitary experience. ie: they talk about experience with teachers (pointage from 1 to 5); they says if they want to continue in the universitary field or not, and so on.
Most of the rules, says: Int_Stud=1-2, RapDoc>4
Int_Stud is the column where i store student intention to continue university. 1 means they want to go on, 2 means they do not want to continue to study. So, this rules has no sense, because it relates all the student (in my mind): the one who wants to continue university and the one who do not want to.
I think problem is that visual studio 2005 and analysis service has no understanding of Int_Stud world, they've no idea that Int_Stud can have just 2 values and that they're opposite each other. Is there a solution to this problem? Can i discretize this column?
Even if I know not to have perfect english, I hope to be understandable
What is the algorithm that generates the itemsets in the Association model? I'm looking to possibly use this part of the Association algorithm (i.e. the grouping into itemsets) in a separate plug-in algorithm.
Why do association itemsets have probabilities associated with them when its rules that generate probabilities? Any queries I do against my model are using these itemset probabilities rather than the probabilities that the rules generate. More over, the probabilities generated for these itemsets are far less than the MINIMUM_PROBABILITY tag in the algorithm properties menu.
I note that there exist three web viewers for data mining algorithms, namely, DMNaiveBayesViewer, DMDecisionTreeViewer and DMClusterViewer. How come there are no viewers for association rules (itemsets, rules, dependency network)? Can you suggest any alternative way of showing such valuable information in a web application?
I understand Mr. MacLennan's explanation provided at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=282651&SiteID=1 and appreciate the time he took to explain how importance works. However, like the user with username "sang", I also ran the data in BI 2005 and got the same results listed by the aforementioned user. I did this using the following data:
donut muffin
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
y y
n y
n y
n y
n y
n y
etc.
The rule muffin -> donut has an importance of -0.105302438, which is not the same as Mr. MacLennan's results. I tried switching the roles of a and b in a -> b and using different bases on the logarithms. I don't get the result of -0.105302438 with any of these. I also tried to calculate importance with a small data set I have and can't get the results using Mr. MacLennan's explanation with that data set either. Any thoughts on the descrepancy?
I am doing the Market basket analysis for a retailer using association rule. The whole data set is huge which contains grocery, clothes and books etc. If I want to check out the relationship between several different clothes brands, (e.g. LEVI'S and adidas), should I just remove all the grocery and books transactions, use the subset which only contains clothes transactions to re-run the association rules? Is this gonna work?
