Can't Seem to Figure Out Why I See What I Do -- Should Be EASY

Let's say my customer dimension is similar to the following:

Customer_key | CustName | AcctOwner
--
1 | ABC Company | Joe Cool
1 | ABC Company | John Doe

* My customer dimension is a Named Query in the DSV where I'm doing a LEFT OUTER JOIN to generate the AcctOwner

Now let's say my Sales Fact table looks like the following:

Customer_key | Product_key | InvoiceAmt
-
1 | 1 | 5.00
1 | 2 | 2.75
1 | 3 | 0.25

Now if my pivot simply contains CustName, AcctOwner and InvoiceAmt, shouldn't I see the following:

CustName | AcctOwner | InvoiceAmt
--
ABC Company | Joe Cool | 8.00
ABC Company | John Doe | 8.00

However, I only see one line and I cannot figure out why I see the line that I do in the cube; can't seem to figure out why it's showing the Account Owner that it's showing.

Surely I'm having a brain fart and it's something really stupid where I'll be embarrassed. Please let me know if additional information is needed.

It's an AS2005 SP2 cube.

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

|||

Darren Gosbell wrote:

It's because your customer keys are not unique. When SSAS builds the dimension it will collect a unique list of the keys, then it will generate the CustName and AcctOwner attributes, with logic Equivalent to:

UDPATE <dimension> SET AcctOwner = dimCustomer.AcctOwner WHERE Customer_Key = 1

I suspect that what you have here is that AcctOwner should not be an attribute of the customer, but rather is a many to many relationship where one account owner can have many customers and one customer can have many account owners.

If this is the case you need to create a bridge table with the account owner and customer relationship and set this up as a measure group which you can use in a many-to-many relationship. There is information in BOL on many-to-many relationships, but there is also an extensive whitepaper available here: http://www.sqlbi.eu/Projects/Manytomanydimensionalmodeling/tabid/80/Default.aspx

Darren,

Thanks for the input. It should NOT be a many-to-many relationship, but there are currently a few cases of bad data (from the source CRM system) where a Customer has more than one Account Owner. Once the data cleanup is performed, it will be a one-to-one relationship.

With that said, I was just testing this attribute (i.e., Account Owner) and came across the above scenario and became baffled on what I saw. I had expected both Account Owners to populate in the pivot.

Any additional thoughts?

|||

If you data is dirty you either need to clean it before loading it, or adjust your design to allow it to display. You could build a dimension table that uses an identity column to generate a unique key for each row, but

You will not see both account owners, because for a given Customer_key value there can only be one Account Owner value, so usually what happens is that the last in wins. Essentially in the scenario you have outlined, the dimension processing updates the Account Owner twice, once to set it to the first value "Joe Cool" and the "Joe Cool" gets overwritten with the value "John Doe".

The relationship between the key of an dimension and the other attributes can either be a one to one or a one to many where many keys can map to a single attribute value, but a single key CANNOT map to more than on attribute value.

Cant Seem to Figure out a Trigger...

Hey everyone,

First post (of hopefully many) to come...

I am currently writing a little Football Pool Application and I need to create a trigger (I think anyways) but I cannot for the life of me figure it out.

I won't bore people with the entire schema. What is needed to know is that I have a "Game" table which stores the ID's of two teams playing, and their respective scores (0 until after the game ends), I have a UserTable which stores a "card id" (basically a set of picks) and then I have UserPicksTable which basically just links to the UserCards as well as has a "WinningTeam" field, which will hold the ID of the team that the user in question has picked to win.

So every Tuesday morning I will enter the game scores into the "Game" table and I want this trigger to give a point to all the users that picked that game correctly...

If anyone could give me a hand, or a boot, in the right direction it would be a huge help. I can't even figure out where to start...

thanks,

--dbWhich database is it? Triggers sound like Oracle; if so, perhaps you could consider using a job scheduler (DBMS_JOB or DBMS_SCHEDULER) which would, depending on the results, send mail to users (or even all of them).

Can't see the ReportManager from another workstation

