Problem with central subscriber model

Hi,

I am trying to acheive the central subscriber model for Sql server 2005 replication. I have two publishers and the centrol subscriber. Both publishers are Sql server 2005 Workgroup edition. The following table shows the Software I have used for the test.

SQL version

Service pack

HotFix

Publishers

SQL Server 2005 Workgroup edition

2

1

Subscriber

SQL Server 2005 Enterprise edition

2

1

I have encountered an obstacle in the replication process to achieve the Central subscriber model. I have configuration table, It exists on servers TB1 and TB2 and want to amalgamate the data to a central subscriber. There are two duplicate primary key and overlapping values existing across the 2 publisher tables. These records need to be updated during the process of replication.

TB1

Guid Name CreatDate EditDate

34a.. TEST02F 3/6/2007 3/6/2007

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

TB2

Guid Name CreatDate EditDate

d008.. MS060F 7/20/2005 11/21/2006

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

Normally, the default Article properties settings are used 'DROP existing table and re-create it', however here we don't want this setting, because the one table records will then be removed, leaving just the data from the other table. So, we modify the article properties on the snapshot tab to 'Keep the existing table unchanged'’. As result we got the PK violation error.

I have tried using -SkipError option in distribution job Replication Agent Profiles, that did not work.

Thank you for your help

Where are you getting this error? Are you getting it while the snapshot is being applied?

If so you need to create a consistent subscription which represents the data in all publishers with duplicates removed. Most people will have a location identifier in all publishers and then filter their publication on this filter (the delete all data which matches the row filter option in the name conflicts section of the snapshot tab).

If you are getting this error during the application of new commands you need to consider how unique your primary key is. GUIDs are pretty well guaranteed to be unique so I don't really understand how this could happen. You could modify your insert proc to do an existence check before it inserts the values.

|||The snapshot is going to take the contents of publisher1 and stick them into the tables on the subscriber. It will then take the contenst of publisher2 and stick that data into the same set of tables. There isn't any error with replication in this. If you can't take a table from publisher1 and a table from publisher2, smash them into a single table, and not get a primary key conflict, replication can not overcome this. A central subscriber is designed to aggregate data from 1 - N publishers into a single set of tables. That data still needs to obey the rules you set up on the tables such as PK. So, the only way this works is to ensure that you aren't trying to send anything in that causes a PK violation.sql

Problem when creating a new named calculation

I have a Customer dimension in my model, and now I want to count the # unique customers in one of my fact tables.

Therefore I created a new named calculation in the fact table which does a count distinct of the customerkey in the fact table.

And when I try to add a new report model i get the following error message, even when I don't select anything in the Select report model generation rules screen:

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Command:
SELECT
COUNT([DEPARTURE]),
COUNT(DISTINCT [DEPARTURE]),
COUNT((SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)),
COUNT(DISTINCT (SELECT
DISTINCT(
COUNT(CLIENTKEY)) AS Number_of_Persons FROM
FCT_TABLE
FCT_TABLE)),
COUNT(Arrival),
COUNT(DISTINCT Arrival)
FROM [dbo].[FCT_TABLE] t

BUTTONS:

OK

What should I do?

As I understand you have defined one of named calculations as

(SELECT DISTINCT(COUNT(CLIENTKEY)) AS Number_of_Persons
FROM
FCT_TABLE AS FCT_TABLE)

Yes, this named calculation will not work in some cases. But it will work if you push it into a named query. Redefine you FCT_TABLE table in DSV editor as a named query:

SELECT
*,
(SELECT DISTINCT(COUNT(CLIENTKEY)) FROM FCT_TABLE) AS Number_of_Persons
FROM FCT_TABLE

Problem using SQL Server BI Development Studio

Hello

I'm using SQL Server BI Development Studio to create a mining structure to forecast time series analysis.
The process to create a model for time series has these steps:

Create a connection to the Database;

Create a source view. Create relationships between tables using the foreign keys;

Create the model with the mining structure. Define the fields that I won’t to predict and the algorithm properties aren’t changed.

Is something wrong in these steps, I missing something?

Other problem is the utilization of the model created earlier. In some tests that I made, the models don’t do forecasting upper then 20 steps. The problem could be the number of lines? But other data set with a large number of null values doesn’t have any problem predicting more than 20 steps using the time series algorithm.

Can someone help me?

Thanks in advance.

Jo?o Santos

<!--[if !vml]--><!--[endif]-->

I don't think there is a problem. The nature of the time series algorithm we use - Auto Regression Trees with Cross Predict (ARTXP) - is that predictions in the future can become unstable, since predictions become dependent on predictions. When we detect this instability, we stop producing output - this behavior can not be changed.

The dataset with large numbers of nulls is using some sort of missing value substitution which actually smooths the series - although you will have to determine the accuracy (usually more data = better).

We are considering allowing users to control the "instability" threshold in future versions.

|||The time series algorithm has a mechanism for detecting instability in predictions and stops returning predicted values when the variance exceeds a certain threshold. The point at which this happens is data-dependent - this explains the behavior you're seeing.