Can someone please explain this statement: At the logical level where there can be any number of entities in a relationship while physically you define relationships between two tables.
thx,
Kat
Are there any pitfalls when naming SQL Server 7.0 objects with underscores, caps, etc.?
View 1 Replies View RelatedHello everybody,
I'm new to Sql.
Can anyone explain me
Data Modeling.
Thanks.
shiak bee.
I am having a problem when modeling a Foreign Key in an "Operations" table. This table holds all information on customers ´s applications and withdrawals.
Here is the structure:
CustomerID int, SourceID int, Value decimal (16,2), OperationDate datetime
Well the problem is that SourceID sometimes might be NULL depending on how the record was inserted. So its kind of cumbersome to define it as an FK, since it can be null...To get things worse, this SourceID might point to more than 1 table (depending on the CustomerType it will point to SourceA table or SourceB table)...
How should this be modeled?
Hi,
does anyone know of any data modeling tools that support or intend to support SQL 7.0 ?
I`d appreciate any comments ...
Kris Klasen
Act. Manager Data Warehouse Project
Information Management Branch
Department of Education, Training, Community and Cultural Development
E-mail: Kris.Klasen@Central.Tased.Edu.Au
http://www.tased.edu.au
Tel: 03 6233 6900
Fax: 03 6233 6969
73 Murray Street
2nd Floor
Hobart 7000
Tasmania
is there a link in this forum that speks about datamodeling or datawarehouse. iam looking for some help regarding the model that i have to build. i am not able to find a relation between different dimensions in terms of time which would be th e key to populate the fact table.
any help appreciated
I'm facing the next problem:I have a table with two columns (among others) modeling category andsubcategory data for each row. I need to summarize info on this twocolumns, but with the next specs:1.- Some grouping is only on the category column.2.- Some other grouping consider the two columns.The values for the two columns come from external source, i.e. I haveno means to know the precise universe of data (I suppose soon or laterwe'll have a sufficient sample of data, but for now it's not thecase). So, I would like to have a grouping table so it's not necessaryto insert a row for every pair of category and subcategory (althoughit would be the best approach for the sake of design's simplicity). AsI don't know every possible combination, I would prefer something like'this category is a - no matter the subcategory', and 'this othercategory + subcategory is b'. Let's go with a sample:--------------------------------------------------------Create Table B ( -- groupings ----categ char(8),subcateg char(5),what_group char(10))-- All rows with 432 code are cat. A ----Insert B ( '00000432', ' ', 'Category A' )-- All rows with 636 code are cat. C except when subcat is 8552 (cat.B) ----Insert B ( '00000636', '08552', 'Category B' )Insert B ( '00000636', ' ', 'Category C' )-- Some data ----Create Table A ( -- data ----categ char(8),subcateg char(5))Insert A ( '00000432', '01322' )Insert A ( '00000432', '01222' )Insert A ( '00000432', '01100' )Insert A ( '00000432', ' ' )Insert A ( '00000636', '08552' )Insert A ( '00000636', '08552' )Insert A ( '00000636', '01100' )Insert A ( '00000636', ' ' )Insert A ( '00000636', '01111' )-- The query like:Select b.what_group, count(*) as cntFrom aLeft Join bOn /* ? ? ? ? */-- Should give ---what_group cnt-------------- ----------Category A 4Category B 2Category C 3-------------------------------------------------------------------It would be easier knowing all the pairs categ - subcateg. If I don'tknow them, is a good idea to model the grouping table as I've donewith rows in B?TIA,DiegoBcn, Spain
View 1 Replies View RelatedIs there a good approach to modelling many heterogeneous entity typeswith that have some attributes in common?Say I have entities "employees" which share some attibutes (e.g.firstname, lastname, dateofbirth) but some subsets of employees (e.g.physicians, janitors, nurses, ambulance drivers) may have additionalattributes that do not apply to all employees. Physicians may haveattributes specialty and date of board certification, ambulancedrivers may have a drivers license id, janitors may havepreferredbroomtype and so on.There are many employee subtypes and more can be dynamically addedafter the application is deployed so it's obviously no good to keepadding attributes to the employees table because most attributes willbe NULL (since janitors are never doctors at the same time).The only solution I found for this is a generalization hiearchy whereyou have the employee table with all generic attributes and then youadd tables for each new employee subtype as necessary. The subtypetables share the primary key of the employee table. The employee tablehas a "discriminator" field that allows you to figure out whichsubtype table to load for a particular entity.This solution does not seem to scale since for each value of"discriminator" I need to perform a join with a different table. Whatif I need to retrieve 1,000 employees at once?Is that possible to obtain a single ResultSet with one SQL statementSQL?Or do you I need to iterate look at the discriminator and thenperform the appropriate join? If this kind of iteration is necessarythen obviously this generalization hierarchy approach does not work inpracticesince it would be painfully slow.Is there a better approach to modelling these kind of heterogeneousentities with shared attributes that does not involve creating a tablefor each new employee type or having sparce tables (mostly filled withNULLS)I guess another approach would be to use name/value pairs but thatwould make reporting really ugly.Seems like a very common problem. Any ideas? Is this a fundamentallimitation of SQL?Thanks!- robert
View 13 Replies View RelatedGood day.
i'm a new person here and not that familiar with T-SQL...
the question is: is there any buil-in functions or special libraries in T-SQL that can help to generate correlated random variables with non-normal distribution function?
It would be also good if someone could advice if there is any application (statistical programm or non-microsoft developed library) that can deal with MS SQL and has modeling and forecasting capabilities...
thanks in advance
I have a case study requesting to create an ER diagram, with the attributes listed in each entity. The data I have is an Excel Spreadsheet listing:
CustomerName
PurchaseDate
Destination
Airline
Flight#
departDate
DapartTime
ArriveTime
Hotel
CheckIn
CheckOut
Car Rental
Pickup
Return
The case is related to travel agency that specializes in booking interesting vacations for people who are single. Note that the travellers have a variety of travel bookings: some may rent a car and drive to the hotel at their destination, others may be staying with friends or relatives at their destination, while others will need flights, hotel and car rentals in their booking.
creation of the Tables and their attributes to Normalize the model to 3NF.
I'm trying to determine the best approach to model tables for a registration form that will be used temporarily and then taken offline once the event is over. I'd like to either model the tables so that they were reusable for other registration forms or perhaps use another method to store the data, maybe using XML or some other method if that is possible?? I'm not sure.
Different registration forms would have different input fields thereby requiring different table structures. It seems inefficient to create tables that will only be used temporarily and then no longer used. So, I would need to remember to delete the tables after they have been used or they would just take up space.
The basic requirement for this registration form is to allow the user to fill out the required fields, submit, get a registration reciept confirming their registration and allow the administrators for the event to pull the data weekly or daily into an excel spreadsheet.
I can create a flat table and a stored procedure that inserts the data. I can also write the dts package that exports the data to an excel file, which would require my intervention. I'd like to have something more automatic without my intervention.
Any suggestions would be appreciated. I'm not sure the best approach for this. Is using tables the best way to go even if the tables aren't re-usable? Requiring my attention to delete afterwards.
What are the options to generate the excel reports without my intervention?
Thanks in advance for any help.
I am running the Office Professional Plus 2007 RTM with all options enabled and SQL 2005 Developer Edition on my local box. Based on the system requirements listed on the download page for the Office 2007 Data Mining Add-In, I've also verified that I have the correct CTP of SQL 2005 SP2 and that .Net 2.0 Framework is installed. Finally, I've verified that my local instance of SQL Server is configured correctly to allow temporary data mining models.
In Excel, all of the Table Analysis tools seem to work fine, and most of the options on the Data Mining ribbon also work; however, all of the options under "Data Modeling" on the Data Mining ribbon return the following error when I try to use them:
"Could not load file or assembly 'Microsoft.DataWarehouse, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependents. The system cannot find the file specified."
I've tried uninstalling everything and reinstalling, but I still get this error when I try to use the Data Modeling options.
Right now, I'm only working against the sample data provided when the data mining add-in is installed.
Any ideas on how to resolve this issue?
Hello,
I need a tool that will let me model a SQL Server 2005 database and then generate the tables, constraints, etc. from the model. I've never used a modeling tool so my knowledge is quite limited. I don't need to model or reverse engineer an application - my sole concern is on the SQL Server database side. I'm not concerned if the tool integrates with Visual Studio. And, of course, price is one consideration.
Are there any good tools that I should look at?
Thanks,
BCB
Hi all
Probably not the right forum - pointers would be appreciated - but I'll give it a try anyway:
I'm in the process of designing a relational database to be used in a BI scenario - ie. dimension and fact tables. The data will eventually be used to feed cubes in Analysis services, however end users will probably be allowed to run reports aginst views of the relational database.
I'm currently looking at the employee dimensions and my first try would designate AGE as a SCD Type 2 attribute. As a result every employee gets at least one new record every year as AGE increases. Given that BIRTHDATE is specified should I drop AGE from the tables and recreate it as a computed attribute in database views and/or cubes?
Regards, Steen
Hello--
A question has come up around the following situation where a number of analysts will building data mining models in a specific analysis services databases.
- There is one AS DB for each modeling "project" and the analysts assigned to work on the "project" are grouped together in Windows Security Groups.
- The analysts are only allowed to access the AS DB for their project. To support this security model, we've implemented scripts to create the AS DB for the "project" and then a Role is created within the AS DB called "Administrator" and the members of this role are the members of the corresponding Windows Security Group.
The AS DB, role and membership are created by a machine "admin". After the AS DB is created, it appears that the only way an analyst can build models using Visual Studio (Business Intelligence Workbench) in the AS DB while maintaining the security model is to do the following:
- Run Visual Studio (or BI Workbench)
- Select File -> Open -> Analysis Services Database, then specify the database that they have access to.
In this "online" modeling environment, things are working fine. The question is -- is it possible for an analyst to create an Analysis Services Project in Visual Studio and "bind" it to already-created AS DB? This doesn't appear to work, but I may be missing something.
Thanks,
- Paul
Our business model involves a lot of dates and the business owners frequently want reports based on each of these different dates. For example in any given order there are as follows:
- Order created date
- Client due date
- Order first payment date (an order can have multiple payments)
- Order fully paid date
- Date assigned to vendor
- Vendor return date
- Date delivered to client
On top of that we have other areas of the business, the data from which ties into the above. Here we have more dates e.g.
- Date vendor recruited
- Date vendor reviewed
At any given point the manager may want a report based on any of these dates. For example;
- Product type by order creation date (fiscal year / month)
- Product type by first payment date  (fiscal year / month)
- Product type by client due date (fiscal year / month)
and so forth. I have been asked to create a report using all of the above on at least one occasion, many of them far more frequently. At the moment I have created a standard date table and then duplicated that for each type of date that I need however this is becoming excruciating to work with as I have approximately 10-12 date tables in my data model. Is there a better way of doing this now, in Excel 2013? If not, is there an improvement in 2016 that may make life easier?Â
I need a recommendation on a data modeling tool that can be used with a data warehouse. My warehouse is running SQL 2012.
Here is my challenge: Most of the tables in the warehouse do not have primary keys and none of the tables have foreign keys on them. However, there are indexes and unique keys/indexes on the tables. I am looking for a tool that I can create virtual relationships on how the data is related, so it is visually easier for the ETL developers to write the code.
I have looked at both ER/Studio 11 and ERwin 9.6. Neither of them do it exactly the way I want it too. However, ER/Studio is pretty close.
hi we are in the process of developing a project and we r at the initial phase i.e. at the analysis phase and we have been discusssing on what is physical and logical database design. can any body send any links or articals on this will be hightly appreciated.
View 1 Replies View RelatedWhat is the difference between the logical database schema and the physical database schema?
Thanks!
Does anyone have any recommendation on whether it's better to monitor the average queue lenght for physical or logical drives? What about for a RAID set?
Just wondering,
thanks
A database was set up as a test database and then the database name changed but the logical and physical filenames still have test in their name - obviously not a good idea. I have tried to change the physical file name but get the error message that the physical file name cannot be changed once the database has been created. I have also tried detaching the database and renaming the mdf and ldf files but these could not be re-attached so had to revert back to the original names.
View 2 Replies View RelatedWhat is the difference between Logical Join and Physical Join and there Types?
View 6 Replies View RelatedIs there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedWe have an OLTP database and operational reporting is carried out on a replica server / database. We have plans to build a new data warehouse and an analysis services cube.
Question 1:Should a cube be designed to extract data from a physical star schema rather than a logical one (3NF relational (ODS?) using a data source view to derive the star)? I'm guessing for performance it's better to pull data from similar structures (physical facts and dimensions as required by analysis services) but is the difference significant?
Question 2:Depending on the answer to q1, is it bad practice to ETL data from a staging database (replica > staging) directly to a star schema (multiple data sources and cleansing / business rules required)? Or should it be processed from staging to an ods and only then to a star schema (physical or logical). I still don't know if an ODS is required but I guess the consideration for this decision is whether the business would require  daily operational (or ad hoc) reporting on the consolidated data sources (without needing historical DW functionality).
Hi all
We generally modify the logical name of a data file or log file from the vendor defined to our standard name. Is there any impact that you know about this? If you have 2 seperate database and accidently you have 2 same logical name .. I dont' think that will be an issue ... correct?
ALTER DATABASE XXX_UAT
MODIFY FILE (NAME = 'mdd_Data', NEWNAME = 'XXX_UAT_Data')
GO
ALTER DATABASE XXX_UAT
MODIFY FILE (NAME = 'mdd_Log', NEWNAME = 'XXX_UAT_Log')
GO
Hi,I want to change the logical name of the database. My database name and.MDF has the same name, but the logical name is different. Willchanging it impact anything?--sharif
View 1 Replies View RelatedI have a query. on my search page i have a number of textboxes e.g. title, subtitle, ISBN where users can enter a search string and when they click on a button the data is displayed in the gridview below.
my query seems to be bringing up the right records but with some additional records into the gridview. I have tried the same query within the SQL server management studio and it brings back two records like indicated but within my aspx page it brings back 6 additional records that have no relevance to the query. Im quite baffled as to why this is happening and i have debugged but nothing comes to light.
my code in my aspx page is as follows:
public void BindData(){String _BookID = TxtBookID.Text;String _Title = TxtTitle.Text;String _Subtitle = TxtSubtitle.Text;String _ISBN = TxtISBN.Text;String AuthorFName = TxtForename.Text;String AuthorLName = TxtSurname.Text;
SqlDataAdapter ad = new SqlDataAdapter("SELECT Book_ID, Book_ID_Internal AS [Book Internal ID], Title, Subtitle, ISBN, Edition, Publication_Date AS [Published Date] FROM Book WHERE Book_ID_Internal = '" + _BookID + "' OR Title = '" + _Title + "' OR Subtitle = '" + _Subtitle + "'", conn);DataSet ds = new DataSet();ad.Fill(ds, "Book");if (ds.Tables[0].Rows.Count == 0){//if there are no rows returned-state in the gridds.Tables[0].Rows.Add(ds.Tables[0].NewRow());GridView1.DataSource = ds;GridView1.DataBind();int columncount = GridView1.Rows[0].Cells.Count;GridView1.Rows[0].Cells.Clear();GridView1.Rows[0].Cells.Add(new TableCell());GridView1.Rows[0].Cells[0].ColumnSpan = columncount;GridView1.Rows[0].Cells[0].Font.Italic = true;GridView1.Rows[0].Cells[0].Text = "No Records Found";}else{GridView1.DataSource = ds;GridView1.DataBind();}}
if i input the same query into SQL management studio:
SELECT * FROM Book WHERE Book_ID_Internal = 'ITD 450' OR Title = 'HTML 4.0'
it returns two records which is correct.
Can anyone see where i am going wrong?
Hello,
Is there any way to change the logical filename of exsiting database
Thanks
Lara
In the database properties screen, there are four tabs: General, Transaction Log, Options, Permissions. In the General Tab, it lists four columns: File name, Location, Space Allocated, File Group.
The string in the File name column has a value such as MY_DATABASE_DAT, whereas the Location column has a value like D:mssql7dataMY_DATABASE.MDF.
The Location value is clearly the Windows file name. The "File name" (which I have seen called the "logical file") is a mystery to me. What is it used for? How can it be changed? Is there a problem if two different databases have the same "logical file" name?
(For example, if you do sp_detach_db, copy the file to another place and then sp_attach_db the new file to another database name, you have two different databases with the same "logical file" name.)
Much thanks to anyone who can shed light on this.
Hi everyone,
I have a database (xyz) one Machine A. On the same machine I want to create a copy of the (xyz)database with different name(xyz_1). When I restore with move option it will restore but the thing is logical file names of xyz and xyz_1 are same. I know I can change these file names by manupulating the system table.
My question is If I didn't change the logical file names of database xyz_1(new). Is there any problems or issues may arise.
Thank you,
Dindu.
The organisation I'm working for has SQL Server 2005 merge replication up and running on 11 servers in 3 different countries.
Ocasionally we experience problems caused by rows in a logical record relationship being replicated between servers in the wrong order, i.e. an attempt is made to insert child rows before the parent. I am not able to reproduce this behaviour on my test servers.
Has anyone else experienced this problem?
We have a progress database that we use RS on. The "status" field (active/inactive) is a logical field (true/false). When doing the SQL how do I specify that I want only active records (value = true).
View 1 Replies View Relatedhi,
I am new to this technology. can anyone pls help me.can anybody tell me what is physiacl file and source physiacl file wat are the attributes of physical file and source physical file.and how do we identify the uniqueness of a job.