Normalization Questions

Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.

2)

Employee (ssn, Name, Salary, Address, ListOfSkills)

Yes,

No.Ans: No. as list of skills would be repeated.

3)

Department (Did, Dname, ssn)

Yes,

No.Ans: No. ssn and did should be moved to a seperate table.

4)

Vehicle (LicensePlate,Brand,

Model, PurchasePrice, Year, OwnerSSN, OwnerName

Yes,

NoAns: No.

5)

Employee (ssn, Name, Salary, did) (obs.:

employee can only belong to one department)

Yes,

No.Ans: Yes.

6)

Customer (Cust_Id, Name, Salesperson, Region) where Salesperson

determines Region.

Yes,

No.Ans: No.Salesperson and region should be moved to a seperate table.

7)

Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo

->ItemName

Yes,

No.Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.

Not homework, right? :)

Hai everybody recently i came across this article and i have tried to answer all the follwoing questions. But i am not sure its correct or not..so you peoples can comment on the follwoing questions.

2) Employee (ssn, Name, Salary, Address, ListOfSkills)

Yes, No. Ans: No. as list of skills would be repeated.

louis: exactly. Any column that is plural likely represents multiple things...

3) Department (Did, Dname, ssn)

Yes, No. Ans: No. ssn and did should be moved to a seperate table.

Louis: Well, Did is fine, but I would expect that ssn violates fourth normal form. If the SSN represents something where there is only one of them (like the manager,) then this is fine. If it represents a member of a department, then you definitely have problems because the department name and members of the department relate differently to the Did key of the Department table.

4) Vehicle (LicensePlate, Brand, Model, PurchasePrice, Year, OwnerSSN, OwnerName

Yes, No Ans: No.

Louis: if you are only allowing a single owner of the vehicle AND you only track the most recent purchase information, then yes. Else no. You always need to consider cardinality between attribute and key.

5) Employee (ssn, Name, Salary, did) (obs.: employee can only belong to one department)

Yes, No. Ans: Yes.

Louis: agree. One employee, one name, one salary, one department, all data corresponds to the employee. That is fine.

6) Customer (Cust_Id, Name, Salesperson, Region) where Salesperson determines Region.

Yes, No. Ans: No.Salesperson and region should be moved to a seperate table.

Louis: Good question. Was this the salesperson of the customer, and the Region of the customer? Or is this the region that the salesperson works, regardless of the location of the customer? That makes a big different.

7) Component (ItemNo, ComponentNo, ItemName, Quantity) where ItemNo -> ItemName

Yes, No. Ans: No.As itemname is a subset of itemno and not a subset of both itemno and componentno.

Louis. No, like you said, this violates second normal form

|||Thanks louis, definetly its not homework. I am very much interested in design, that's why i posted.sql

Non-additive measures

We need to support non-additive measures in our cube, such as rates. The rates will be averaged across time by using a custom formula (weighted average). The rate measures cannot be aggregated by any other dimension. However, when the end user browses by the Account dimension (each member in the Account dimension relates to exactly one record in the fact table), the user must be able to see the rates.

I set the average function of the rates measures to None. How can I surface the rate measures from the measure leaves to the Account dimension leaves?

What is the granularity of rates in the fact table ? If they are entered, for example, per Account and Time - you should create new measure group at that granularity.|||

Hi, Mosha. I appologize for not making this clearer. The measure group is already created. The lowest grain is the Account dimension. The measure group intersect with other dimensions too, including Time. Besides non-additive measures, the measure group has additive and semi-additive measures.

If the aggregate function of a measure is None, is it possible to show up that measure at the Account level so I can apply the custom aggregation formula across time to get:

Jan Feb Mar Apr .... Total

Acct A 0.45 0.5 0.6 0.4 = (0.45 * 31 + 0.5 * 28 + 0.6 * 31 + 0.4 * 30 + ...) / Total days for selected months

Acct B 0.30 0.35 0.75 0.8 = <same custom formula>

Again, an Account member has no more than one corresponding record in the measure group always.

|||What I suggested was to leave all other (additive) measures in the measure group that you already created. Based on your example, the granularity of Rates is at least Account and Time (but possibly other attributes). You need to create NEW measure group and include just these two dimensions (unless rates vary by other dimensions as well). You didn't specify how the rates should aggregate across accounts, but probably they don't at all.|||

Thanks Mosha. I prefer to leave the rates in the same measure group for usability reasons and I think I have a workaround by setting the Account All member to NULL and the measure aggregate function to SUM.

Let me ask a more general question. If I have an aggregate function of None, is it possible to bring up the measure leaves to the dimension leaves? I haven't been successful doing so. At the same time, this is what BOL states for the None aggregate function:

"No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure."

Why I don't see the non-additive measure values when browsing at the Account leaves then?

|||

I prefer to leave the rates in the same measure group for usability reasons

What are these usability reasons ? By artificially forcing deeper grain on the rates you make your model less natural and it is harder to work with. Why are you so resistant to set correct grain for you rate measures ?

If I have an aggregate function of None, is it possible to bring up the measure leaves to the dimension leaves?

I think you misunderstand the notion of "measure group leaves" and "dimension leaves". Perhaps the following blog will be of help: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

Anyway, if you were to follow my advice about creating measure group with Account and Time, and set custom formula to aggregate Time as Average - you wouldn't have had this issue.

|||

What are these usability reasons ? Why are you so resistant to set correct grain for you rate measures ?

B/c the semi-additive and additive measures will be organized in display folders under one measure group while the rates will be under another. If you are building off-the-shelf solution, things like these matter especially when your end users haven't heard about OLAP. Besides, I need to worry now about partitioning and processing a separate measure group.

I think you misunderstand the notion of "measure group leaves" and "dimension leaves". Perhaps the following blog will be of help: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

I've read the article (thanks for sharing) and anything written on this subject but I couldn't find answers to the questions "what happens when the aggregate function is set to None, where is the data stored, and how the heck I can get to it" (hint, hint :-).

Anyway, if you were to follow my advice about creating measure group with Account and Time, and set custom formula to aggregate Time as Average - you wouldn't have had this issue.

It is a good advice and I can see the simplifications it brings. However, setting the aggregate function to Average won't help anyway since I need a weighted average calculation over time. Hence, I still need to overwrite the total over time.

NON equijoins

Hi there.

I just wanted to see if anyone has come across any patterns to deal with NON equijoins.

Based on this article - http://sqljunkies.com/WebLog/tpagel/archive/2005/08/31/16585.aspx - there seems to be 2 approaches:

1) encapsulate the logic in a stored procedure or

2) generate a joined dataset with potentially alot of unwanted rows which then need to be filtered out.

Are there any other patterns people have discovered?

Thanks.

Hi,

I had the same issue we found the solution ourselves. Look at the third thread in this question

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=367775&SiteID=1

Basically you can modify the sql in Advanced tab. Hope this helps

NOLOCK on views

Hey guys,

I came across a SQL statement, thought up by a developer, in which two views were joined with the NOLOCK hint:
SELECT v1.xxx, v2.yyy
FROM dbo.vw_SomeView v1 WITH (NOLOCK)
INNER JOIN dbo.vw_SomeOtherView WITH (NOLOCK) ON v1.id = v2.id
The views are not created the NOLOCK hint. So my question is: has the NOLOCK hint any effect here?

I've looked in the BOL and searched on the net but can't find anything on this particular topic.

Lex

PS. Personally I don't like to use views in JOINs. I've seen too many cases in which tables are joined twice just because they are part of both views. Further more I don't like the "random" use of NOLOCK because most people don't seem to understand the implications of it. But this is besides the point of my question ;)Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.|||Looks like it's time for a little hands on experiment. Take an update lock on one of the tables used in either of the views in one QA window, and try to run the sql in another.

I use and recommend (NOLOCK) Optimizer hints on a regular basis. Just know that when a (NOLOCK) hint is used, it performs a "Dirty Read" against the data.

