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???
Please help I'm a bit lost.
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.
View 3 Replies View RelatedHi all,
I am new to SQL Server 2005 Analysis Services and would like to use the OLAP Cubes as a datasource to build Mining Model . However i would like to use a particular view of the OLAP cube that i have generated to be used as the datasource for the mining model . I find that i am not able to save the Cube View while browsing the OLAP cube in Business Intelligence Studio. Is there a way i can acheive this requirement.
Any ideas regarding this will be really appreciated.
Thanks & Regards,
Satya
Hi,
I plan to use my stored procedures to build the ad-hoc reporting model
Can someone please tell me where to use the OPENROWSET hack mentioned in this post (below) to create a datasource view for building the Report Model.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=345209&SiteID=1
Appreciate your help
Thanks.
Hi,
I removed a role in the datasourceview of a datamodel. Later I removed the role in the reportmodel. There are always two roles for one relation. Now I only have one role left and I can't delete it anymore.
This is the error I get when building the project.
Error 2 The RelatedRoleID property of the Role 'invoicelineid' refers to the Role 'Gd1b03590-f98b-46aa-945d-c90ccd5d6028', which is not in the same SemanticModel as the Role 'invoicelineid'. Max generiek.smdl 0 0
When I try to remove the role I'll get an error : object reference not set to an instance of an object.
If I look at the properties of the role I see that when I open 'related role' there is nothing in it, except an ID: Gd1b03590-f98b-46aa-945d-c90ccd5d6028 and as name: Object reference not set to an instance of an object.
I also tried to remove the whole entity but that resulted in the same error. It's not an option for mee to remove the whole model and autogenerate it again, but there is too much work in it already.
If I try to remove this thing in the xml itself it gives other errors and the model won't load anymore.
I hope somebody has a solution for this.
thanks in advance,
Julian Kooiker
HI
Thanks a lot for your answer
I say my request but I don€™t know why anybody don€™t answer me
I have a project about predicting a value about selling and buying of a good like t-shirts and I use data mining for my project ,so I should use time series algorithm ,that €˜s mean I have previous data about t-shirts for 11 months ago and now I should say for 12th month ,how many t-shirts are sale?
My tables saved on the excel file and it is problem, because how should I use this table for building model?
After building a model and structure and predicting the value of 12th month for this store in the mining model then I use this query in model in bi:
€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
This query showed a column and prediction value.
After all of that now I should show this value in the application so I use c# language for building it, so I use a form in c# then I add a button to form that with clicking on this button, I can connect to my structure and then show process of connecting in the panel, then with clicking on the other button I can use this query
(€œSELECT
PredictTimeSeries(amount)
From
[Forecasting]€?
) and after using I can see prediction value in the textbox, that€˜s mean the value of 12th month show on textbox.
Form has two buttons: one button is for connecting to mining structure and other button is for sending PredictTimeSeries query to structure, one textbox, which is for showing the predicting value for 12th month for selling t-shirts, one panel for viewing the lift chart.
Also you say
You are building an application that programmatically creates mining structure and a model, and then you want to train the model and display some results.
And I don€™t know how can I train my model, I should use a special code for it?
If your answer is say, please explain that and then say that codes are for training.
Please if you can send c# code for Sporadic of stage, please send that, I need to this code; my request is emergency for me.
Thanks a lot
i am very sad because any body don't answer me
Hello,
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
with best regards
Anton Kalcik
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies View RelatedHi Champs
I am importing huge amout of row into a DW, that I then want to build Cubes from.
This process is slow, as SQL insists of concurrently building an index, whil loading.
Is there a possebility to delay the build of Index to After the data is loaded into the DW?
/Many thanks
I am using visual basic 2008
I am making a program, I used sql server compact edition (sdf) (i think it is no more only for mobile device, I am working for desktop application)which i created with the same visual basic. i update data by using table adapters,
when I close the program and build again, the data previosly updated are deleted, and I get empty database?? why is that. do i need to set some copy to.........properties. i have used copy if new.
I have one table Emp_MAster with two column ID-Sup_Id. I need to create a table where i can store data link this
Id-Hreid
Only difference is that I need to store data in an hierarchy view so Emp 1 is reporting to Emp2 and Emp2 is reportign to Emp3 so in new table I should get
Emp3-Emp2
Emp3-Emp1
Emp2-Emp1
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?
View 6 Replies View RelatedI 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] ....
Hi,
I am finding nice examples data model of CMS. Do you know any link to data model phorum or any link to data model examples(nice)?
Thanks
Why would we need to know the data model to write queries?
Sorry but its been a long time since I've worked with databases and I don't remember!
Could anybody lead me in the right direction please?
Why would we need a data model to write queries?
Sorry but its been a loong time since I've worked with databases and I barely remember.
Could anybody lead me in the right direction please?
Thanks.
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 the following scenario,
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.
Thanks Much
Tejinder
Hi,
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.
Any help will be appreciated.
thanks
Hi,
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.
thanks,
RK
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.
Hi, new to this forum. I have a data model that I'm curious about, because it has a structure that's new to me:
TableA -- TableB -- TableD
TableA -- TableC -- TableD
TableB and TableC each model a many-to-many relationship between TableA and TableD.
In addition to being new to me, it might also be the causing us problems with our code generator (a product called .netTiers).
Anyone used this construct before, or suggest an alternative?
Dave
P.S. Data model is attached
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
View 4 Replies View RelatedI 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.
Thanks for you help
Ryan Swann
Hi,
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.
Thanks,
Sri
I followed the tutorial posted at [URL] ...
Everything was ok until the last step where I had to process the mining structure which resulted in a warning
"Informational (Data mining): Decision Trees found no splits for model, Tbl Decision Tree Example."
What does this error mean? How do I resolve it? Also, I only see the first level in the Mining Model Viewer, I don't see the levels 2 and 3.
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?
Thanks in advance for any insight.
Hi all,
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
View 3 Replies View RelatedDoes 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
View 1 Replies View RelatedI 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?
View 3 Replies View Related