CA Quick Bytes - Modeling 101 Working with Relationships

Posted by LPA Software Solutions on Jul 23, 2021 12:08:39 PM


CA Quick Bytes - Modeling 101 - Working with Relationships


Cognos Analytics Quick Bytes: Modeling 101 Working with Relationships Transcript 

Presenter: Chris Keaton, LPA Director of Business Intelligence 

Chris Keaton: Good day and welcome to Cognos Analytics Quick Bytes with LPA. Our bite size chunks of big functionality. Today’s Modeling 101 session is going to be on working with relationships. If you haven’t seen the other videos “Driving: Modeling with Intent” or “Modeling 101: Creating Merged Tables” you might want to watch those first, if not go ahead and join us I’ll tell you’ve what we’ve done.

We’ve built an intent driven model. We took five product related tables and merged them into a single table called “Products”, and here we have our model at that point. We noticed that the five tables are joined to my “Order details," and my “Products” merged table doesn’t have a relationship so let’s work a little bit with these relationships.

If I left click on the relationship, I’m able to see what it’s all about, visually, tell me a little bit about the fields, as well as whether it’s an Innerjoin, Left-join, etc. If I’m right clicking on that relationship line, I have the ability to edit it, see it as query information, or remove it. In this case, I am going to remove this relationship between the currently hidden product tables that are really the source behind this merge products (plural) that is what my users are going to work with.

In order to create a new relationship, I am going to click on the products table and hold down control and click on order details. I could have also control clicked over here in the data module pane on the left side. Right clicking the object, I have the option to create a new relationship and I will. Into the new relationship, create relationship window; I see table one as “Products”, table two is “Order details” and I see that I have a common product number between the two of them.

Cognos Analytics Quick Bytes Modeling 101 Working with RelationshipsAs I click on a field, I do get a preview over here on the left for the left table, and on the right for the right table. This preview intended to give me a good feel that I’ve got the right data types. If I click on a field that doesn’t seem to match it's going to tell me that might have a little bit of something that I need to investigate. In this case, product number to product number is a sound relationship.

I’ll go ahead and choose match at which point I need to turn my attention to the bottom left which is the sprocket. I’m going to click here and see what relationship type, Cardinality, or Optimization options I want to select for this relationship. If you have any doubts, you can hover over the eye click on it, and it will tell you a little about what you are doing as it relates to those joins, similarly for the Cardinality and the Optimization options.

There is a good amount of reading here to do if you’re not sure about those optimizations, but if you use them correctly, you’ll see that there is a significant performance improvement in your subsequent reports. In this case, I have a good data warehouse that is built exactly to spec, so I’m going to choose this inter-join style relationship because I know I’m not going to drop any records. The cardinality that was selected was one-to-many saying that if you look, I’ve got each row in table one, “Products”, has one or more matching records in table two, “Order Details”. I am okay with leaving that.

My option for optimization was currently defaulted to no filtering. I can think about that for a moment. Unique values work really well if I’ve got a fine number, 20 or less, realistically unique values over here on the left side. The range of values will give you, if my product numbers are between 10-2,000, it’ll do a sub-query (between expression) that will tell you between 10-2,000. In the case of this though since I don’t have a one-to-one relationship and were going to go ahead and look at maybe perhaps using unique values in a sub-query. In this case, the sub-query will return my unique values and optimize my performance.

This choice is personal choice, you think about it, you think about the performance you’re looking to get. Make that choice dependent on your feel about it. If you are in any concern or have any doubts about it go ahead and click no filtering, that will be just fine. It's not like you’re going to experience any errors if you choose “no filtering”, but it is an option to give you a little better performance.

Cognos Analytics Quick Bytes Modeling 101 Working with Relationships 1We then will preview our records, you see our fuscia table on the left, our jade table on the right. Were looking for things like cartesian products, we want to make sure we don’t have any crazy repeats here. I look at the data, I feel pretty good about it, I’m going to go down here and choose “okay”. Now my “Product” table is joined into my “Order details”. Order details exists on the many side of its relationships, so that’s telling me it’s going to function a little bit like a fact table, and that’s exactly how I want it to be.

These relationships can be built, they can be modified the same way. If I go into an existing relationship, right click it and choose edit you’ll see I’ve got the same type of screen, it’s just I’ve already got a matched column so let’s go ahead, click there and we can see that branch code on the left matches the sales branch code on the right. I feel good about that relationship if I needed to change it I absolutely could here. In this case I’m going to go ahead and cancel out and make zero changes.       

Thank you for joining us for today’s Cognos Analytics Quick Bytes with LPA, Modeling 101, and we’ll be back for lots more so check us out here in the future.

Have a wonderful day.

Disclaimer: This transcript was created with AI technology and lightly proofread; please forgive errors you might find. 

From Software Selection to Cognos Implementation & Optimization: Building a Data-driven Culture

Topics: Training, Cognos Analytics, Data Science & AI, Cognos Analytics Quick Bytes