June 17, 2014 at 6:09 am
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
June 17, 2014 at 6:20 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 11:13 am
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.
June 17, 2014 at 1:00 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 1:17 pm
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'.
June 17, 2014 at 1:32 pm
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 1:57 pm
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.
June 17, 2014 at 2:17 pm
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.
June 17, 2014 at 2:26 pm
How do you link Thomas from View 1 to the measures in View 2?
Is record key the same?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 2:28 pm
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.
June 17, 2014 at 2:34 pm
Koen Verbeeck (6/17/2014)
How do you link Thomas from View 1 to the measures in View 2?Is record key the same?
Yes sir, same key for Thomas in both views.
One thing to keep in mind though is that- view 2 will have multiple records with the same key. One record for each month.
June 17, 2014 at 3:02 pm
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.
This is because your fact table (which is view 2) has only 1 surrogate key (the record key) and that key links to a single link in view 1.
It doesn't link for example to a gender, but instead to the unique combination of name, gender and automobile.
The dates dimension is created on top of view 2 (I'd rather create a dedicated time dimension, but anyway). The key is the date itself.
Now, in the data source view you need to link view 2 to view 1 with the record key.
The measure group is created on top of view 2 and selects only the measures.
This measure group is linked to the dimension of view 1 (in the dimension usage tab) using the record key.
If you want seperate dimensions, you'll need to create views on top of view 1 and assign surrogate keys. It's a bit of a mess this database unfortunately.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 18, 2014 at 5:02 am
Koen Verbeeck (6/17/2014)
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.
I figured as much, but how does one do this?
if it helps at all, I correctly built this cube in Analysis Services 2000 and converted them successfully, attached is what it looks like in the dimension usage tab - however I cannot recreate it. I can't seem to define a relationship between view 2 (measures) and view 1 (dimensions). I had no problem doing this in Analysis Services 2000 and it converted without creating extra views so there must be a way, however I cannot figure out how to do it manually. As I keep saying, thank you for your time.
June 18, 2014 at 5:26 am
Ryan1 (6/18/2014)
Koen Verbeeck (6/17/2014)
The views being what they are, you can only create 1 dimension which holds all the attributes of view 1.I figured as much, but how does one do this?
As you would create any other dimension and measure group in SSAS.
Record key is the surrogate key for the dimension.
When you create the dimension, you simple select all fields from view 1, with record key being the key column.
When you create the measure group, you select all fields from view 2, except the date field.
In the dimension usage tab, you link the measure group to the dimension by using a regular relationship, where recordkey of view1 equals record key of view 2.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 18, 2014 at 11:59 am
This works you are the man! Thank you so much.
Any chance I can make this thing smaller?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply