Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

2008r2 Cube not linking two views on key Expand / Collapse
Author
Message
Posted Tuesday, June 17, 2014 6:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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
Post #1582279
Posted Tuesday, June 17, 2014 6:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582289
Posted Tuesday, June 17, 2014 11:13 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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.


  Post Attachments 
Dimension_Measure_help.jpg (11 views, 50.19 KB)
Post #1582489
Posted Tuesday, June 17, 2014 1:00 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582538
Posted Tuesday, June 17, 2014 1:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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'.
Post #1582557
Posted Tuesday, June 17, 2014 1:32 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582567
Posted Tuesday, June 17, 2014 1:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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.
Post #1582581
Posted Tuesday, June 17, 2014 2:17 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, December 12, 2014 1:54 PM
Points: 643, Visits: 2,149
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.

Post #1582589
Posted Tuesday, June 17, 2014 2:26 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1582593
Posted Tuesday, June 17, 2014 2:28 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 6:59 AM
Points: 43, 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.
Post #1582594
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse