duplicate records linking tables

Want to do a MAS90 PR report linking Tables PR6, PR7, & PR8. The tables have multiple records for each employee. When I link PR6 & PR7 everything's fine. When I include PR8 then the records from PR7 print multiple times. What am I doing wrong? Thanks.Post the sample data and the result you want|||you need to be sure you understand the relationships the tables have to each othe.

In most cases, you'll need to do LEFT OUTER JOINS..and not INNER JOINS.

But again, this is based on the data set that you are trying to achieve.

post some sample data lines of what you are getting and also an example of what you would like to see ...then we can help.

Duplicate Information with Different IDs

I am trying to sum monthly hours for weekly timecards which have multiple
line items as tasks, but I am retrieving duplicate entries.
This is because a timecard can have a timecard_type of "r" (regular), or a
timecard_type of "c" (corrected).
If the timecard is corrected, the original with the timecard_type of "r"
still exists, but both time cards with duplicate information, have different
timecard IDs.
WHERE tc_type = 'r'
OR tc_type = 'c'
doesn't help me, so how can I get a monthly total of regular
timecards(timecard_type = 'r'), & corrected (timecard_type = 'c'),
and the original regular timecard is not returned, if it has been corrected.
I've got to think there's some way to use the timecard IDs.
When trying to use an "IF...ELSE", I get the error "Subquery returned more
than 1 value" because of the multiple line items.
Can someone please help?
Thanks much.ok. this uses a bit of old school sql but i hope it will help you
i use subqueries and join.
first to find out if a task has a corrected hour report for a certain
week
and second to retrieve the correct report for the task
here goes...
if your table , WEEKLY_CARDS , has the following fields :
EMPID , YEAR, WEEK_OF_YEAR , TASKID , HOURS , CARDTYPE
and EMPID , YEAR, WEEK_OF_YEAR , TASKID , CARDTYPE are the business
unique key in the table.
run the following sql
select myTable.*
from
WEEKLY_CARDS as myTable
inner join
(select
regular.empid
,regular.year
,regular.week_of_year
,regular.taskid
,isnull(corrected.cardtype,regular.cardtype) as cardtypeToUse
from
(select empid , year , week_of_year , taskid , cardtype
from WEEKLY_CARDS
where cardtype = 'r'
) as regular
left outer join
(select empid , year , week_of_year , taskid , cardtype
from WEEKLY_CARDS
where cardtype = 'c'
) as corrected
on regular.empid = corrected.empid
,regular.year = corrected.year
,regular.week_of_year = corrected.week_of_year
,regular.taskid = corrected.taskid
) whatToUse
on
on mytable.empid = whatToUse.empid
,mytable.year = whatToUse.year
,mytable.week_of_year = whatToUse.week_of_year
,mytable.taskid = whatToUse.taskid
,cardtype.cardtype= whatToUse.cardtypeToUse
if you use more than 1 table (maybe u have a CARDS table) then adjust
the sql accordingly. also, if you need information about a certain week
add the appropriate WHERE clauses
hope this helps
lior
JDArsenault wrote:
> I am trying to sum monthly hours for weekly timecards which have multiple
> line items as tasks, but I am retrieving duplicate entries.
> This is because a timecard can have a timecard_type of "r" (regular), or a
> timecard_type of "c" (corrected).
> If the timecard is corrected, the original with the timecard_type of "r"
> still exists, but both time cards with duplicate information, have different
> timecard IDs.
> WHERE tc_type = 'r'
> OR tc_type = 'c'
> doesn't help me, so how can I get a monthly total of regular
> timecards(timecard_type = 'r'), & corrected (timecard_type = 'c'),
> and the original regular timecard is not returned, if it has been corrected.
> I've got to think there's some way to use the timecard IDs.
> When trying to use an "IF...ELSE", I get the error "Subquery returned more
> than 1 value" because of the multiple line items.
> Can someone please help?
> Thanks much.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

DTS: One failure step for multiple process steps

How can I make this work?

I have two process steps in a package and one failure step. Both process steps have On Failure workflows to the failure step. The problem I'm having is that it seems if two or more workflows go to the same failure step then neither go to the failure step, but it does go if only one process step is attached to it.

I know I can create duplicate steps for each process step, but I was hoping to be able to do it this way FMI.

Thanks for your assistance.Has anyone tried what I am talking about on their end? I'd really like to know if it's just a problem I'm having.

DTS: migrating many, multiple, new databases regularly

I am looking for some advice on best ways to use DTS between our various test database servers.

I need to test an application against MS SQL Server 2000/2005 and Oracle 9i/10g. This includes creating new databases,logins etc on each server. And also means I need to recreate all my test data again at least twice; on Oracle and MS SQL Server(i.e I can backup and restore my databases between MS SQL and Oracle versions). I am looking to for a better way to achieve this and am here looking for some advice on the best way.

DTS (tried only on MS SQL 2000 so far) does seem to help however, one problem I have come across is mapping tables. The application creates little over 200 tables, although the schema remains over all remains identical, but with slight differences. E.g different name formats. For instance. a table on SQL2000 is called "Table" whereas Oracle calls is "TABLE". This means, on the DTS wizard I need to drop down and select the correct table name for each table.

What could be other possible and efficient ways to achieve this? Also, I need to do this for around 10 multiple databases and not just one database. I will also need to be doing this frequently, if not regularly as I will be creating new test databases and test data.

Any inputs will be appreciated. Thank you.

i think DTS or SSIS of sql server 2005 (the true ETL Tool with much more advanced features than DTS) can handle this problem. If you want to create your own automated script then you can configure LinkedServer from your staging server and fetch the data.

Madhu

|||Thanks for the reply Madhu.

I am quite inexperienced and still learning a lot of things. I have only used DTS a few times. SSIS? Havent really heard of it before!
If I was to use DTS to transfer data from my MS SQL200 database to Oracle 9i/10g and SQL2005, what would be an easy way to map these 200 tables rather than going and selecting the correct table from destination database?

Thanks once again.|||

if DTS is the ETL tool of SQL Server 2000 , SSIS is enhanced , Feature rich ETL tool in SQL Server 2005. Here only name is not changed, there are lot of enhancement as far as the features are concerned. If you wnat to transfer the data from SQL Server 2000 to 2005 then you must create SSIS not DTS. I am not sure about transporing to oracle. But you can configure Linkedserver to oracle/sql server and you can create synonyms in sql server 2005 which will be as good as you are accessing the object from your own database.

Madhu

|||

N

There is alot information available on the SQL Server books online about SSIS,make sure you have downloaded the latest update from Microsoft.

Look at this blog http://sqljunkies.com/WebLog/donald_farmer/archive/2005/03/13/8819.aspx that helps.

http://www.developer.com/db/article.php/3635316

http://msdn2.microsoft.com/en-us/sql/aa336312.aspx

|||Thanks again Madhu.
This is useful. However, just as I mentioned in my previous post
-I purely need to copy the test data from my 200 tables that the application has created to or fro Oracle server/SQL Server; as the requirement is for
-copying many such databases and not just one database
-the application should be able to run independently on each server. as we not not bother about the server, we need a solution so that we do not need to create test data twice on each database SQL and Oracle databases.
-this is going to again be regular thing that I should be able to do as I will need to create new type of test data move it to other type of server (may it be Oracle or SQL) and test the application on both these databases.
Secondly, I have tried DTS - and what I need is a way to be able to map the tables. The wizard does not recognize the same table names with different cases. like A_Table and A_TABLE(oracle's table)|||Satya, Thanks for the links. They look very useful.

DTS to Create New Excel Sheet in Existing Workbook

Has anyone been able to create a DTS that will create a new sheet within an existing Excel file. I want one Excel file with multiple tabs referencing the beginning of each week and growing. The name needs to be dynamic so as to not overwrite previous sheets (tabs) in the file.

I have a current ActiveX script that will move the data to a dynamic name (date), I just can't create the table (sheet/tab) to move the data to.

Any help would be appreciated.

-MarkI don't think that you can...you probably need to use COM objects...

Why do you need multiple sheets?|||I have a user asking for statistics in Excel with weekly tabs to compare. I may just build something in Crytal.

Thanks,|||This may help

http://www.dbforums.com/t977745.html|||Multiple worksheets are done by creating multiple tables in your DTS package. Each table represents a worksheet.

There you have it :)|||To delete the sheet in your excel and re-create it
you can do it in SQL task by including the following lines:

DROP table `a43`
GO

CREATE TABLE `a43` (
`Col001` VarChar (2) ,
`Col002` VarChar (8) ,
...
...
... )

where a43 is your sheet name ~
hope this will help you out :D|||As has been previously stated, create a connection to your workbook, then use an execute sql task on the connection. The sql task should be a create table statement. This will create your sheets in your work book.