SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


2008r2 Cube not linking two views on key


2008r2 Cube not linking two views on key

Author
Message
Ryan1
Ryan1
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 130
I am building a cube in 2008r2 analysis services (BIDS) which requires me to link two views. The first view contains only measures (these measures are calculated by SUM) and the second view contains only dimensions. It seems as if I hook up the dimensions properly because I can view the data by dimension in the browser and it matches spot on. However, when I view the measures in browser they seem to give me a total no matter what I do. If I give several columns worth of dimensions on top and a single measure on the side, it will give me the same total all the way across. I want it to give me the SUM of the measure, but also filtered by dimension.

-I set the 'relationship' between the two views under Data Source Views in BIDS to match both keys
-There are hardly any keys set in the actual SQL database. For example the dimension tables aren't hard linked.
-The view with the measures has multiple copies of the key/record (for different parts of the year).

I understand that I am probably not providing enough information, however, if someone could point me in the right direction as to what I should be providing that would be great. Thank you very much
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60747 Visits: 13297
Ryan1 (6/17/2014)
I am building a cube in 2008r2 analysis services (BIDS) which requires me to link two views. The first view contains only measures (these measures are calculated by SUM) and the second view contains only dimensions. It seems as if I hook up the dimensions properly because I can view the data by dimension in the browser and it matches spot on. However, when I view the measures in browser they seem to give me a total no matter what I do. If I give several columns worth of dimensions on top and a single measure on the side, it will give me the same total all the way across. I want it to give me the SUM of the measure, but also filtered by dimension.

-I set the 'relationship' between the two views under Data Source Views in BIDS to match both keys
-There are hardly any keys set in the actual SQL database. For example the dimension tables aren't hard linked.
-The view with the measures has multiple copies of the key/record (for different parts of the year).

I understand that I am probably not providing enough information, however, if someone could point me in the right direction as to what I should be providing that would be great. Thank you very much


Check if the measure group and the dimensions are linked properly in the dimension usage tab of the cube.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Ryan1
Ryan1
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 130
Attached is what my dimension usage tab looks like.

Gender, Automobile, and Customer is from view one which contains only data that is directly linked to dimensions.

Dates is a dimension in table two. View two contains many columns of measures(SUM).


Glancing at this I would say that table one should be on this tab however it seems as if the data is having issues only with measures. Any help is greatly appreciated. Thank you.
Attachments
Dimension_Measure_help.jpg (17 views, 50.00 KB)
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60747 Visits: 13297
Ryan1 (6/17/2014)
Attached is what my dimension usage tab looks like.

Gender, Automobile, and Customer is from view one which contains only data that is directly linked to dimensions.

Dates is a dimension in table two. View two contains many columns of measures(SUM).


Glancing at this I would say that table one should be on this tab however it seems as if the data is having issues only with measures. Any help is greatly appreciated. Thank you.


Apart from Dates, not a single dimension is linked to your measure group. Click on each gray box, set the relationship type to regular and specify the granularity and on which keys to join.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Ryan1
Ryan1
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 130
The measure group is made from the view which does not contain any of these dimensions, therefore there is no key which matches the 'Measure Group Column'.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60747 Visits: 13297
Ryan1 (6/17/2014)
The measure group is made from the view which does not contain any of these dimensions, therefore there is no key which matches the 'Measure Group Column'.


Can you give the definition of those views, otherwise I can just keep guessing here.


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Ryan1
Ryan1
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 130
View 1 contains dimensional data such as gender, type of owned an automobile, etc.

View 2 contains measures which require a sum aggregation that can be broken up by date.



So if Thomas was a man who owned a lexus and came into the shop in march, april, and may...

View one would contain a record which specified:
Record Key, Thomas(Column: name), Man(Column: gender), Lexus(Column: automobile)

View two would contain:
Record Key, March(Column: Date), 1 (Column: WorkDone [displayed as a 1 or a 0])
Same Record Key, April(Column: Date), 1 (Column: WorkDone [displayed as a 1 or a 0])
Same Record Key, May(Column: Date), 1 (Column: WorkDone [displayed as a 1 or a 0])

I want my cube to be able to filter on the dimensions such as view_one.gender and view_one.automobile, but also to show the measure of SUM( view_two.WorkDone ) during a certain month.


We could then see how many men who owned Lexus's and got work done in the month of May.
Nevyn
Nevyn
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3402 Visits: 3149
I have no idea why you would want to set up your views that way. With no measures in the first view, there is really nothing you gain from having 2 views as opposed to having just one. When you link it to the view with the measures you will hit that granularity level anyway.

So easy answer: make it one view.


Harder answer if you must have 2 views: Make the first view a dimension and make other dimensions referenced dimensions through that first view.
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60747 Visits: 13297
How do you link Thomas from View 1 to the measures in View 2?
Is record key the same?


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
Ryan1
Ryan1
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 130
Nevyn (6/17/2014)
I have no idea why you would want to set up your views that way. With no measures in the first view, there is really nothing you gain from having 2 views as opposed to having just one. When you link it to the view with the measures you will hit that granularity level anyway.

So easy answer: make it one view.


Harder answer if you must have 2 views: Make the first view a dimension and make other dimensions referenced dimensions through that first view.




I didn't make this database and I don't necessarily want to be building a cube from this either. Please bare with me.


Also, Sorry but I am not sure what you mean by the harder answer if I cannot edit the database. Any chance you could offer assistance with those conditions in mind?

I really do appreciate all of the help, thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search