Click here to monitor SSC
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 Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16465 Visits: 13207
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.



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Ryan1
Ryan1
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
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.
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16465 Visits: 13207
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.



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Ryan1
Ryan1
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16465 Visits: 13207
Ryan1 (6/18/2014)
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?


Glad you got it resolved.
What do you want to make smaller?



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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Ryan1
Ryan1
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
Koen Verbeeck (6/18/2014)
Ryan1 (6/18/2014)
This works you are the man! Thank you so much.


Any chance I can make this thing smaller?


Glad you got it resolved.
What do you want to make smaller?


The cube itself is massive. After completing your steps: it seems to increase by a few megabytes every time I hookup a new dimension. It looks like it's creating a new dimension for EVERY key + a new dimension for every dimension that should be hooked up for that key.
Ryan1
Ryan1
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
Sorry to keep bringing this up guys but I've been banging this around for more than week now, took an online course, and have been working through microsofts tutorials and have found nothing. Can anyone point me in a direction of some readings that may suit the topic?

Please let me know if I should provide any more information, thank you and have a nice day.
Nevyn
Nevyn
SSC Eights!
SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)SSC Eights! (960 reputation)

Group: General Forum Members
Points: 960 Visits: 3149
So now the question is just size?

That is pretty tough to answer without getting into specifics (how big is too big? do you need all the data you are including? etc)

You could look at working in ROLAP mode instead of MOLAP, which would save space in the cube, but make things slower (and increase space usage someone in the dw). Other than that, your only method I know of is getting rid of things you don't need.
Ryan1
Ryan1
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 130
Nevyn (7/4/2014)
So now the question is just size?

That is pretty tough to answer without getting into specifics (how big is too big? do you need all the data you are including? etc)

You could look at working in ROLAP mode instead of MOLAP, which would save space in the cube, but make things slower (and increase space usage someone in the dw). Other than that, your only method I know of is getting rid of things you don't need.




I will do my best to answer this fully:

The issue is size. The data is correct, and the measures can be reported along with the dimensions in the same cube.
When I convert the cubes over from Analysis Services 2000 they are but 2 or 3 megs a piece. Unfortunately, when I create the cubes from scratch using Koen Verbeeck method, stated previously in this thread, the cube can easily climb to over 80 mb while only hooking up a few dimensions and I need to hook up many more.

Also, ROLAP option is not my first choice because this scenario will not be fast enough.
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