I have been given the assignment to come up with an Entity Relationship diagram of an existing database. The database wasn't designed by me. I was wondering if there is a standard scientific way of displaying the relationships between the various tables at a functional level on Visio. Since, I didn't design the database in the first place, I don't know how to take a step backward and recreate the ERD from the given database.
I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
I've question about how to handle structural datamodel changes in a datasource of PowerPivot. Suppose I'm developing a starmodel in SQL Server and sometimes a datatype changes or a name of a field changes in a table. It seems to me that PowerPivot handle this not gracefully as Analysis MD does (mostly). I received an error because of a wrong fieldname or even no error when a dattype changes in PowerPivot. Is this common or do I something wrong here. Does this mean that every time the datamodel changes the PowerPivot should be recreated? Or am I missing the clue here?
I am getting ErrorCode 8 while loading the data from stage to model. I have checked my error view it states that "Member Code is Inactive".
Initially I have loaded same set of data in Model from MDS Stage table but then deleted with ImportType = 5 which removed all the data from the MDM model.
Now i want to load it back but its giving the Error Code 8 .. Before loading the same data i have changed the stage table Importtype to 2 and Importstatusid to 0.
I don't know if the question has been nailed down. Aside from deleting tables, can we delete the *content* of data within the tables. It doesn't seem crazy that, if you can pull in data from a feed then you should be able to remove the content out again (without also destroying the user's meta-data work ). Reasons for this include:
- Security (a user may not have rights to see *my* data and should go refresh their own) - Size (workbook doesn't need to have GB's of irrelevant data saved to disk in a workbook if it was just useful during development phase to a pre-production data feed) - Bad data (pre-production data feed is not good data) - User-friendliness (data feed was refreshed 2 years ago and workbook was saved to file server. Users shouldn't be presented with irrelevant data, but should get empty pivot tables until they go do their refresh)
Obviously Excel internally knows how to clear out PowerPivot data, given the prompt shown here: [URL] ....
But how does a user initiate this on their own (corruption aside)?
Previous time this question was asked, without a real resolution: [URL] ....
I have a package which loads data from a flat file (csv) to 4 tables in a database. Now, the load is incremental.
I want to clear the data of all 4 tables(in the database) before loading the data from flat file everytime.How can i do this? Iam using 4 Oledb Destinations, 1 multicast, 1 source component to do this. Also can it happen like a transaction? because if it deletes the existing data and couldnt load new data there will be a problem!.how to avoid this?
I need to develop a language specific dwh, meaning that descriptions of products are available from a SAP system in multiple languages. English is the most important language and that is the standard. But, there are also requirements of countries that wants productdescriptions in their language.Â
Productnr Productdesc Language 1       product    EN 1       produkt    DE
One option is to column the descriptions, but that is not very elegantly. I was thinking of using bridge tables to model this but you have to always select a language in a filter (I think)..
I'm thinking of a technical solution, such that when a user logs on, the language is determined and a view determines whether to pick a certain product table specific for a certain language. But then I don't have the opportunity to interchange the different language specific fields in a report (or in my case PowerPivot).
We have our Production server having database on which Few DTS packages execute every night. Most of them have Bulk Insert stored procedures running.
SO we have to set Recovery Model of the database to simple for that period of time, otherwise it will blow up our logs.
Is there any way we can set up log shipping between our production and standby server, but pause it for some time, set recovery model of primary db to simple, execute DTS Bulk Insert Jobs, Bring it Back to Full recovery Model AND finally bring back Log SHipping.
It it possible, if yes how can we achieve this.
If not what could be another DR solution in this scenario.
I went to look at the connection string previously entered for a dataset created in a new report, and am not seeing anything intuitive for bringing up the associated datasource dialog box that was used to enter name, type and connection string. I'm also noticing nothing intuitive for deleting an existing dataset. How do you do these two very simple things in an existing project? I dont see the dataset in solution explorer, I see it only in the text box on the data tab and in a limited kind of way on the dataset view where the columns show and maint is allowed mostly on the columns only. I tried hilighting the dataset here and hitting the delete key to no avail.
I would like to know best way to design the database for the following requirement. I have a collection of tree nodes. each node has a type and set of attributes and a parent node (except for the node which has no parent). node type refers to the level of the node in the tree. child node inherits the attributes from the parent node (similar to object oriented programming where derived class inherits properties of the base class). user can add/update/delete nodes from the tree and user can choose to override the attributes of the parent node in child node. what is best way to store this type of data? should there be a separate table for each node type (level in the tree). but the problem with this approach is that we need to duplicate the columns of the parent node, because user can overwrite the parent node attributes in the child node. there can be more than one at the same level and all of them share same set of attributes. this concept is exactly like inheritance in object oriented programming. as far as the data is concerned, there are around 15 levels, around 30K nodes and 30 attributes spread across different node levels.
This may be a noob question... but I am not able to create a data model and its frustrating me. :)
I made the Data Source... I made the Data Source View (just grabbed a table) and made the report model... but the model is empty >< When it autogenerated the model, it did yell at me about the table not having a primary key... do all the tables have to have primary keys???
I am trying to do the examples using SQL Server 2005 Reporting Services (2006) by Brian Larson. The example says to use GDS as the data source yet all I see is Adventure Works. The tables for AW are not the same as the tables for GDS.
Is there something I need to do or does the DBA need to install something else.
I would like to restore database using RESTORE DATABASE ... REPLACE command. If database exists already and has any open connections this command will fail. I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command. I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.
How would I select from TABLE A and add this data to TABLE B which has existing data that I want to keep. I would like to keep adding onto the old data. When I do an "Insert Into", it keeps on deleting my old data.
I am new to sql server 2000, and need a little help.
I have a table called CMRC_Products with various columns, there is one column called Product Images that has the name of every image in my catalog over 4000, I want to add to each row in that particular column .jpg without loosing what is already in there
I have tried:
UPDATE CMRC_Products SET ProductImage = ProductImage&' .jpg'
I need to develop an internal messaging sub-system that is similar toa web mail application but without SMTP support (e.g message routesare confined to the webapp domain). The requirements are rathersimple: Each user (e.g mailbox) can view incoming messages and hisoutgoing messages. Message quota is defined as the sum of all incomingand outgoing messages per userand tracked in the users' row (Users table – log_TotalMessages). Thequota is enforced by the business logic layer and not by the DB.I am considering the following data model for the storage component,and would appreciate community feedback:Table layout for incoming and outgoing messages************************************************CREATE TABLE [dbo].[Messages] ([MessageID] [int] IDENTITY (1, 1) NOT NULL , // The messageID[RecipientID] [int] NOT NULL , // The userid ('Users'Table)[SenderID] [int] NOT NULL , // The userid ('Users'Table)[GroupID] [uniqueidentifier] NULL , // Only assigned if theuser "replyed" to an incoming message[SubmitDate] [smalldatetime] NOT NULL , // the date of themessage[DeleteBySender] [bit] NOT NULL , // Since I want to maintain onlyone copy of each message I mark a message "to be deleted" and deleteonly if both are true.[DeleteByRecipient] [bit] NOT NULL ,[SeenByRecipient] [bit] NOT NULL , // Used to "highlight" unreadmessages[Subject] [tinyint] NOT NULL , // Subject is derived from a fixedlist[MessageText] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_ASNOT NULL) ON [PRIMARY]CREATE INDEX [Messages_RecipientID_IDX] ON[dbo].[Messages]([RecipientID]) ON [PRIMARY]CREATE INDEX [Messages_SenderID_IDX] ON [dbo].[Messages]([SenderID])ON [PRIMARY]/* Send Message */CREATE PROCEDURE SendMessage (@IN_RecipientID int,@IN_SenderID int,@IN_GroupID uniqueidentifier,@IN_Subject tinyint,@IN_MessageText varchar(2000),@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION SendMessageTransINSERT INTO Messages(RecipientID,SenderID,GroupID,SubmitDate,Subject,MessageText)VALUES (@IN_RecipientID,@IN_SenderID,@IN_GroupID,GETDate(),@IN_Subject,@IN_MessageText)UPDATE UsersSET log_NumberOfNewMessages = log_NumberOfNewMessages + 1WHERE usr_AccountNo = @IN_RecipientIDUPDATE UsersSET log_TotalMessages = log_TotalMessages + 1WHERE usr_AccountNo = @IN_SenderIDSAVE TRANSACTION SendMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION SendMessageTransENDELSEBEGINCOMMIT TRANSACTION SendMessageTransEND/* ReadMessage */CREATE PROCEDURE ReadMessage (@IN_MessageID int,@IN_RecipientID int,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION ReadMessageTransSELECT MessageText FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Messages SET SeenByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_NumberOfNewMessages =log_NumberOfNewMessages - 1 WHERE usr_AccountNo = @IN_RecipientIDSAVE TRANSACTION ReadMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION ReadMessageTransENDELSEBEGINCOMMIT TRANSACTION ReadMessageTransEND/* Delete Message */CREATE PROCEDURE DeleteMessage (@IN_MessageID int,@IN_DeleteIncomingMessage bit,@IN_DeleteOutgoingMessage bit,@OUT_ERRCODE tinyint OUTPUT)ASBEGIN TRANSACTION DeleteMessageTransDECLARE @Recipient intDECLARE @Sender intSET @Recipient = (SELECT RecipientID FROM Messages WHERE MessageID =@IN_MessageID)SET @Sender = (SELECT SenderID FROM Messages WHERE MessageID =@IN_MessageID)IF (@IN_DeleteIncomingMessage = 1)BEGINIF((SELECT DeleteBySender FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDELSEBEGINUPDATE Messages SET DeleteByRecipient = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @RecipientENDENDIF (@IN_DeleteOutgoingMessage = 1)BEGINIF((SELECT DeleteByRecipient FROM Messages WHERE MessageID =@IN_MessageID) = 1)BEGINDELETE FROM Messages WHERE MessageID = @IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDELSEBEGINUPDATE Messages SET DeleteBySender = 1 WHERE MessageID =@IN_MessageIDUPDATE Users SET log_TotalMessages = log_TotalMessages - 1WHERE usr_AccountNo = @SenderENDENDSAVE TRANSACTION DeleteMessageTransSET @OUT_ERRCODE = @@errorIF (@@error <> 0)BEGINROLLBACK TRANSACTION DeleteMessageTransENDELSEBEGINCOMMIT TRANSACTION DeleteMessageTransEND/* ListIncomingMessages */CREATE PROCEDURE ListIncomingMessages (@IN_RecipientID int)ASSELECT SenderID, MessageID, SubmitDate FROM Messages WHERE RecipientID= @IN_RecipientID AND DeleteByRecipient = 0 ORDER BY SubmitDate DESC/* ListOutgoingMessages */CREATE PROCEDURE ListOutgoingMessages (@IN_SenderID int)ASSELECT RecipientID, MessageID, SubmitDate FROM Messages WHERE SenderID= @IN_SenderID AND DeleteBySender = 0 ORDER BY SubmitDate DESCThanks in advance!-Itai
I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.
Is there a documented work around or will I need to set a primary key on each table?
I am trying to applying an data filter that will filter out user based on userid
Three tables:
tblPerson (fact table): contains over 1 million records with about 20 fields. One of the fields is called BureauID (int). BureauID indicates what Bureau the person works in for an given record.
tblBureau (attribute table) which has a FK relationship to tblPerson) contains the following fields:
BureauID int - this is the linked field to tbl person Bureau Code varchar Bureau ShortName varchar
The reason we use tblBureau as in attribute table instead of place the bureau code and short name directly into table Person is so that we index tbl person quickly with "ints".
tblDataAccess, which has two fields loginID with bureau Code. The bureau code in this table tells me what bureau that userid has access to.
How do i get the report model to filter based on this userid????. I know i have to add some data filters but i am not sure where.
I put an filter on DataAccess so that login = getuserid().
But what do i do to table person or table bureau, so that a person can only see the people in there bureau.
I created a report model using reporting services, deployed the model locally and then built a report based on that model. The report works fine, but any data updates don't show up, seems like data is cached. I bounced reporting services, cleared cached tables in ReportServerTempDB but of no use. Has anybody faced this problem? How can I get updated data for reports built through a report model. Any help is appreciated.
I am building RDA process between SQL server and SQL server CE. Since when we use the method 'SqlCeRemoteDataAccess.pull()', the data will be pulled down from SQL server to CE server and a new table is created with some addtional columns. As I only intend to pull the data and never push it back to SQL server, if I pull the data down for the second time, there will be an error saying that table is already existing. I wonder whether I can pull the data to an existing table everytime I do the RDA?
I have an opportunity to rebuild a database model with the express purpose of improving query performance. So given the following I have a few questions.
Table A (~500M records) Primary Key Field (int) Field 1 (varchar) Field 2 (varchar) Field 3 (varchar) Field 4 (varchar) Field 5 (varchar)
Table B (1B+ records) Primary Key Field (int) Foreign Key Field (int) Field 1 (varchar) Field 2 (varchar) Field 3 (varchar) Field 4 (varchar) Field 5 (varchar)
* Assumed: Tables are inner joined on all queries. The database is readonly.
-- Most of my lookups are based on querying Field 1 of Table A. The data content of Field 1, Table A is 90% unique. 1) Would it be more beneficial to put the clustered index on Field 1 instead of the PK field in Table A? 2) Can an Identity column be non-clustered? 3) Alternatively, would it be beneficial to build a separate lookup table with just the PK & Field 1 of Table A, with a clustered index on the lookup table Field 1 which I join on Table A? (did that make sense?)
-- I have a secondary lookup that performs queries on Fields 1, 2, 3, 4 & 5 of Table B 1) Would it be more beneficial to create an additional indexed lookup column of the concantenated values of Fields 1-5 of Table B versus a covering index of all 5 columns? 2) Does a clustered index have to be unique? 3) Would a clustered index be more beneficial over Fields 1-5 or the special lookup column versus the PK or FK fields? 4) Would creating a special lookup table with just the requisite fields be more beneficial?
An extra question. The existing data model uses the CHAR datatype for all columns less than 9 characters wide and the columns are set to allow nulls. This requires every select statement to COALESCE() and RTRIM() all these columns. I intend to make all (affected) columns VARCHAR, NOT NULL with a default value of a 0-length string. Will this enhance query performance?
I'm going through a very tedious process of documenting tables and fields for a third party database, with an eye toward creating lots of SSRS Reports. I'm making progress, but I was wondering if there's a slick way to export all the tables, fields and key relationships.
I am having data model diagram containing 150 odd tables in MS Office Visio 2007.The Datamodel Diagram even has the Relationships between the Tables.
Do we have any Procedure to export the Datamodel Diagram from the Visio to the Sql Server carrying all the relationships.
We are having the reverse engineer procedure to export database diagrams from Sql server to the datamodel diagram in the Visio in the same way, do we have any procedure to Export DataModel diagram from Visio to SQL Server to create tables with all the relationships?
I first got this map from SQL Server Magazine subscription. Now isee it's available to download as a PDF from Microsoft.http://www.microsoft.com/downloads/...&displaylang=enI've just installed Visual Studio Team Edition for Database Professionalsand for the next coming weeks i'll get a chance to see what kind of datamodel design capabilities the product offers.My question would be do you know how I can make our databasemodel look cool like this map? Would you know if this is a third partysoftware that does this with nice colors and all?Do you think I can do similar quality data model map using VSTE forDB Pro?Thank you
Does anyone know where to start if I want an exported copy of homeappliances, cars, cell phones etc.I just need to populate my database and I want to get some data feedfrom all the different types of manufactures.I think there are services out there. But I have not seen one.I want a CSV file that has all the make/models for lets say: TVs
I have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?
When I attempt to generate a datasource model I get the following error messages: ------------------------------------------------ More than one item in the Entity 'Customer' has the name 'Customer Merge Custs'. Item names must be unique among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Customer' has the name 'Customer Merge Custs'. Field names must be unique within an Entity. (DuplicateFieldName)
More than one item in the Entity 'Pricing Service Layout Detail' has the name 'Pricing Service Extensions'. Item names must be unique among immediate siblings. (DuplicateItemName)
More than one Field in the Entity 'Pricing Service Layout Detail' has the name 'Pricing Service Extensions'. Field names must be unique within an Entity. (DuplicateFieldName) ---------------------------------------------------
Examining any of the above tables in SQL Server Management Studio does not reveal any duplicate column names. In fact, 'Customer_Merge_Custs' does not appear to be a column in 'Customer' nor does 'Pricing_Service_Extensions' appear in 'Pricing_Service_Layout_Detail'.
As an experiment, deleting the table 'Pricing_Service_Extensions' and regenerating did make the two associated messages go away.