The primary benefit to a (NOLOCK) hint is to prevent the blocking of objects from occurring when users are selecting data. I would recommend using them if you have contention in your environment with users holding exclusive locks on tables.

Hope this helps!

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Server Authentication and SSIS !!!!

Hi

I want to manage my servers from a central location and have the ability to manage my packges too. Unfortunatley my servers are across domains which means I have to use SQL Server Login, which isn't a bad thing. However for some bizzare reason I can't connect to Integration Services using this method, only windows authentication which is a complete pain in the butt from a management stand point as I have to copy packages to various servers rather than add them from a central point.

Does anyone know if this bug/feature or what ever Microsoft is calling it will be fixed or know of any workarounds.

Thanks

I have no problem with SSIS and SQL-Server-Authentification.|||

I do not even get the option to change the authentication method to SQL Server and the box is greyed out so I can't change the Windows authentication user either.

I do not have any problems inside SSIS, only when I try to connect to the SSIS server from within SQL Server Management Studio in a different domain.

|||I am assuming you are trying to connect to Integration Services from SQL Server Management Studio. You are right, you cannot change the authentication method to SQL Server authentication in this case. This is because it is a separate service, and sql server authentication does not work in this case.|||

This is indeed problematic... if developers for SSIS or even SSAS for that matter have workstations in a domain different from the domain that hosts the Server you cannot deploy packages to the server, you can't create/access SSAS Dbs either.

This means then that any developer must work in the same domain as the server they are trying to deploy to or access (SQL Server mgmt. studio to monitor SSIS packages or to even open a SSAS DB in Visual Studio)

Anyone find a way around this?

Joe.

|||

I just found this the hard way. I work for client in a different domain to my workstation and I cannot deploy the app. Like Joe, if someone has already researched this, plxz let us know.

I also have problems with restricted VPN access with locked down ports at the client.

Thanks

|||

I have exactly the same problem and also found out the hard way. In fact my problem is a little worse - our client doesn't even HAVE a domain (yes they are a little backward)!

I managed to get the package installed on the client's server after much trial and error (by copying the VS solution to the server, editing my connection managers and debugging then deploying from there. Thankfully they had installed VS on the server).

The bigger problem is now that the clients can never run the SSIS package remotely. What is worse is that SSAS has the same problem - my clients can't browse their cube from Excel or SSMS! The only way I can think of around this is to remote desktop into the server, which is obviously bad.

Thanks in advance for any help / suggestions / comments.

|||

If you want to execute a SSIS package remotely, set up a SQL Server Agent job to execute the package. That job can be executed from wherever you like by anyone with SSMS.

-Jamie

|||

Aranda wrote:

The bigger problem is now that the clients can never run the SSIS package remotely.

SSIS Service does not provide remote execution, as Jamie suggested - use Agent Jobs instead.

The usual way to get package installed on a machine you can access remotely is to build deployment utility (option in VS SSIS project), copy the folder with deployment utility and run it on the target machine.

What you'll be missing due to lack of domain is
1) ability to remotely store packages, and
2) ability to remotely monitor packages as they are being run, and to terminate these packages.

|||

I just came across this.

I to am frustrated that you can only connect to the SSIS service via windows authentication. I am attempting to connect from my computer(a client running Management Studio) not directly from the machine 'hosting' the SQL Server and SSIS services. When I do this, I obviously have to set myself up with an operating system(Win 2k3 server )account on the server machine but I am only able to connect to SSIS if I am a member of the administrators group. Is this true? Can I connect as someone with less rights/privledges?

No support for SQL Express or SQL Developer edition running on Virtual PC?

Am I miss-reading something? After spending hours upon hours trying to install VS2005 Beta 2 June CTP edition, I stumbled across some documentation that basically says that SQL Express is *not* supported running on a Virtual PC.

Has anyone else run into this problem?

This is the documentation that I found:
http://download.microsoft.com/download/9/9/5/99599cc4-93ee-4252-85dd-23abff8631b0/RequirementsSQLEXP2005.htm
SQL Express will work fine on Virtual PC.