Hi folks. I'm architecting a data warehouse for the first time and I am wondering what the best logical and physical designs for the datamarts would be. I understand that datamarts are sets of views specific to the needs of a particular user or type of user. I am just wondering whether the best way to deal with datamarts would be to define them as schemas on the database that holds the data warehouse (easy peasy from an implementation standpoint but not great for performance if we get lots of users), or whether I should investigate setting up subscriptions (I have no idea how difficult subscriptions are to implement but I imagine that it would lead to superior performance if we get to the point where we have hundreds of simultaneous users and we want to move the snapshots to additional OLAP servers).
I'm doing a top-down design since our customer wants to be able to do ad-hoc queries on anything that the operational databases might be holding. The initial design will be an offline datawarehouse but it may evolve to to a realtime or near-realtime datawarehouse as requirements are refined. There may be some two way integration on one of the applications
Another question I have has to do with indexing the views. I know that a view must be schemabound and must only reference other objects in the same schema in order to be indexed. How much of a performance boost would I get my using indexed views as opposed to non-indexed views? Is it worth the extra space required to make copies of the tables on all the schemas that will be using them? If I go with subscriptions instead, would that make indexing the views a moot point since I could just use filtered tables instead?
I have scaled out an application so that multiple servers contain identical production databases (various clients use different server databases.)
I have replication configured so that the same tables from these different databases are published into single subscription tables (to provide data warehouse and reporting across all production databases). The publications are set up to replicate ddl.
Everything works wonderfully except I have just discovered the need to alter the schema of a table that is within the production databases.
When I apply a script to change the ddl in all the publication databases I am getting errors in replication. I understand that I am NOT supposed to change an uderlying subscription table (this should be done through replication of the schema.) I suspect that the replication error is caused when the schema changes are replicated from the very first Publication update. What I'm effectively doing is changing the subscription table independently (and prior to) changing the schema of the OTHER (subsequent) publication table schemas. I experimented and unsubscribed my target table from all but ONE publication, and then my schema change is fine and replication is happy. But when I have multiple publications feeding the subscription I am not able to propagate the schema change across replicating publications without breaking replication. I am sure that other people out there must have similar situations as mine, where multiple publishers update a single subscription. How are you able to update schema successfully? Many thanks in advance, Normajean P.S. I have posted the script that I am running below....
And here is the error I get in Replication Monitor when I try to run the script: The index 'Idx_FacilityStayPayer_facStayID_PayerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074) Get help: http://help/5074 The index 'Idx_FacilityStayPayer_payerID' is dependent on column 'payerID'. (Source: MSSQLServer, Error number: 5074) Get help: http://help/5074 ALTER TABLE ALTER COLUMN payerID failed because one or more objects access this column. (Source: MSSQLServer, Error number: 4922)
------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ -- make the facilityStayPayer.payerID column non nullable
-- drop all of the foreignKeys and indexes that reference the column IF EXISTS (select constraint_name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name = 'facilityStayPayer' and constraint_name = 'FK_FacilityStayPayer_Payer') BEGIN ALTER TABLE dbo.FacilityStayPayer DROP CONSTRAINT FK_FacilityStayPayer_Payer END
GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_payerID') BEGIN DROP INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF ) END GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'Idx_FacilityStayPayer_facStayID_PayerID') BEGIN DROP INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] WITH ( ONLINE = OFF ) END GO IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[FacilityStayPayer]') AND name = N'UNQ_FacilityStayPayer') BEGIN ALTER TABLE [dbo].[FacilityStayPayer] DROP CONSTRAINT [UNQ_FacilityStayPayer] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN -- alter the column ALTER TABLE FacilityStayPayer ALTER COLUMN payerID UNIQUEIDENTIFIER not null END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN
-- add all of the indexes and foreign keys back ALTER TABLE dbo.FacilityStayPayer WITH NOCHECK ADD CONSTRAINT FK_FacilityStayPayer_Payer FOREIGN KEY (payerID) REFERENCES dbo.Payer(payerID) ON UPDATE NO ACTION ON DELETE NO ACTION END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN
CREATE NONCLUSTERED INDEX [Idx_FacilityStayPayer_payerID] ON [dbo].[FacilityStayPayer] ([payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN CREATE UNIQUE NONCLUSTERED INDEX [Idx_FacilityStayPayer_facStayID_PayerID] ON [dbo].[FacilityStayPayer] ([facStayID] ASC, [payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
IF EXISTS (select name from sys.objects where name = 'FacilityStayPayer' and type = 'u') BEGIN ALTER TABLE [dbo].[FacilityStayPayer] ADD CONSTRAINT [UNQ_FacilityStayPayer] UNIQUE NONCLUSTERED ([facStayID] ASC, [payerID] ASC) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] END GO
It seems to me that if a scheduled SSRS report subscription fails ( Status Message - An error has occurred during report processing) , that I actually need to delete the subscription and reconfigure it from scratch. The scheduled job doesn't try to run again automatically (say the next Monday on a weekly Monday schedule).
Is there a way to "reset" a failed subscription without have to recreate the entire subscription?
Existing subscription already contains table included in the new subscription.
What are the possible causes of this merge replication error? Could it be caused by a SQL Server Compact Edition User trying to sync their .sdf file after their subscription has already expired on the SQL Server?
Would you expect to see a different message if a SQL Server Compact Edition user tried to sync a subscriber database (.sdf file) with merge replication if it's been longer than the subscription retention period since their last sync?
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
We are migrating our reporting services database from one server to another.
What needs to be done to subscriptions to ensure they work properly. Will they automatically update as part of the migration, or is there a manual process.
We are using Reporting Services 2000 and will be using Reporting services 2000 as well on the new server.
Hi all, I'm creating data driven subscription for parameterized reports. I'm supposed to deliver one report to six different customers. But each customer can see only his own data in the report. This is not a problem. But the problem is I need another report in which all the data can be seen. When I create DD subsription, I chosed Auto increment option for the write mode under "
Specify delivery extension settings for Report Server FileShare" . But then it creates seperate report for each parameter value. But it does not create the report which contains all the data for all parameter values. How can I make it without creating another report. Also when I use overwrite option instead of Auto Increment, It creates only one reports. Does any one know why it happens?
Hi, I am doing a repl.in SQL7/NT. At time of 'Push Subscription wizard' - I used the option "Yes,Initialize the schema and data......" whereas I already had data and schema at the subscriber. Now my question is what will happen now and how long will this process take(for approx5GB of DAta). OR should I stop the process - it's already an hour when I started the process. Any suggestion appreciated.
I have created a subscription pull from one SQL server to another so that the database from one SQL server is replicated to another. But when the subscription runs, no data appears in the subscriber SQL server. When I check the subscription, I see that the database is connecting to the publisher but that is it. I have the login correct but what am I missing? Do the tables in the subscriber database have to be already created and matching the ones in the publisher database before the subscription runs or are they automaticaly created after the subscription runs?
I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help.
The information in the database for the report is as follows
Because of problems trying to alter databases used for replication my software would need to find out if a database is a publisher or was repliated (using T-SQL). Is this possible?
I want to use window script program to execute a pull subscription installed on SQL 2005 express edition. Because it free for downloand. is there a script that can call an existing pull subscription execution.
is there anyone know how to drop subscription from the publisher database without a connection to the subscriber(because the subscriber server is formated)
I have uploaded a report onto my 2005 Reporting Manager and want to create a daily email of the report... How do I set up the email facility? Can someone give me a link? I not sure what to do because the company uses Exchange Server and not SMTP....
I need to subscribe one report in Reporting Services. I was compiled all fields and I said to report server that I want excel file at one shared directory of the server. When the report server tried to process the report I have the following messagge:
Failure writing file S16 : An unexpected error occurred in Report Processing.
The current action cannot be completed because the user data source credentials that are required to execute this report are not stored in the report server database. (rsInvalidDataSourceCredentialSetting)
Delievered by: Report Server E-Mail SMTP: is enabled on IIS Reporting Services Configuration Manager:
Email Setting: setup correctly
What else is it that I would need to check to get the subscriptions going?
We are seeing a problem with the users of our application. If the user closes our application from running programs (as best as we can tell as we cannot repro in dev) and SQL Mobile is in the middle of an async sync, sometimes when the application restarts it fails to load the properties for replication from the database. We are using code that is nearly identical to the samples provided by MS for performing replication async. Basically, the database still exists on the device and the application can use it just fine, but when a sync starts, the database is already there, so it tries to load the properties at which point we get a message stating that there is no subscription in the database.
Any ideas?
The second question would be if it doesn't exist and the database already contains data, can I create the subscription and have it not loose the users changes? Will it sync correctly after that?
hi. i was following the newbie's guide: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnppcgen/html/med302_msdn_sql_mobile.asp?frame=true
on the creating a new subscription i was doing okay until i get to the last part wherein i click finish and this error pops up when it starts synchronizing data..
An error has occured on the computer running IIS. Try restarting the IIS server.
i already tried restarting the server.. is there anything else i can do to make it work?
I have setup Merge replication with 2 publishers and one subscriber. On the subscription machine I can see publisher 1 listed under local subscriptions but not publisher 2. I have created the second subscription on publisher 2 and also tried to create the subscription again on the subscription machine but i get an error saying the 'Subscription already exists'.
Neither do I see the second subscription nor does it work as if it is subscribed to Publisher 2. What could be going wrong.
I know how to schedule a report to generate and email somebody weekly on a particular day of the week but how do you tell it to run for previous date range? For example to run on Thursday Sept.13, I would want it include data from Start date of Sept 3 and end date Sept 9.
I have attempted this with more than one database (SQL Server 2005). The snapshot was created successfully. The Web configuration was completed successfully. All my security logins (DB and authentication) have the correct permissions and I have followed Microsoft's instructions perfectly (over and over again!).
I am using a MS Server2003 R2 box, Service Pack 1. All of my Mobile 5.0 components installed successfully.
This is a data formatting issue, but I am attempting with an empty table (3 fields) to make it as simple as possible. In fact, the first time through the instructions I used their SQLMobile database created with Microsoft's script. Same ending, same issue. I cannot create a SQL Server Mobile subscription.
I should mention that this error occurs on the 2nd task "Synchronizing Data".
What URL should be tested prior to subscribing - HTTP://MyBox/MyAppName? What should be the expected results? Right now, I get a page does not exist - of course, there are no pages, just the dll in the virtual directory.
I have Sharepoint Central Administration and Team Foundation Server web sites running. In addition,
Hello, I have spend 2 days already in search how to fix my query notification problem. Here is the case :
I run very very basic query like "SELECT name FROM dbo.author" with SqlNotificationRequest and I monitor events in SQL profiler. On Audit login I have :
-- network protocol: TCP/IP set quoted_identifier on set arithabort on set numeric_roundabort off set ansi_warnings on set ansi_padding on set ansi_nulls on set concat_null_yields_null on set cursor_close_on_commit off set implicit_transactions off set language us_english set dateformat mdy set datefirst 7 set transaction isolation level read committed
I understand my subscription fails but the reason as given in msdn is :
"The connection options were not set appropriately when the command was submitted"
You can see that my options are exactly as stated in MSDN. I'm stuck here and don't know what more to check in order to resolve this. If anyone can point me in some direction what I can check for this error will be highly appreciated. Thx in advance
I need to test a database solution (many tables) all indexes, ref. keys, because i get this error when I trying to create a new subscription. tru internet.
"No key matching the described characteristics could be found within the current range ".
I need somekind of program that checks data integrity. or what is this?
HELP!!! this issue is starting to bee very IMPORTANT to get some solution. clients is waiting to update database!
Is this the couse:
added new tables
added new columns
added new foreign keys
when replication was online??
Or have the DBMail something to do with this? I have seen in log files that mail is somehow connected.