Maybe this sounds like a simple question for you, but it really took me a
while trying to figure it out.
I have had a report ready on my Report Manager, but I could not run it on
any other workstations at all. My workstation is using IIS 5.1, Win XP, RS
2000 and VS .NET 2003. The database server and report server is a remote
server.
I am thinking maybe I just missed something in the configuration. Please
advise. Thanks a lot.Check that you have given the correct security permissions for your users.
If you are saying that you log in with your own credentials on several boxes
but can only access the report manager from one box then that is a different
problem.
Jim.
"WindWind" wrote:
> Maybe this sounds like a simple question for you, but it really took me a
> while trying to figure it out.
> I have had a report ready on my Report Manager, but I could not run it on
> any other workstations at all. My workstation is using IIS 5.1, Win XP, RS
> 2000 and VS .NET 2003. The database server and report server is a remote
> server.
> I am thinking maybe I just missed something in the configuration. Please
> advise. Thanks a lot.
>|||Hi, Jim,
Thanks for the reply. Yes, I logged in the computers under the same domain ID.
Does this cause the problem?
"Jim Breffni" wrote:
> Check that you have given the correct security permissions for your users.
> If you are saying that you log in with your own credentials on several boxes
> but can only access the report manager from one box then that is a different
> problem.
> Jim.
>
> "WindWind" wrote:
> > Maybe this sounds like a simple question for you, but it really took me a
> > while trying to figure it out.
> >
> > I have had a report ready on my Report Manager, but I could not run it on
> > any other workstations at all. My workstation is using IIS 5.1, Win XP, RS
> > 2000 and VS .NET 2003. The database server and report server is a remote
> > server.
> >
> > I am thinking maybe I just missed something in the configuration. Please
> > advise. Thanks a lot.
> >|||On the report server itself, go to http://localhost/reports
then click on Site Settings
(What we are doing is verifying that no one changed anything for the Browser
rights)
In the Security section, click the "Configure item-level role definitions"
link
Next, click the browser role.
Verify that View folders, View models, view reports, view resources, and
manage individual subscriptions is checked (this is the default settings)
If any are missing, check them and then click Ok
Next, go back to your home page by click the Home link in the upper right.
Click the Properties tab.
You should see two entries (by default only one - the BUILTIN\Administrators
will be listed)
The other entry should be (if you want domain users to have rights to see
this report server) is:
DOMAIN\Domain Users (where DOMAIN is the name of your domain)
If you don't see your domain users being listed, then click "New Role
Assignment" and then check the box for Browser rights and type the group you
want to have rights. In this case, DOMAIN\Domain Users
If they still cannot connect, log them off their workstation, and log back
on after making these changes.
=-Chris
"WindWind" <WindWind@.discussions.microsoft.com> wrote in message
news:A7F95DD1-107D-4100-B2F5-2864BD41F2B4@.microsoft.com...
> Maybe this sounds like a simple question for you, but it really took me a
> while trying to figure it out.
> I have had a report ready on my Report Manager, but I could not run it on
> any other workstations at all. My workstation is using IIS 5.1, Win XP, RS
> 2000 and VS .NET 2003. The database server and report server is a remote
> server.
> I am thinking maybe I just missed something in the configuration. Please
> advise. Thanks a lot.
>

Cant quite figure this one out... any help?

I have 2 tables, one contains the details for all the documents stored on a server such as the documents name and which directory it is stored in, and the other contains the details for each document version (if there are any) these are paired up using the documents id, per example below

Documents
ID, Name, Dir
1, word.doc, /docs
2, excel.xls, /docs
3, webpage.html, /docs

Document Versions
ID
1
1
1
2
2
3

so, as you can see, in the versions table there are 3 versions of 'word.doc' 2 of 'excel.xls' and 1 of 'webpage.html'. i need to write a query that will return all the documents in the versions table who have a matching document in the documents table (that is not the major problem though) the main problem is that i only want to return it if the matching record in the documents table passes certain criteria, this being that it is from a certain site and has a particular type of file name. for example, i would like to return only the document versions whose parent records are '.doc' or '.xls' files, but not html. this would leave me with :

Versions
ID
1
1
1
2
2

leaving the last record out, as it's parent is a html file.

As a bonus, if you could tell me how to append the parents name to each record so i get a table like this :

Results
ID Name
1 'word.doc'
1 'word.doc'
1 'word.doc'
2 'excel.xls'
2 'excel.xls'

that would be great, thank youYou didn't specify database engine you use; but, in Oracle it would be something like this:SELECT d.id, d.name
FROM DOCUMENTS d, VERSIONS v
WHERE v.id = d.id
AND SUBSTR(d.name, INSTR(d.name, '.', 1) + 1, LENGTH(d.name)) <> 'html';
WHERE clause will be different for another requirements, of course.|||hmmm, not sure if that's quite it. and the database engine is SQL server|||As I can't see a generic SQL solution, I'd recommend using:SELECT d.id, d.name
FROM documents AS d
JOIN versions AS v
ON (v.id = d.id)
WHERE d.name NOT LIKE '%.html'-PatP|||Got It! Thanks for your help though. This was the final solution for anyone who's interested

SELECT Docs.Name, DocVersions.TimeCreated, DocVersions.Content
FROM DocVersions INNER JOIN
Docs ON DocVersions.Id = Docs.Id
WHERE (Docs.LeafName LIKE '%.doc' OR
Docs.Name LIKE '%.xls' OR
Docs.Name LIKE '%.ppt' OR
Docs.Name LIKE '%.pdf' OR
Docs.Name LIKE '%.mpp' OR
Docs.Name LIKE ' % .txt ') AND (Docs.DirName LIKE 'sites/archive/%')

I think i confused u all with my talk of leaving out the html document. It wasn't so much i wanted to leave that out as include the others if you know what i mean