POWER BI RELATIONSHIPS. I'M PETER MYERS, SO I'M YOUR PRESENTER, AND I HAVE WORKED FOR MANY YEARS IN BUSINESS INTELLIGENCE AND WORKING WITH MODELING, AND I'M ALSO AN STRURKT, SO I GET TO SEE STUDENTS AT DIFFERENT LEVELS, WHETHER THEY'RE ENTRY LEVEL, INTERMEDIATE OR ADVANCED, AND I JUST SEE SO MANY DIFFERENT PROBLEMS, AND WHAT I DETECT OUT OF THESE ARE PATTERNS ON HOW TO SOLVE THEM. SO I'VE PUT TOGETHER THIS PRESENTATION THAT TELLS YOU PRETTY MUCH WHAT RELATIONSHIPS ARE, WHAT DOES WORK, AND WORKS BY DESIGN, AND WHAT ARE SOME COMMON PITFALLS AND THINGS PERHAPS YOU SHOULDN'T DO. OKAY. SO THIS SESSION OUTLINE IS WE'LL COVER WHAT POWER BI MODEL RELATIONSHIPS ARE, AND THEN WE'LL SWITCH STRAIGHT INTO PRACTICE. THROUGH DEMONSTRATION I'LL FOCUS THE KEY THEORY OF THE DOS AND DON'TS. HOW ABOUT WE START WITH THE TOPIC OF PERHAPS WHAT YOU SHOULDN'T DO. SO HERE YOU SEE POWER BI DESKTOP, AND I'VE IMPORTED SOME DATA REPRESENTING SOME SALES ACTIVITY, AND WE END UP WITH A SINGLE TABLE NAMED SALES EXTRACT. THIS IS QUITE COMMON, IN FACT, BECAUSE SOME SYSTEMS EXPORT A DENORMALIZED DSV FILE AND THE TEMPTATION IS LET'S GO AND IMPORT IT AS IS AND START BUILDING OUR REPORTS ON TOP OF IT. WHILE THAT MAY WORK TO SOME DEGREE, IT CERTAINLY ISN'T FRIENDLY. IT'S NOT WELL ORGANIZED. AND AS YOUR DATA VOLUMES GROW, IT BECOMES INEFFICIENT AND NOT OPTIMAL. NEXT IS THAT AS YOU START TO BUILD MORE COMPLEX EXPRESSIONS FOR YOUR MEASURES, IT BECOMES INCREASINGLY DIFFICULT WHEN THEY ARE ALL COLUMNS OF A SINGLE MASSIVE TABLE. SO THAT IS THE BAD EXPERIENCE. I'LL KICK OFF MY DEMO BY DOING THE RIGHT THING HERE, PUTTING IT IN THE TRASH CAN. [ Laughter ] SO LET'S TALK ABOUT SOME THEORY TO BACK UP WHAT GOOD MODELING IS. OR WHAT A GOOD PROJECTION IS. THE PROBLEM UP FRONT HERE, IT'S VERY DARK, AND I NEED A CANDLE OR SOMETHING. THERE WE GO. DO WE SEE THAT? AWESOME. OKAY. SO MODELING RELATIONSHIPS. WHAT ARE THESE RELATIONSHIPS? SO WHAT WE NEED TO APPRECIATE IS THAT AS A TABULAR MODEL DESIGN, WE TYPICALLY HAVE MORE THAN ONE TABLE, AND TABLES, WHEN WE APPLY FILTERS TO THEM, ARE DESIGNED TO PROPAGATE FILTERS TO OTHERS. SO THE KEY TAKEAWAY HERE IS THAT THESE MODEL RELATIONSHIPS WE'RE ABOUT TO TALK ABOUT IN GREAT DETAIL IN THIS SESSION ARE THERE TO PROPAGATE FILTERS TO OTHER TABLES. SO WHAT DOES THIS MEAN? LET'S SEE BY EXAMPLE. I HAVE A SALES TABLE WITH SIMPLISTIC INFORMATION, LIKE A PRODUCT KEY, A YEAR AND A QUANTITY. AND IN THE SAME MODEL I HAVE A PRODUCT TABLE, AND YOU CAN SEE THE FIRST COLUMN OF THE PRODUCT TABLE HAS A UNIQUE COLUMN OF PRODUCT IDS, AND WE ESTABLISH A RELATIONSHIP BETWEEN THEM, SUCH THAT WHEN OUR VISUAL IN A REPORT ASKS WHAT IS THE SALE QUANTITY OF PRODUCT B, THE FILTER PROPAGATION WORKS LIKE THIS. FILTER ON PRODUCT B, PROPAGATE THOSE DOWN TO THE SALES TABLE, AND THEN IT WILL SUM THE VALUES OF 10, 5 AND 2. NOW AS A VARIATION OF THIS, WE MIGHT HAVE ANOTHER TABLE WHERE CATEGORY FILTERS TO PRODUCT AND PRODUCT FILTERS TO SALE. ENABLING US TO ASK A QUESTION AT A HIGHER LEVEL. WHAT WERE THE SALE QUANTITIES FOR CAT A, AND YOU CAN SEE THE PROPAGATION OF FILTERS. TWO FOR CAT A. THE RELATED SALES GIVES US THE RESULT THIS TIME OF 14. OR GUESS WHAT, WE COULD HAVE OTHER TABLES, THIS TIME A DATE TABLE OR YEAR, AND SO WHAT WE'LL DO IS ASK A MORE INCREASING QUESTION WHEN IT COMES TO FILTERS, WHAT WERE THE SALE QUANTITIES FOR CAT A IN 2018, AND THIS IS HOW IT WORKS. AT THE INTERSECTION OF THESE TWO FILTERS IT REVEALS JUST THE ONE ROW, SO THE ANSWER IN THIS CASE IS 11. AND SO PRETTY MUCH WHEN IT COMES TO FILTERS IN YOUR MODELS, IT'S A VARIATION ON THIS DESIGN. AND I'LL POINT OUT AT THIS STAGE THAT YOU'LL SEE THAT THOSE RELATIONSHIPS HAVE A ONE AND A MANY SIDE, THE ASTERISK. AND THIS IS A VERY COMMON DESIGN PATTERN. AND WE'LL TALK ABOUT VARIANTS ON IT TODAY. SO GOOD MODEL DESIGN SHOULD STRIVE TO DELIVER THE RIGHT NUMBER OF TABLES AND THE RIGHT RELATIONSHIPS IN PLACE. AND FOR THOSE THAT HAVE DATA WAREHOUSING BACKGROUND WITH STAR SCHEMA, THIS IS TYPICALLY THOSE THAT HAVE DEVELOPED OR WORKED WITH DATA WAREHOUSES WHICH, BY THE WAY, ISN'T THE IMMEDIATE TARGET AUDIENCE FOR POWER BI DESKTOP. IT'S THE BUSINESS ANALYST THAT LIKELY USED THE DATA WAREHOUSE, BUT NEVER DESIGNED THEM OR HAD TO THINK IN TERMS OF FACT OR DIMENSION TABLES. IF YOU CAN ADOPT SOME OF THIS THEORY, IT GOES A LONG WAY IN ASSISTING YOU TO DESIGNING AN OPTIMAL POD MODEL IN POWER BI DESKTOP. THERE IS SOME CONFUSION THAT THOSE THAT COME FROM RELATION BACKGROUND. IN A DIAGRAM WE SEE THEM AS RELATIONSHIPS TWEEB TABLES, BUT THEIR PURPOSE IS VERY DIFFERENT. ALTHOUGH COMMONLY WHERE YOU HAVE A FOREIGN KEY CONSTRAINT ENFORCING INTEGRITY ACROSS TABLES, THEY ARE LIKELY TO BECOME RELATIONSHIPS IF YOU HAVE THOSE TABLES IN YOUR MODEL. AND THE OTHER THING I'LL SAY IS THIS IS NOT AN EXACT SCIENCE, THAT THE DECISION OF THE TABLES AND THE COLUMNS AND THE RELATIONSHIPS YOU ESTABLISH, THEY COULD BE MANY WAYS TO SOLVE THE SAME PROBLEM. SO THERE'S A BALANCE THERE BETWEEN SCIENCE AND ART. ALL RIGHT, SO A QUICK BACKGROUND FOR THOSE THAT DON'T HAVE STAR SCHEMA. WE HAVE THE CONCEPT OF A FACT TABLE AND WHY IT'S CALLED A STAR SCHEMA IS THE POINTS OF THE STAR ARE WHAT WE CALL DIMENSION TABLES. SO IN THIS CASE, IF ANYONE RECOGNIZES THE DATABASE, WHERE DID THIS DIAGRAM COME FROM? THE ADVENTURE WORKS DATA WAREHOUSE AS AN INSTRUCTOR AND DEMONSTRATIONS IT'S BEEN INCREDIBLY USEFUL OVER THE YEARS. WELL LET'S UNDERSTAND WHAT THE PURPOSE OF THE TWO DIFFERENT TYPES OF TABLES ARE. THIS IS A WORD-HEAVY SLIDE, SO LET ME SUMMARIZE IT. DIMENSION TABLES ARE THERE TO DESCRIBE YOUR BUSINESS ENTITIES, THE THAINGS THAT YOU'RE MODELING. AND WHEN YOU LISTEN TO THE REQUIREMENTS FOR YOUR MODEL, YOU'RE LISTENING FOR THAT TWO-LETTER WORD, BY. I NEED TO SEE SALE QUANTITY BY YEAR, BY CATEGORY, BY PRODUCT. AND WHEN YOU HEAR THAT, IT TELLS YOU THAT IN YOUR MODEL YOU'LL NEED A TABLE AND COLUMNS TO SUPPORT THAT. ALL RIGHT, SO ESSENTIALLY DIMENSION TABLES DESCRIBE YOUR THINGS, INCLUDING TIME ITSELF, AND WHEN IT COMES TO THE ANALYTIC QUERIES THAT POWER BI WILL EXECUTE ON YOUR BEHALF AND VISUALIZE IN REPORTS, DIMENSION TABLES SUPPORT TWO THINGS: FILTERING AND GROUPING. NOW THE FACT TABLES, THEY REPRESENT AN ACCUMULATION OF YOUR BUSINESS ACTIVITY. SALES, TEMPERATURE READINGS, BUDGETS, YOU NAME IT. THE PURPOSE OF FACT TABLES ARE TO DELIVER MEASURES OR SUMMARIZATION OF DATA. SO IN AN ANALYTIC QUERY, THERE ARE THREE DISTINCT PHASES, TO FILTER, GROUP AND ACHIEVE SUMMARIZATION. AND SO START ASKING YOU TO THINK THAT YOUR MODEL DESIGN NEEDS TO HAVE TABLES THAT MAP TO THIS. TABLES THAT ARE DIMENSION TABLES FOR FILTERING AND GROUPING AND RELATIONSHIPS ACROSS TO FACT TABLES THAT WILL ACHIEVE SUMMARIZATION. SO WHEN WE HAVE A LOOK AT POWER BI AND THE OBJECTS WITHIN A MODEL, THERE IS THE TABLE ITSELF MORE OFTEN THAN NOT THAT TABLE ARRIVES IN YOUR MODEL BECAUSE YOU HAVE A POWER QUERY THAT WHEN APPLIED LOADS DATA INTO YOUR MODEL. AND THERE ARE PROPERTIES THAT WE CAN CONFIGURE, LIKE THE NAME OF THE TABLE, THE DESCRIPTION, WHICH MIGHT APPEAR AS A TOOL TIP IN THE FIELDS PANE. MORE RECENTLY WE'VE SEEN A STORAGE MODE PROPERTY, LIKE DOES IT REPRESENT IMPORT DATA, DIRECT QUERY OR BOTH. AND THEN A PROPERTY CALLED TABLE ROLE. IS IT A DIMENSION OR IS IT A FACT? AND I'VE JUST MADE THAT UP FOR IMPACT. THERE IS NO SUCH PROPERTY IN A TABULAR MODEL THAT DECLARES THAT THAT TABLE IS A DIMENSION TABLE OR A FACT TABLE. THOSE TERMINOLOGIES DO NOT APPEAR ANYWHERE INSIDE POWER BI DESKTOP. WHEN WE HAVE A LOOK AT THE RELATIONSHIPS THAT WE'LL ESTABLISH, LET ME GET THE THEORY OUT OF THE WAY NOW. THOSE RELATIONSHIPS TAKE PLACE BETWEEN TWO TABLES FROM ONE COLUMN TO ANOTHER COLUMN. THERE ARE NO MULTI-COLUMN RELATIONSHIPS. THERE ARE NO SELF-REFERENCING RELATIONSHIPS BACK TO THE SAME TABLE, SO IT'S AS SIMPLE AS THAT. ONE COLUMN IN ONE TABLE MUST PROPAGATE FILTERS TO ANOTHER COLUMN IN A DIFFERENT TABLE. THE PROPERTIES THEN CONSIST OF WHAT IS THE CARDINALITY. SO YOU HAVE ALL FOUR POSSIBILITIES OF ONE AND MANY. FILTER DIRECTIONS, SINGLE OR BOTH. DOES IT APPLY SECURITY FILTERS? WE WON'T SEE THAT IN DEMO, BUT IF YOU'RE ENFORCING ROW-LEVEL SECURITY, YOU MIGHT ENABLE THAT PROPERTY TO ENSURE FILTER PROPAGATION ENFORCES ROW LEVEL PERMISSIONS AS YOU EXPECT. IS THE RELATIONSHIP ACTIVE OR NOT? WE'LL EXPLORE THIS. AND DOES IT ASSUME REFERENTIAL INTEGRITY WHICH IS ONLY A DIRECT QUERY MODEL DESIGN. THEREFORE, THE QUERY SENT TO THE UNDERLYING SOURCE WILL USE INNER JOINTS RATHER THAN OUTER JOINS. NOTE THERE CAN ONLY BE ONE ACTIVE PATH BETWEEN THE TWA TABLES IN A MODEL. I'LL DESCRIBE THIS IN DEMONSTRATION. IT WILL MAKE MUCH MORE SENSE. IT IS THE CARDINALITY PROPERTY THEN THAT DETERMINES THE ROLE THAT YOUR TABLE PLAYS. WE DON'T HAVE A PROPERTY THAT DECLARES A TABLE AS A DIMENSION OR A FACT, BUT WHICH SIDE OF THE RELATIONSHIP WITH THE CARDINALITY WILL DETERMINE WHETHER IT'S FILTER GROUP BEHAVIOUR OR SUMMARIZED BEHAVIOUR. SO WITH THAT NEW KNOWLEDGE, IF WE TOOK THAT STAR, WE WOULD SEE THIS PATTERN, THE ONE SIDE IS ALWAYS ON THE DIMENSION TABLE SIDE. YOU WILL HAVE A COLUMN CONTAINING UNIQUE VALUES, LIKE THE PRODUCT STOCK KEEPING UNITS. AND AS WE'D EXPECT IN A SALE FACT TABLE, HOPEFULLY YOUR BUSINESS IS SUCCESSFUL ENOUGH THAT YOU SELL THE SAME PRODUCT MORE THAN ONCE, RIGHT? SO OBVIOUSLY ON THE FACT TABLE SIDE, THE PRODUCT COLUMN WILL CONTAIN DUPLICATES. VERY COMMON PATTERN TO SEE. ALL RIGHT, I'LL JUST INTRODUCE UP FRONT THAT IN THE DAX FUNCTION LIBRARY THERE ARE SOME FUNCTIONS DEDICATED TO WORKING WITH RELATIONSHIPS, AND I'LL DEMONSTRATE AS MANY OF THESE AS I CAN WITHIN THE TIME I HAVE FOR THIS SESSION. AT THE BOTTOM OF THE PATH FUNCTIONS, I'LL MENTION THEM RIGHT NOW BECAUSE I WON'T DEMO THEM. WHILE THERE ISN'T A CAPABILITY TO HAVE SELF-REFERENCING RELATIONSHIPS, REMEMBER RELATIONSHIPS BETWEEN TWO DIFFERENT TABLES IN THE MODEL, IF YOU DO HAVE THAT CHARACTERISTIC IN YOUR DATA, TYPICALLY USED TO GENERATE A PARENT-CHILD HIERARCHY, LIKE AN ORGANIZATIONAL CHART OF EMPLOYEES, GENERAL LEDGER STRUCTURES OFTEN CONFORM TO THIS, YOU CAN USE THE PATH FUNCTIONS TO HELP NATURALIZE THESE INTO COLUMNS AND THEN BUILD A FIXED LEVEL HIERARCHY. THERE'S NO CONCEPT OF A CHILD-PARENT HIERARCHY INSIDE TABULAR. ARE WE READY FOR SOME GOOD MODELING? THAT'S ALL OF THE THEORY AS IT STANDS, BUT NOW IT'S TIME TO SEE THE APPLICATION OF THAT. ALL RIGHT, SO WHERE IT BEGINS IS I HAVE A POWER BI DESKTOP FILE. I'VE JUST DELETED A TABLE THAT WAS NOT UP TO SCRATCH. AND THE VERY FIRST THING THAT I'M GOING TO DO IS COME TO MY FILE OPTIONS BECAUSE THERE ARE SOME PROPERTIES AND OPTIONS HERE UNDER DATA LOAD THAT ARE SPECIFIC TO RELATIONSHIPS. ALL RIGHT? AND YOU'LL SEE THAT IT WILL IMPORT THE MINUTE IT DETECTS THEM AS FOREIGN KEY CONSTRAINTS IN YOUR SOURCE, OR THE OTHER OPTION THERE IS IT MIGHT AUTODETECT THEM AND CREATE THEM FOR YOU AUTOMATICALLY. BUT THAT'S NOT A LOT OF FUN. OKAY? IT'S MORE EDUCATIONAL FOR ME TO DO IT RATHER THAN HAVE IT DO IT ON OUR BEHALF. SO I DON'T SUGGEST THAT YOU TURN THESE OFF, BUT IN DEMONSTRATION I'LL START THAT WAY. AND HAVING DONE THAT, IT'S NOW TIME TO BRING IN SOME DATA INTO MY MODEL. AND I'M DELIBERATELY KEEPING THE DATA REALLY SIMPLE. THIS COMPANY DOESN'T SELL A LOT. IT HAS THREE SALES TRANSACTIONS. HERE WE SEE A FACT TABLE OF SALES. JUST TO BECOME ACQUAINTED WITH THE DATA, EACH SALE HAS AN ORDER DATE, A SHIP DATE, A STOCK KEEPING UNIT FOR THE PRODUCT, THE MANAGER'S FIRST AND LAST NAME. THE SALE QUANTITY AND THE SALE PRICE. SO I WILL USE TRUSTEE POWER QUERY TO CONNECT TO THAT EXCEL DOCUMENT, AND THERE'S THE SALE DATA. AND WE'LL JUST BRING IT ALL IN. THERE'S JUST ONE THING MISSING. WE NEED TO ANALYZE SALES, SO I'M GOING TO TAKE THAT QUANTITY AND MULTIPLY IT BY THE SALE PRICE AND PRODUCE A COLUMN THAT IS THE PRODUCT. SO THAT WILL BECOME MY SALE AMOUNT. NEXT WE HAVE SOME PRODUCT. SO THE PRODUCT IN FACT COMES FROM A DIFFERENT SOURCE. IN A CSV DOCUMENT, I HAVE THE PRODUCTS. VERY SIMPLE. JUST THE FOUR PRODUCTS. SO THERE'S A SINGLE TRANSFORMATION I NEED TO DO HERE, AND THAT IS PROMOTE THE FIRST ROW'S HEADERS, AND YOU SEE WE'VE GOT A UNIQUE COLUMN OF STOCK-KEEPING UNITS, AND THEN WE HAVE A PRODUCT AND IT'S ASSIGNED TO A SUBCATEGORY. NOW THERE HAPPENS TO BE CATEGORIES AS WELL, BUT THEY COME IN A DIFFERENT FILE. SO I'M GOING TO GO BACK TO EXCEL AND FIND THAT I'VE GOT A SUBCATEGORY WORKBOOK, AND THIS WORKBOOK ALLOWS ME TO MAP A SUBCATEGORY TO ITS CATEGORY. BUT WAIT, THERE'S MORE. THE LIST PRICE OF A PRODUCT ISN'T CONTAINED IN EITHER OF THOSE DOCUMENTS, SO FOR THE LIST PRICE I NEED TO COME BACK TO EXCEL, AND WE'LL FIND THAT FOR EACH PRODUCT WE MAINTAIN THIS IN A SEPARATE DOCUMENT. SO FOR EACH STOCK-KEEPING UNIT, WE SEE ITS LIST PRICE. AND YET THERE'S MORE. SO CONTAINED WITHIN A CSV DOCUMENT I HAVE MY MANAGERS. REMEMBER MANAGERS ARE ASSIGNED TO SALES. ALL RIGHT, SO I'LL BRING THESE IN, AND I'LL APPLY A TRANSFORM TO USE THE FIRST ROW AS THE COLUMNS. NOW TAKE A CLOSE LOOK AT THIS. SOMETHING A LITTLE INTERESTING. IT'S NOT JUST DESCRIBING MANAGERS, RIGHT? WE HAVE THEIR FIRST AND LAST NAME AND AN EMAIL ADDRESS, BUT THE LAST COLUMN INTRODUCES ANOTHER DIMENSION, THAT THERE IS SOME ASSOCIATION BETWEEN A MANAGE R AND A SUBCATEGORY. FOR THAT REASON WE SEE THAT IS IT FRED OR BEE ACTUALLY IS ASSIGNED TO TWO SUB-CATEGORIES. SO THAT RELATIONSHIP WE'RE GOING TO REFER TO AS A MANY-TO-MANY, THAT MANAGERS ARE ASSIGNED FOR PERFORMANCE REASONS WHEN IT COMES TO ANALYZING THEIR PERFORMANCE, THEY'RE ASSIGNED AT A SUBCATEGORY LEVEL AND CAN BE ASSIGNED TO ONE OR MORE. SO AS A MANY-TO-MANY, WE COULD INTERPRET THAT, AS A SUBCATEGORY COULD HAVE MULTIPLE MANAGERS ASSIGNED TO IT, AND IT WORKS THE OTHER WAY, ALL RIGHT, THAT THE CATEGORIES CAN HAVE MULTIPLE MANAGERS. SO THIS IS NOT IN A STATE READY TO LOAD, OKAY? I REALLY NEED TO FOCUS ON SINGLE DIMENSIONS, NOT LOAD THEM TOGETHER, AND SO WHAT I'M GOING TO DO IS RENAME THE QUERY AS MANAGER RAW. OKAY? AND I'M GOING TO DISABLE THE LOAD OF IT, AND THEN I'M GOING TO REFERENCE IT IN A NEW QUERY WHICH I'LL NAME MANAGER. AND THIS GIVES ME THE OPPORTUNITY TO THEN REMOVE THE SUBCATEGORY, REMOVE DUPLICATES, AND THERE IS MY LIST OF MANAGERS. I WILL TAKE THIS OPPORTUNITY TO INTRODUCE THE MANAGER AS A FULL NAME. AND NOW I HAVE FIVE QUERIES THAT REPRESENT THE SUBJECT AREA. SO I'M JUST GOING TO GO AHEAD AND APPLY THOSE POWER QUERIES, AND NOW THE MODEL IS BORN. EACH OF THOSE BECOMES A TABLE IN THE MODEL, AND SWITCHING BACK TO POWER BI DESKTOP, THEN, I'M GOING TO USE THE MODEL DESIGN TO LAY THESE OUT IN A LOGICAL KAY WAY, AND I'M THINKING STAR SCHEMA. THERE'S MY PRODUCT IN THE MIGDZ MIDDLE. I CAN SEE LIST PRICE HERE. NO RELATIONSHIPS AUTODETECTED BECAUSE I TURNED THAT OFF. NOW CRITICAL TO ALL MODEL DESIGN IS TO HAVE A DATE TABLE. SO I WILL GO AHEAD AND CREATE A TABULATED TABLE TO DELIVER DATE, AND SO I CAN ACHIEVE THAT ON THE MODELING RIBBON WITH A NEW TABLE. AND THE DATE WILL SIMPLY EQUAL THE CALENDAR AUTOFUNCTION. SO I'LL KEEP THIS CONVERSATION SHORT, BUT THIS FUNCTION WILL DELIVER A FULL YEAR RANGES OF DATES ENCOMPASSING WHAT IS ALREADY LOADED INTO THE MODEL. REMEMBER THERE ARE TWO DATE COLUMNS IN THE SALE FACT: THE ORDER DATE AND THE SHIP DATE. I'M GOING TO ENHANCE THIS WITH ADDITIONAL COLUMNS LIKE YEAR. SO IN RECOGNITION THAT I HEARD THAT "BY" WORD USED THAT SOMETIMES WE NEED TO GROUP OR FILTER BY THE YEAR OF THE SALE, AND I'LL JUST ADD ONE MORE, WHICH IS THE MONTH. AND I'LL USE THE FORMAT FUNCTION HERE TO FORMAT THE DATE VALUE USING THE YYY-MM. THE LAST THING THAT I'LL DO IS MARK THAT AS A DATE TABLE, AND I AM GOOD TO GO. AND THEN I'LL SWITCH BACK TO THE MODEL DIAGRAM, AND WE'LL SEE THAT THE STAR SCHEMA IS TAKING SHAPE WITH OUR FACT TABLE IN THE MIDDLE. ALL RIGHT, BEFORE I GO AHEAD AND CREATE ANY RELATIONSHIPS, I DO LIKE TO DEMONSTRATE WHAT IT LOOKS LIKE WHEN THERE'S AN ABSENCE OF RELATIONSHIPS. I'M GOING TO CREATE A TABLE THAT SAYS, OKAY, LET'S HAVE A LOOK AT THE MONTHS, AND WE WANT TO KNOW WHAT THE SALES WERE THAT WERE ACHIEVED IN THOSE MONTHS. AND WHEN YOU SEE THIS, YOU MIGHT SCRATCH YOUR HEAD AND THINK, WELL, THAT'S JUST A COINCIDENCE, AND MAYBE IT IS. WHAT DO YOU THINK? THAT WE HAPPENED TO HAVE SOLD $15 WORTH OF SALES EVERY MONTH, DOES THAT SEEM REASONABLE? WELL, COME ON OF COURSE IT ISN'T BECAUSE WE'VE JUST SEEN THAT THERE ARE ONLY THREE FACTS IN THIS TABLE. WHEN YOU SEE A REPEATING VALUE, AND A BIG GIVE AWAY IS THIS, EVEN THE TOTAL COMES TO 15, THERE IS NO FILTER PROPAGATION TAKING PLACE. THAT VISUAL IS ASKING FOR A GROUP BY YEAR AND THEN SUM OF SALES. THE ABSENCE OF PROPAGATION SAYS YOU'RE GETTING THE TOTAL OF ALL SALES FOR EVERY YEAR. LET ME GO AHEAD AND OPEN UP THE MANAGE RELATIONSHIPS WINDOW, GO AHEAD AND CREATE A RELATIONSHIP FROM THE SALE TABLE DOWN TO THE DATE TABLE, MAPPING ORDER DATE TO DATE, AND JUST CLICK OKAY. AND I LIKE DOING IT THIS WAY BECAUSE YOU SEE INSTANT VALIDATION THAT, AHA, NOW THAT'S MAKING SENSE. THE EASIER WAY, THOUGH, TO CREATE RELATIONSHIPS IS TYPICALLY TO USE THE DRAG-DROP APPROACH. FROM THE MANY SIDE TO THE ONE SIDE. SO WHAT DO I HAVE HERE? I HAVE STOCK-KEEPING UNITS DRAG DROP TO STOCK-KEEPING UNIT. SUBCATEGORY DRAG DROP TO SUBCATEGORY. STOCK-KEEPING UNIT DRAG TO STOCK-KEEPING UNIT, AND NOW I'M STUCK. MANAGER DOESN'T PROVIDE ME A SINGLE COLUMN THAT I COULD RELATE TO THE SALE. AND THIS IS WHERE I NEED TO GO BACK TO MY EDUCATE -- DATA PREPARATION PHASE AND RE-WORK THINGS. SO I'LL OPEN UP POWER QUERY, AND I HAVE THE MANAGER TABLE HERE. THE ANSWER OF COURSE IN A DATA WAREHOUSE WOULD BE TO DO WHAT? >> SURROGATE KEY. >> THANK YOU. SO WE WANT A UNIQUE IDENTIFIER AS A SINGLE COLUMN. HERE IN MY MANAGER QUERY THAT'S EASILY SOLVED BY COMING TO THE ADD COLUMN RIBBON. IN THIS CASE I'LL CHOOSE A ONE-BASED INDEX. RENAME THIS AS MANAGER KEY. I'LL UPDATE THE DATA TYPE TO A WHOLE NUMBER. AND THAT FIXES UP THE DIMENSION SIDE. I NOW HAVE THIS ONE COLUMN, BUT I NEED TO GIVE CONSIDERATION TO HOW IT'S GOING TO RELATE TO SALE. SO HERE I'M GOING TO PERFORM A MERGE AND MERGE THE SALE TO THE MANAGER. NOW THIS WINDOW LOOKS SO REMARKABLY LIKE THE MANAGE RELATIONSHIP WINDOW. IT IS NOT THE SAME CONCEPT. ONE COLUMN RELATING TO ONE COLUMN. HERE THIS IS ACTUALLY MERGING TWO QUERIES TOGETHER BASED ON COMMON COLUMN OR COLUMNS. WE CAN MULTI-COLUMN JOIN HERE. SO I'M PRESSING THE CONTROL KEY, AND I'M SELECTING FIRST NAME THEN LAST NAME, AND PAY ATTENTION TO ORDER MATTERS IN THE WAY THEY'RE GOING TO MAP, AND THEN I DO THE SAME THING DOWN HERE, FIRST NAME AND LAST NAME. SO THIS IS INTEGRATING TWO QUERIES TOGETHER. REMEMBER THEY COME FROM DIFFERENT SOURCES, AND THEN I'M GOING TO INTRODUCE SIMPLY THE MANAGER KEY. AND NOW THAT I HAVE MANAGER KEY IN THE SALE QUERY, NOW'S THE OPPORTUNITY TO REMOVE THESE TWO. SO LET ME GO AHEAD AND APPLY, AND NOW I'VE SATISFIED THE REQUIREMENT FOR THAT RELATIONSHIP THAT THERE NEEDS TO BE A ONE SIDE AND A MANY SIDE IN THE DATA. AND THEN IT'S JUST A MATTER OF DRAGGING MANAGER KEY TO MANAGER KEY. A STAR IS BORN. OKAY, WELL, LET'S GIVE IT A TEST DRIVE. WE'VE ALREADY SEEN HERE THAT WE'VE GOT MONTHLY SALES. NOW WHAT'S GOING TO HAPPEN AS OUR BUSINESS BECOMES MORE AND MORE SUCCESSFUL AND, YOU KNOW, WE SELL SOME MORE THINGS? SO IN APRIL WE SOLD THE SAME THING. AND MAYBE WE DID IT AGAIN, JUST MAKING UP SOME DATA. SAVE. COME BACK TO OUR MODEL. IT'S TIME TO REFRESH. WE'RE GETTING AN ERROR. SOMETHING ISN'T RIGHT. AND WE COULD READ THE MESSAGE, BUT TIME DOESN'T REALLY ALLOW. LET ME TELL YOU WHAT'S GOING ON. TAKE THE CLOSE LOOK AT THE RELATIONSHIP SPECIFICALLY TO THE CARDINALITY. ONE TO MANY. ONE TO ONE. THAT ONE WE KNOW IS OKAY. THERE'S A LIST PRICE FOR EACH PRODUCT, BUT HERE IS THE PROBLEM. A ONE-TO-ONE, AND WHEN YOU SEE A ONE SIDE POINTING TO A FACT TABLE, SOMETHING ISN'T RIGHT. WHAT YOU'RE DICTATING IN YOUR MODEL DESIGN IS YOU CAN ONLY EVER SELL THAT PRODUCT ONCE, AND I'M NOT SURE HOW LONG YOU'LL REMAIN IN BUSINESS. NOW THE REASON THAT THE DRAG DROP TECHNIQUE LET US DOWN WAS THAT THERE WERE UNIQUE COLUMNS IN BOTH SIDES, LIKE POWER BI UNDERSTOOD WHAT WAS IN THE COLUMN. SO IT JUST SAID, OKAY, UNIQUE, UNIQUE, IT MUST BE A ONE-TO-ONE. SO IT REQUIRES ME IN TWO INSTANCES TO COME AND DOUBLE CLICK THAT RELATIONSHIP AND SAY, ACTUALLY NO, THAT IS A MANY-TO-ONE RELATIONSHIP, AND I ONLY WANT A SINGLE FILTER DIRECTION. WE'LL TALK ABOUT THIS IN MORE DETAIL SHORTLY. AND THEN DATE HAD THE SAME PROBLEM. WE HAD UNIQUE DATES ON BOTH SIDES, SO I'LL SWITCH THIS TO A MANY-TO-ONE AND BACK TO THE DEFAULT OF SINGLE FILTER. NOW LET'S GO AHEAD AND SEE IF I CAN REFRESH THAT CELL DATA. OKAY, WE HAVE SUCCESS. SO THE POINT ABOUT THIS IS THE FACT TABLE WILL ALWAYS, AS WE CAN SEE HERE, BE THE MANY SIDE OF THOSE RELATIONSHIPS. OKAY. WELL LET'S TAKE A LOOK AT SOME DAX FUNCTIONS. SO I HAVE SOME SNIPPETS HERE, AND THE FIRST ONE I'M GOING TO DO IS DEMONSTRATE THE RELATED FUNCTION. SO THERE ARE A FAMILY OF FUNCTIONS RELATED AND RELATED TABLE THAT NAVIGATE RELATIONSHIPS. SO THIS ONE HERE IS GOING TO CREATE A CALCULATED COLUMN ON THE SALES TABLE, AND NAVIGATING IN THE ONE DIRECTION OF THE RELATIONSHIPS, IT'S GOING TO ADD A COLUMN THAT IS THE CATEGORY OF THE PRODUCT THAT WAS SOLD. NOW YOU REALLY SHOULDN'T DO THIS BECAUSE WE ALREADY HAVE THIS DATA IN THE MODEL THE WAY WE WOULD ACHIEVE KNOWING CATEGORY SALES IS TO FILTER BY CATEGORY, LET THE PROPAGATION FILTER THE SALE TABLE AND THEN ACHIEVE THE SUMMARIZATION OF IT. BUT JUST FOR DEMONSTRATION, THE RELATED FUNCTION, IN FACT, HAS TRAVERSED OVER TO RELATIONSHIPS FROM SALE TO PRODUCT CONTINUING IN THE ONE DIRECTION TO THE SUBCATEGORY TABLE. DON'T DO THIS AT HOME, GUYS. SO I'LL GO AHEAD AND DELETE IT. THE NEXT ONE IS A LITTLE MORE INTERESTING BECAUSE STILL USING THE RELATED FUNCTION, IT'S DOING THIS, THAT THE DISCOUNT AMOUNT IS FOR WHATEVER THE PRODUCT IS GOING AND GET ITS RELATED LIST PRICE, MULTIPLY BY THE QUANTITY AND SUBTRACT THE SALE AMOUNT. LOGICALLY ANY DIFFERENCE MEANS THERE MUST HAVE BEEN A DISCOUNT APPLIED. SO THAT IS A VALUABLE COLUMN FOR THE ANALYSIS THAT WE KNOW WE'RE GOING TO DO. ALL RIGHT? GET THE RELATED LIST PRICE AND THEN GENERATE ANY DIFFERENCE. I WILL KEEP THIS ONE. AND LET'S JUST TAKE A LOOK FROM THE SUBCATEGORY SIDE THAT I COULD ALSO USE THE MANY DIRECTION, AND THAT'S WHAT THE RELATED TABLE FUNCTION DOES. SO ON THE SUBCATEGORY TABLE, LET'S COUNT THE NUMBER OF ORDERS THAT EACH OF THOSE SUB-CATEGORIES HAS ACHIEVED. RELATED TABLE RETURNS A TABLE, SO WE NEED TO SUMMARIZE IT, AND THE COUNT ROWS COUNTS THE ROWS OF THAT TABLE. AGAIN, YOU SHOULD NOT DO THIS. YOU SHOULD NOT INCREASE THE SIZE OF THE MODEL BY ADDING THESE UNNECESSARY COLUMNS THAT CAN BE EASILY EVALUATED AT QUERY TIME, SO I WILL ATHLETE IT. BUT NOW YOU'VE BEEN INTRODUCED TO THE RELATED AND THE RELATED TABLE FUNCTIONS DELETE IT. WITCHING BACK TO THE MODEL -- SWITCHING BACK TO THE MODEL, LET'S PAY TOENGS TO THE ONE-TO-ONE RELATIONSHIP. WHILE THERE'S SUPPORT FOR THIS ONE-TO-ONE, I DON'T USE IT. I LIKE TO KEEP MY FIELD PANE AND THE TABLES AS WELL ORGANIZED AS POSSIBLE. I DON'T WANT TOO MANY TABLES IS WHERE I'M HEADING. IF THERE TRULY IS A ONE-TO-ONE RELATIONSHIP, LET'S TAKE THE COLUMNS AND CONSOLIDATE THEM INTO THE PRODUCT TABLE. SO BACK TO THE QUERY EDITOR, I COME TO PRODUCT AND I MERGE QUERIES, AND I MERGE FROM THE PRODUCT LIST PRICE RELATING STOCK-KEEPING UNIT TO STOCK-KEEPING UNIT. I THEN INTRODUCE THE LIST PRICE. AND THEN IMPORTANTLY I DISABLE THE LOAD ON THE PRODUCT LIST PRICE BECAUSE I NO LONGER NEED THAT DATA IN THE MODEL ITSELF. YES, I'M PREPARED FOR THAT TABLE TO DISAPPEAR FROM THE MODEL, AND WE'LL GO AHEAD AND APPLY THOSE CHANGES. ALL RIGHT, ALL SHOULD BE OKAY EXCEPT OF COURSE THERE WAS A DEPENDENCY THAT THIS MEASURE OR RATHER THIS COLUMN WAS REFERENCING A LIST PRICE IN A TABLE THAT NO LONGER EXISTS. SO THAT'S JUST A MATTER OF RE-FACTORING, THAT LIST PRICE AS A COLUMN IS NOW FOUND WITHIN THE PRODUCT TABLE, AND WE'RE BACK ON TRACK. ALL RIGHT, SO ONE-TO-ONE. YES, YOU CAN DO IT, BUT THE RECOMMENDATION IS KEEP THE NUMBER OF TABLES AS FEW AS POSSIBLE. NOW THERE'S ANOTHER OPPORTUNITY FOR CONSOLIDATATION, AND YOU THINK ABOUT IT, THEY ALL BELONG TO THE ONE FAMILY. IT JUST SO HAPPENED, THOUGH, THAT THEY CAME FROM DIFFERENT FORMATS, EXCEL AND CSV. SO I JUST FOLLOWED WHAT THE DATA WAS, AND I JUST PRESENTED THEM AS SEPARATE TABLES. IN DATA WAREHOUSE TERMINOLOGY, WE'D CALL THIS A SNOWFLAKE DIMENSION. WE COULD KEEP IT THIS WAY, BUT THE SIDE EFFECT IS THERE'S A CLEAR HIERARCHY FROM CATEGORY TO SUBCATEGORY TO PRODUCT. AND I LIKE TO DECLARE THESE IN MY MODEL BECAUSE IT'S IMPORTANT FOR REPORT OWNERS TO KNOW THERE'S AN OPPORTUNITY TO DRILL DOWN AND DRILL UP AND USE MATRICES AND TREE MAPS. A HIERARCHY, HOWEVER, ITS LEVELS CAN BE BASED ON COLUMNS THAT COME ONLY FROM A SINGLE TABLE. SO HAVING PRODUCT STANDING ACROSS TABLES ISN'T GOING TO WORK. I'LL TAKE THE OPPORTUNITY TO DO THE SAME THING, RETURN TO POWER QUERY, COME TO MY PRODUCT AND MERGE IN THE PRODUCT SUBCATEGORY, THIS TIME RELATING SUBCATEGORY TO SUBCATEGORY, INTRODUCING CATEGORY, AND THEN DISABLING THE LOAD ON SUBCATEGORY. AND IN MY OPINION, THAT'S A MUCH NICER MODEL. LOOK AT THIS. WE HAVE A SINGLE PRODUCT TABLE EXPRESSING ALL RELATED FIELDS TO PRODUCTS, AND THEN IT GIVES ME THAT OPPORTUNITY TO DO THIS. RIGHT CLICK CATEGORY, CREATE A HIERARCHY, NAME THE HIERARCHY PRODUCTS. I'M NOT SURE WHY THIS IS IN AN ADVANCED GROUP. PRETTY MUCH THE LEVELS OF THE HIERARCHY IS PRETTY STABLE. BUT IT'S HERE THAT I COME AND INTRODUCE THAT I CAN'T SUBCATEGORY AS THE SECOND LEVEL PRODUCT AS THE LOWER LEVEL, APPLY THE CHANGES. THAT CAN ONLY BE ACHIEVED BECAUSE ALL OF THE COLUMNS CAME FROM A SINGLE TABLE. THE NEXT THING TO NOTICE IN THE MODEL IS THAT SALE ALSO HAS A SHIP DATE. SO THERE ARE TWO DATES THAT ARE RECORDED AGAINST A SALE. WHEN WAS IT ORDERED? WHEN WAS IT SHIPPED? AND SO LET'S GO AHEAD AND CREATE ANOTHER RELATIONSHIP TO THE DATE TABLE. AND WHAT YOU'LL SEE IS THAT POWER BI DESKTOP IS HAPPY TO CREATE THAT RELATIONSHIP, BUT IT LOOKS DIFFERENT. AND WHAT YOU SEE VISUALLY IS THE ACTIVE VERSUS INACTIVE PROPERTY. THE SOLID LINE IS ACTIVE THAT IS A DEFAULT PROPAGATION, THE DASH LINE IS INACTIVE. IT'S ONLY ACTIVE IF AN EXPRESSION ENGAGES IT, ALL RIGHT? SO WHAT WE COULD DO IS PLAY THIS WITH A LITTLE. WE COULD COME BACK TO THE REPORT AND SAY, OKAY, IF I MANAGE RELATIONSHIPS HERE AND I TURN OFF THE ACTIVE FLAG FOR THE ORDER DATE AND I MAKE THE SHIP DATE ACTIVE, WHAT'S GOING TO HAPPEN? AND NOW YOU SEE A DIFFERENT PROPAGATION TAKING PLACE. THOSE MONTHS ARE FILTERING SALE BY SHIP DATE AND WE'RE SEEING SUMMARIZATION AT THIS LEVEL. AND LET ME PUT THAT BACK. THE CHALLENGE WE THEN HAVE IS THAT THERE CAN ONLY BE ONE ACTIVE PATH BETWEEN TWO TABLE, AND THEREFORE IT'S ONE OR THE OTHER, WHICH COULD BE A LITTLE FRUSTRATING IF YOU NEED TO VISUALIZE WHAT WAS ORDERED AND SHIPPED AT THE SAME TIME. SO ONE APPROACH TO SOLVE THIS, LET ME FLICK THEM BACK TO ORDER DATE, AND BY THE WAY, I WOULD TAKE THE OPPORTUNITY TO COME TO MY DATE TABLE AND ITS PROPERTIES AND MAKE THIS SUPER CLEAR IN A DESCRIPTION. FILTER SALE BY ORDER DATE BECAUSE THOSE THAT ARE USING YOUR MODEL DON'T ACTUALLY SEE THE FILTER PROPAGATION. AND BECAUSE IT'S A GENERICALLY NAMED TABLE "DATE, " IT'S LEFT FOR GUESSING, REALLY, AND SO THE TOOL TIP THERE IS GOING TO MAKE THAT CLEAR TO ANYBODY, THAT IF I FILTER BY THE DATE TABLE ITSELF, THEN IT IS FILTERING BY ORDER DATE. NOW WHAT I CAN DO IS CREATE A MEASURE, AS IS D CASE HERE, CALLED SALE SHIPPED. I WANT YOU TO NOTICE THE USE OF THE USE RELATIONSHIP FUNCTION, WHERE IN THE CALCULATE WHERE I'M MODIFYING FILTER CONTEXT FOR THE EVALUATION OF THIS MEASURE, I'M FORCING IT TO USE A DIFFERENT AND IN THIS CASE THE INACTIVE RELATIONSHIP. SO I'LL GO AHEAD, ADD A NEW MEASURE TO THE SALE TABLE, BRING THIS IN, AND NOW IT IS POSSIBLE SIDE BY SIDE TO THEN SEE WHAT WAS ORDERED AND WHAT WAS SHIPPED. BUT THAT APPROACH COULD BE TEDIOUS, ESPECIAL IF YOU HAVE LOTS OF DATES, ORDER DATE, DUE DATE, SHIP DATE, INVOICE DATE AND SO ON, THAT YOU'RE GOING TO HAVE TO CREATE ALL OF THESE MEASURE VARIANTS BASED ON ALL OF THESE POSSIBILITIES. SO WHEN IT COMES TO WHAT WE CALL IN DATA WAREHOUSING A ROLE PLAYING DIMENSION, THAT DATE SOMETIMES MEANS ORDER DATE, SOMETIMES SHIP DATE AND SO ON, THE APPROACH YOU CAN TAKE IS YOU REALLY SHOULD CREATE AN EXPLICIT TABLE FOR IT. NOW IT WOULD BE GREAT IF THERE WAS A COPY AND PASTE FUNCTION FOR A TABLE, AND THERE ISN'T. THE MODELLING -- YOU'LL CREATE A CALCULATED TABLE IN THE CAME SAY THIS DATE TABLE WAS CREATED, BUT WHEN I CREATE THE SHIP DATE TABLE, THE WAY THAT YOU CREATE A CLONE, IS JUST MAKE IT = THE OTHER TABLE. ANY VALID TABLE EXPRESSION WILL WORK, AND THE NAME OF THE TABLE IS THE TABLE. SO NOW I HAVE A CORRECT CLONE, AND I'D RECOMMEND WHEN YOU DO THIS APPROACH YOU SHOULD RENAME THE COLUMNS AS WELL SO THEY ARE DESCRIPTIVE. WE HAVE SHIP DATE. WE HAVE SHIP YEAR AND WE HAVE SHIP MONTH. AND THEN YOU PROBABLY GUESSED WHERE WE'RE HEADING WITH THIS. LET'S NOT FORGOT MARK THIS AS A DATE TABLE. IS THAT WE'RE NOW GOING TO HAVE TWO ACTIVE RELATIONSHIPS BASED ON TWO DIFFERENT TABLES. AND THIS WILL SUPPORT SIMULTANEOUS FILTERING BY ORDER DATE AND BY SHIP DATE. ENABLING ME TO PRODUCE THIS TYPE OF VISUALIZATION. ALL RIGHT, LET'S DO A STACKED COLUMN. WHAT I'D LIKE TO SHOW IS THE ORDER MONTH AND THE SHIP MONTH AND WHAT WE SOLD. THERE WE GO. NOT SO EXCITING AND FIRST OF ALL IT'S SORTING NOT IN A WAY I'D LIKE. GO AND SORT BY MONTH IN ASCENDING ORDER. BUT IF YOU HAVE A LOOK AT THE LENLD, YOU CAN SEE WHAT WAS ORDERED IN JANUARY-FEBRUARY WAS ACTUALLY SHIPPED IN FEBRUARY AND WHAT WAS ORDERED IN MARCH ACTUALLY SHIPPED IN MARCH AND SO ON. AND WE CAN PERHAPS MAKE THIS A LITTLE MORE INTERESTING BY BRINGING IN SOME MORE DATA. LET'S HAVE A LOOK. LET'S GO AHEAD AND CREATE ANOTHER ORDER, BUT IT'S SHIPPED IN MAY. AND ANOTHER ORDER, AND IT'S SHIPPED IN JUNE. GO AHEAD AND SAVE AND REFRESH THE DATA. LET'S SEE WHAT IT LOOKS LIKE. THAT WOULD ONLY BE POSSIBLE BECAUSE WE HAVE TWO SEPARATE TABLES WITH ACTIVE RELATIONSHIPS. SO I'VE FOUND IN MY MODEL DESIGN THAT WHILE I'VE GOT THE POSSIBILITY TO DEFINE INACTIVE RELATIONSHIPS, I'M MORE OFTEN THAN NOT DO THIS. I HAVE MODELS THAT HAVE OVER 15 DATE TABLES. DUE TO ALL THE VARIATIONS ON DATE YOU HAVE. OKAY? SO IT WILL AFFORD YOU THE MOST FLEXIBILITY. ALL RIGHT. SO THAT DEALS WITH THE ACTIVE VERSUS INACTIVE DISCUSSION. THE NEXT DISCUSSION IS TO EXPLORE THAT WE'VE GOT SOME ADDITIONAL DATA THAT I NEED TO ENHANCE THIS MODEL WITH. SO LET ME COME BACK TO POWER QUERY, BECAUSE I'VE GOT SOME SALES TARGET DATA. I WANT YOU TO NOTICE WHAT THIS DATA LOOKS LIKE. SO WE HAVE THE THREE COLUMNS, THE TARGETS, AND THIS IS VERY
COMMON, ARE AT A HIGHER LEVEL TO THE FACT DETAIL OF THE ACTUAL SALES THEMSELVES. WE HAVE A MONTH. WE HAVE A SUBCATEGORY TARGET. WE DON'T BOTHER TO GO TO INDIVIDUAL PRODUCT. IT'S JUST TOO TEDIOUS. SO NOW WE'VE GOT AN INTERESTING SITUATION BECAUSE WE WON'T HAVE A CLEAR ONE-TO-MANY PATTERN ANYMORE. LET ME DESCRIBE. FIRST OF ALL I WANT TO POINT OUT THAT MONTH, IF YOU LOOK AT THE COURSE DATA, MONTH WAS ACTUALLY TEXT, THAT IT CONVERTED IT AUTOMATICALLY, IT DETECTED A PATTERN THERE. I'LL KEEP IT AS DATE. AND REALLY MY FOCUS IS ON SUBCATEGORY. SUBCATEGORY WILL HAVE DUPLICATE VALUES IN THIS COLUMN. SO LET'S GO AHEAD AND ADD IT TO THE MODEL, SEE WHAT HAPPENS. WE'VE GOT SALE TARGET COMING IN, SO NOW WE HAVE TWO STARS, AND BY THE WAY, WHILE I WON'T DEMONSTRATE IT, A NEWER CAPABILITY IS THAT YOU CAN CREATE MULTIPLE DIAGRAMS NOW, AND THE RECOMMENDATION IS THAT YOU CREATE A DIAGRAM PER STAR. SO IT'S SORT OF EASY TO SEE EACH FACT TABLE AND ITS RELATED DIMENSION TABLES. OKAY. SO TO ESTABLISH RELATIONSHIPS, LET ME TAKE THE DATE COLUMN AND INTERESTINGLY LET ME RELATE THAT TO MONTH. REMEMBER THE POWER QUERY CONVERTED IT TO A DATE. AND IT'S DETECTED A ONE-TO-MANY THERE. WE JUST WANT TO BE CAREFUL, HOWEVER, THAT REALLY THAT DATE REPRESENTS A FULL MONTH PERIOD, NOT REALLY A SINGLE DAY. THE TARGET IS FOR THE MONTH. AND THEREFORE THE DATE REPRESENTS THE FIRST DATE OF THE MONTH PERIOD. BECAUSE MY DATE TABLE DOESN'T GO DOWN TO DATE LEVEL, IT'S YEAR, MONTH, WELL, ACTUALLY IT DOES GO DOWN TO DATE LEVEL, WE JUST WANT TO BE CAREFUL HOW YOU PRESENT THAT DATA. DON'T PRESENT IT THAT YOU MUST ACHIEVE QUOTA ON THE FIRST OF MARCH BUT FOR THE NEXT 30 DAYS YOU CAN RELAX AND DO NOTHING. IT DOESN'T MEAN THAT. JUST BE CAREFUL THE WAY YOU PRESENT THAT. BUT HERE'S WHERE THE INTERESTING PART COMES, BECAUSE SUBCATEGORY, AS WE SHOULD APPRECIATE, IS NOT A UNIQUE COLUMN. THERE ARE MULTIPLE PRODUCTS THAT ARE ASSIGNED TO THE SAME SUBCATEGORY, SO WE'LL HAVE DUPLICATES HERE, AND OF COURSE WE HAVE MULTIPLE TARGETS FOR THE SAME SUBCATEGORY, SO WHAT'S GOING TO HAPPEN WHEN I RELATE THIS TO THIS? AND YOU'LL SEE THAT WE'VE GOT THIS WINDOW. IT DIDN'T AUTOMATICALLY CREATE THE RELATIONSHIP, BUT IT'S REALLY DRAWING YOUR ATTENTION TO THIS YELLOW BOX THAT SAYS THERE'S SOMETHING YOU REALLY SHOULD KNOW, THAT YES YOU CAN SEE THE CARDINALITY HAS BEEN DETECTED AS MANY-TO-MANY, BUT IT MIGHT NOT PRODUCE THE RESULT YOU EXPECT. I'VE GOT TO SAY I'M NOT TERRIBLY HAPPY WITH THE WORDING HERE. IT REALLY DOESN'T MAKE A GREAT DEAL OF SENSE TO ME, AND I SORT OF KNOW WHAT I'M DOING. AND SO I MIGHT CLICK ON LEARN MORE, AND I'M SOMEWHAT DISAPPOINTED THAT IT DOES TAKE ME TO SOME DOCUMENTATION BUT RATHER GENERIC DOCUMENTATION ON MANY-TO-MANY. IT DOESN'T REALLY ADDRESS THE ISSUE THAT MIGHT BE CONFRONTING YOU. SO NOW IS THE OPPORTUNITY IN THIS SESSION TO REALLY DESCRIBE WHAT IT'S TELLING YOU. WE'RE GOING TO GO AHEAD AND SAY THAT'S OKAY. I AM GOING TO SWITCH THE CROSS-FILTER DIRECTION. SO YOU'LL NOTICE HERE THAT THE CROSS-FILTER DIRECTIONS EARLIER WERE SINGLE OR BOTH IN A ONE-TO-MANY ARRANGEMENT IT WILL ALWAYS BE FROM THE ONE TO THE MANY SIDE AND OPTIONALLY YOU CAN HAVE IT GO BACKWARDS, BUT IN A MANY-TO-MANY ARRANGEMENT, YOU HAVE THREE OPTIONS. BUT IT IS GOING TO BE BOTH OR IT'S FROM ONE OF THE MANY SIDES ONLY. AND WHEN YOU THINK ABOUT IT, IT REALLY SHOULD PROPAGATE FROM THE DIMENSION-TYPE TABLE. IN THIS CASE IT'S THE PRODUCT TABLE. WE SHOULDN'T PUSH FILTERS BACK FROM A FACT TABLE TO A DIMENSION. SO I'M GOING TO CONFIGURE THAT, THAT IT'S A SINGLE DIRECTION FROM PRODUCT THAT WILL FILTER SALES TARGET. AND I'M NOT SURE WHY IT TURNED THIS OFF, BUT YES I WANT AN ACTIVE RELATIONSHIP BETWEEN PRODUCT AND SALES TARGET. AND THERE YOU SEE IT. WE HAVE NOW, AND THIS IS RELATIVELY NEW, THE ABILITY TO DEFINE A MANY-TO-MANY. BUT LET'S UNDERSTAND THE POTENTIAL PROBLEMS WITH THIS. SO I'LL GO AHEAD AND CREATE A NEW REPORT PAGE, AND THEN I'M GOING TO BRING IN THAT I WOULD LIKE TO SEE MONTH AND SUBCATEGORY, AND THERE WE GO, SEE ALL SUBCATEGORIES FOR ALL MONTHS. AND THEN WE'LL TAKE A LOOK AT THE ORDER QUANTITY OR THE SALE QUANTITY COMPARED TO THE TARGET QUANTITY. AND DOES IT MAKE SENSE TO US? OKAY, THE SALE ACTIVITY IS VERY LOW. SOMEONE SET VERY HIGH TARGETS, BUT AT THIS STAGE IT SHOULD MAKE SENSE. WE CAN SEE THAT IN JANUARY 2019 YOU'RE SUPPOSED TO SELL 510 AND SO FAR YOU'VE SOLD ONE. NOT A PROBLEM. BUT HEREIN LIES THE PROBLEM WITH THIS MANY-TO-MANY ARRANGEMENT. IS THAT WHAT WOULD HAPPEN IF YOU DO A GROUPING ON THE PRODUCT TABLE THAT IS BENEATH THE GRANULARITY OF THAT MANY-TO-MANY RELATIONSHIP? IN THIS CASE, INSTEAD OF HAVING SUBCATEGORY, WE BRING IN PRODUCT. CAN YOU SEE ANY PROBLEM WITH THIS? >> [INDISCERNIBLE]. >> IS THE TARGET WRONG? WELL, HERE'S THE ISSUE. IF YOU SEE ON THE RIGHT-HAND SIDE, THERE ARE ACTUALLY FOUR ROWS REPRESENTING JANUARY 2019, AND IF YOU WERE TO SUM THEM TOGETHER, IT WILL COME TO DOUBLE WHAT THE LEFT-HAND SIDE IS SAYING. IT'S TECHNICALLY NOT WRONG, BUT IT'S OPEN TO EASY MISINTERPRETATION. THIS IS THE PROBLEM. THERE'S A MANY-TO-MANY RELATIONSHIP GOING ON BETWEEN PRODUCTS AND SUBCATEGORIES. SO WHAT IS TECHNICALLY HAPPENING IS THAT BECAUSE WE'RE NOW GROUPING BY A PRODUCT, WHAT IT DOES IS IT DETERMINES WHAT ARE THE SUBCATEGORIES FOR THAT PRODUCT AND THEN PROP GAITS THE SUBCATEGORIES. SO IF YOU'RE GOING TO USE A BENEATH THE GRAIN, WUR GOING TO BE OPEN TO THIS TYPE OF PROBLEM. DOES THIS MEAN WE SHOULDN'T DO THIS? THE SOLUTION IN THE PAST, BY THE WAY, WOULD HAVE BEEN WE WOULD HAVE SACRIFICED OUR PRODUCTS HIERARCHY. WE WOULD HAVE KEPT THE SUBCATEGORY TABLE SEPARATE BECAUSE THAT GAVE US A UNIQUE COLUMN. WE HAD A UNIQUE SUBCATEGORY THAT WE COULD HAVE JOINED DOWN TO SALE TARGET, BUT WE COULDN'T HAVE HAD A HIERARCHY, BUT THAT WAS A SMALL PRICE TO PAY TO GET THE PROPAGATION TO WORK PROPERLY. NOW THAT WE HAVE THE MANY-TO-MANY, IT ENABLES US TO JOIN OR RATHER TO RELATE A DIMENSION TO A FACT AT A HIGHER LEVEL THAN THE DIMENSION'S GRAIN. OKAY? BUT WE MUST TAKE CARE, AND THAT'S WHAT THAT YELLOW BOX WAS ATTEMPTING TO SAY. SO WHAT WE COULD DO IS CONTROL THE WAY THAT TARGET QUANTITY WAS ACTUALLY REPORTED. INSTEAD OF JUST LETTING THEM SUMMARIZE A COLUMN, YOU KNOW, I'LL COME INTO MY SNIPPETS HERE, AND I'VE GOT A MEASURE HERE THAT JUST DOES SOMETHING FANCY. THAT PROVIDING THE PRODUCT ISN'T IN SCOPE, YOU CAN GO AHEAD AND SUM IT. OTHERWISE RETURN BLANK. IT WOULD BE SAFER NOT TO SHOW YOU A TARGET IF YOU'RE AT A PRODUCT LEVEL. SO IT'S WHERE YOUR FANCY DAX WORK WILL NEED TO COME IN. SO I'LL ADD TO CELL TARGET A NEW MEASURE. PROVIDING PRODUCT IS NOT IN SCOPE, SO WE'LL SEE THAT WE DO NOT SEE A CELL TARGET IN THIS SITUATION, BUT IN THE ORIGINAL TABLE, IF I REMOVE THE COLUMN AND PLACE IN TARGET, YOU WILL SEE IT. OKAY? SO IT MIGHT REQUIRE SOME DAX WORK TO GET THAT TO BEHAVE IN A WAY THAT DOESN'T OPEN UP MISINTERPRETATION OF THE DATA. SO YES, MANY-TO-MANY IS A COOL THING AS A CARDINALITY. RESERVE IT ONLY FOR DIMENSIONS RELATING AT A HIGHER GRAIN. AWESOME. WELL, LET'S HAVE PLAY AROUND WITH THIS. SO WHAT WE HAVEN'T YET COVERED IS THE CONCEPT OF BIDIRECTIONAL FILTERING, AND I'LL REMIND YOU THAT WE SAW THAT YOU COULD ONLY CREATE AN ACTIVE PATH BETWEEN TWO TABLES, BUT LET'S SEE WHAT WOULD HAPPEN IF I DID THIS. LET'S ALLOW FILTER PROPAGATION TO WORK IN BOTH DIRECTIONS HERE AND HERE. AND YOU'LL SEE THAT I'M PROHIBITED FROM DOING THIS. I'VE INTRODUCED AMBIGUITY INTO THE RELATIONSHIP SCHEME, AND THEREFORE THERE IS NO DETERMINICSIC WAY THAT WE COULD PROPAGATE THE FILTERS. THERE'S A CIRCLEAR REFERENCE GOING ON. YOU WON'T BE ABLE TO DO THAT, WHICH IS A GOOD THING. WE FOUND WHEN BIDIRECTIONAL FILTERING BECAME AVAILABLE, IT WAS BEING MISUSED. IT WAS BEING USED BECAUSE IF YOU'RE FAMILIAR WITH PIVOT TABLES IN EXCEL THAT HAVE SLICERS, AND DEPENDING ON YOUR FILTER CONTEXT, THE SLICER WOULD ELIMINATE OPTIONS THAT MEASURES DIDN'T RETURN VALUES FOR, WHICH WAS ENORMOUSLY USEFUL. IF FILTER BY 2019, SHOW ME THE PRODUCTS SOLD IN 2019. YOU SHOULD KYIV -- COULD ACHIEVE THAT, BUT AT GREAT COST. AS DATA VOLUMES GROW, YOU MIGHT SEE PERFORMANCE DEGRADATION, AND THE OTHER THING IS IT CAN OPEN UP PROBLEMS WITH CALCULATIONS. BIDIRECTIONAL FILTERING SHOULD BE USED IN MY OPINION IN VERY SPECIFIC CIRCUMSTANCES, WHICH I'M ABOUT TO DESCRIBE. SO UNDERSTAND THAT THE CAPABILITY IS THERE AND THAT IT SHOULD BE USED IN VERY SPECIFIC CIRCUMSTANCES, WHICH IS THE MANY-TO-MANY ARRANGEMENT THAT DIFFERS FROM WHAT I JUST DID. THAT MANY-TO-MANY, AS I'LL REMIND YOU, IS FROM A HIGHER GRAIN. WHERE YOU'VE GOT A MANY-TO-MANY BETWEEN DIFFERENT DIMENSIONS, RECALL MANAGERS AND SUBCATEGORIES HAVE A RELATIONSHIP. THEY ARE DIFFERENT DIMENSIONS. THE APPROPRIATE DESIGN APPROACH IS TO INTRODUCE IN DATA WAREHOUSE TERMINOLOGY A FACTLESS FACT TABLE. WHO IS THE MANAGER, WHAT IS THE SUBCATEGORY? AND THEN ACHIEVE ONE-TO-MANY RELATIONSHIPS ACROSS IT. SO LET ME SHOW YOU HOW THIS COULD WORK. BACK TO POWER QUERY, RECALL THAT I HAVE MY RAW MANAGER DATA HERE. I NEED TO ADD A TABLE TO THE MODEL THAT WILL GIVE ME THE COMBINATION OF MANAGERS AND SUBCATEGORIES. SO I'LL CREATE A REFERENCE QUERY HERE, RENAME IT AS MANAGER SUBCATEGORY. AND THEN -- WELL, I NEED MY MANAGER KEY, SO I'M GOING TO HAVE TO MERGE BACK TO MANAGER. RELATE FIRST NAME AND LAST NAME TO FIRST NAME AND LAST NAME AND BRING IN THAT SURROGATE KEY. THEN I CAN JUST KEEP THESE TWO COLUMNS, REMOVE THE OTHERS, AND THERE IS MY FACTLESS FACT TABLE. IT'S NOT A DIMENSION, BUT IT'S DESCRIBING RELATIONSHIPS BETWEEN TWO DIMENSIONS. ALL RIGHT. LET'S LOAD THIS INTO THE MODEL. OKAY SO MANAGER IS DOWN HERE AND SUBCATEGORY IS HERE. WE HAVE A MANY AND WE HAVE A MANY. WHAT I NEED TO ACHIEVE THIS IN THE MOST EFFICIENT WAY IS ONE-TO-ONE, ONE TO MANY, ONE SO MANY. I'M STILL MISSING A TABLE WHICH GIVES ME A SIDE -- I COULD DO IN IN DAX. I'M GOING TO CREATE A TABLE CALLED DISTINCTION SUBCATEGORY WHICH JUST EQUALS GUESS WHAT WE HAVE A DISTINCT FUNCTION OF THE PRODUCTS OF CATEGORY COLUMN. THERE WE GO. THERE ARE TWO SUBCATEGORIES. THERE'S OUR ONE SIDE, SO I'LL JUST POSITION THIS TABLE SO ABOUT THERE. AND IT WILL JUST BUILD UP THESE RELATIONSHIPS. MANY TO ONE. MANY TO ONE. MANY, MANY TO ONE. THIS DIAGRAM IS A LITTLE FLAKEY. MANY TO ONE. MANY TO ONE. WHAT DID I SAY ABOUT CIRCULAR REFERENCES? DO WE HAVE A CIRCULAR REFERENCE AT THIS STAGE? HOW WOULD WE ANSWER THAT QUESTION? WELL, LET'S THINK ABOUT IT THIS WAY. I WILL BUILD UP A TABLE HERE THAT SAYS JUST SHOW ME MANAGER AND SALES. NOW REMEMBER THERE ARE TWO ASSOCIATIONS. MANAGERS ARE ASSIGNED TO A SALE BECAUSE THEY PHYSICALLY DID THE SALE, AND MANAGERS ARE MEASURED BY THEIR PERFORMANCE BASED ON THE SUBCATEGORIES THEY'RE ASSOCIATED WITH. SO WHAT AM I SEEING HERE? ANGUS AND BEE. THE WAY TO ANSWER THAT QUESTION IS THIS: COMING TO THE DIAGRAM HERE, THERE IS A FILTER ON THIS TABLE, AND WE SEE THAT IT DOES PROPAGATE TO SALE, BUT IT ALSO PROPAGATES TO MANAGER SUBCATEGORY, BUT THIS IS WHERE IT FALLS DOWN. THERE IS NO FILTER PROPAGATION GOING ANY FURTHER, SO IT IS UNDERSTOOD THAT THAT IS THE MANAGER AND THE SALE THAT THEY'VE MADE. WOULD I BE ABLE TO MAKE THIS BOTH DIRECTIONS? AND ALLOW THAT FILTER TO PROPAGATE OVER THE FACTLESS FACT TABLE? AND IT MIGHT SURPRISE YOU, BUT YES I CAN. AND AT A GLANCE, THIS IS NON-DETERMINISTIC NOW. WHICH WAY IS MANAGER GOING? IT CAN FILTER SALE DIRECTLY, OR IT CAN FILTER THIS TABLE TO THIS TABLE TO THIS TABLE TO THIS TABLE, AND I'M SURPRISED THAT WE'RE ALLOWED TO DO IT, BUT WE ARE. I DON'T KNOW WHY. BUT IF WE COME BACK TO THE REPORT, I'M NOT SURE HOW GOOD YOUR MEMORY IS, THE FACT IS THAT THE RESULT HASN'T CHANGED. SO THE RULE THAT I WOULD COME UP WITH IS THE SHORTEST TOP, BUT IS THAT ONE THAT YOU WANT TO RELY ON? IT MIGHT BE A FUTURE UPDATE AND SOMEBODY AT MICROSOFT CHANGES THE LOGIC AND ALL OF A SUDDEN IT USES THE OTHER PATH TO RESOLVE AND THEREFORE YOU'RE SEEING THE SALES ASSIGNED TO THE SUBCATEGORIES THAT THE MANAGER IS ASSIGNED TO. SO I DON'T THINK YOU SHOULD EVER RELY ON THE FACT THAT THIS MAY BREAK IN THE FUTURE. SO TO BE ABSOLUTELY SURE ON THIS, YOU KNOW, I WOULD SUGGEST THAT YOU CREATE EXPLICIT MEASURES. SO LET'S TAKE A LOOK AT HOW THIS WOULD WORK. ON THE SALE TABLE I'LL CREATE A MEASURE, AND HERE'S THE USE OF THE USER RELATIONSHIP FUNCTION. DEFINITELY USE THE RELATIONSHIP BETWEEN THE MANAGER AND SALE TABLE. THAT'S EXPLICITLY DECLARED. SO WITH CONFIDENCE, I CAN COME TO MY TABLE NOW AND SAY I DON'T WANT THAT SALE AMOUNT COLUMN ANYMORE. I'M GOING TO USE THE MEASURE, AND WE KNOW THE PATH THAT IT'S GOING TO TAKE. AND THE OTHER APPROACH IS FOR THE MANAGER'S CELL PERFORMANCE, THIS IS A DIFFERENT TYPE OF ANALYSIS FOR THE MANAGER. THIS TIME THE MEASURE IS USING THE CROSS-FILTER FUNCTION. SO YOU CAN ACTUALLY DISABLE -- THERE MIGHT BE AN ACTIVE RELATIONSHIP, BUT THE CROSS-FILTER FUNCTION WILL ALLOW YOU TO SAY CHANGE ITS FILTER TO SINGLE TO BOTH OR TO NONE. DO NOT EVEN USE THAT RELATIONSHIP. SO LET'S SEE WHAT HAPPENS WHEN I USE THIS FUNCTION, AND NOW WHAT WE'RE SEEING IS A VERY DIFFERENT RESULT. THE SECOND ONE IS RESOLVING VIA THE ASSOCIATIONS OF MANAGERS TO SUBCATEGORIES TO THEIR PRODUCTS DOWN TO THE SALES THEMSELVES. BUT WHEN YOU REALLY STOP AND THINK ABOUT IT, IT'S SORT OF MAYBE FUN IN THEORY, BUT I WOULD NOT DESIGN THE MODEL THIS WAY. WHEN YOU SEE THE EXAMPLE OF DATE AS A ROLE PLAYING DIMENSION THAT SOMETIMES IT MEANS ORDER DATE, SOMETIMES IT MEANS SHIP DATE, MANAGER IS NO DIFFERENT IN THIS SITUATION. MANAGER SOMETIMES MEANS THE MANAGER THAT IS RESPONSIBLE FOR THE SALE VERSUS THE MANAGER WHOSE PERFORMANCE IS BEING MEASURED BY SALES OF SUBCATEGORIES. IT'S REALLY A ROLE PLAYING ARRANGEMENT. SO WHILE YOU'LL GET AWAY WITH THIS AMBIGUITY, YOU KNOW, REALLY IN THE REAL WORLD IS YOU SHOULDN'T HAVE THIS HAPPENING. YOU MIGHT GET AWAY WITH IT, BUT ESPECIALLY WHEN YOU ARE USING BIDIRECTIONAL FILTER, YOU SHOULD ENSURE THAT YOUR DIRECTIONS TERMINATE. THEY DO NOT COME BACK AND JOIN ANOTHER DIMENSION WOULD BE PROBABLY A GOOD SOUND PRACTICE, IN WHICH CASE WHAT WE COULD DO, BECAUSE I THINK THAT MIGHT HAVE BROKEN THINGS, DO YOU SEE THAT NO FILTER PROPAGATION IS NOW HAPPENING? WE'RE SEEING THAT ALL MANAGERS SEE THE SAME PERFORMANCE. WE HAVE ANOTHER FUNCTION CALLED TREAT AS HERE. SO I COULD UPDATE THE MEASURE TO SAY WHATEVER FILTERS ARE BEING APPLIED TO THE MANAGER TABLE, PUSH THEM TO THE MANAGER SUBCATEGORY TABLE AT THE SAME TIME. SO I'LL TAKE THAT MANAGER CELLS PERFORMANCE AND I'LL UPDATE IT TO USE THE TREAT AS FUNCTION, WHATEVER THE MANAGER KEY FILTERS ARE ON MANAGER APPLY THEM TO THE MANAGER SUBCATEGORY MANAGER KEY. AND THEN WE'RE BACK. WOW. OKAY. HOW ARE WE DOING? YEP? OKAY. WELL, THAT'S PRETTY MUCH EVERYTHING, ISN'T IT? HAVE I MISSED ANYTHING? WE'VE GOT ALL THE CARDINALITIES IN THERE, WE GOT RID OF THE ONE-TO-ONE. WE TALKED ABOUT MANY-TO-MANY, BUT WHEN IT'S A MANY-TO-MANY BETWEEN TWO DIFFERENT DIMENSIONS, WE COULD GO TO THE ONE-TO-MANIES ACROSS THE FACTLESS FACT TABLE WHICH WILL PROVIDE YOU FAR BETTER PERFORMANCE. OKAY. WELL LET'S SWITCH BACK TO THE SLIDE DECK. AND THAT WAS ALL ABOUT GOOD MODELING. ALL RIGHT, SO LET'S WRAP UP WITH WHAT MATTERS. WHAT IS GOOD, WHAT IS NOT. SO LET'S TAKE A LOOK AT THESE TOPICS. MODELING. STRIVE TO FOLLOW APPLICABLE STAR SCHEMA DESIGN PRACTICES. IF ANYONE IS FAMILIAR WITH RALPH KIMABLE AND HIS PUBLICATIONS, THERE'S A REMARKABLE AMOUNT OF GOOD KNOWLEDGE IN THERE THAT APPLIES TO GOOD TABULAR DESIGN. I DON'T LIKE THE IDEA THAT A TABLE IS BOTH A DIMENSION AND FACT TABLE AT THE SAME TIME. I LIKE THE IDEA THAT DIMENSIONS ARE THERE FOR FILTERING AND GROUPING AND MY FACTS ON THE MANY SIDE ARE THERE FOR SUMMARIZATION. WE SAW THE CONCEPT OF SURROGATE KEYS, THAT WHERE YOU DO HAVE MULTIPLE COLUMNS PROVIDING UNIQUENESS, YOU WILL HAVE TO ADD A SURROGATE KEY. SNOWFLAKE DIMENSIONS LIKE PRODUCT AND SUBCATEGORY, IF YOU NEED THOSE HIERARCHIES, PROBABLY BEST TO CONSOLIDATE THEM. ROLE PLAYING DIMENSIONS. YOU COULD GO FOR THE INACTIVE PATH, BUT GENERALLY THE FLEXIBILITY COMES BY HAVING MULTIPLE TABLES WITH ACTIVE RELATIONSHIPS. JUNK DIMENSIONS, I WON'T GO THROUGH THEM NOW, BUT YOU MIGHT FIND THAT TOPIC USEFUL IN OUR DESIGN. OF COURSE MEASURES, HAVING THOSE COLUMNS AND FACT TABLES FOR SUMMARIZATION, AND WE DID SEE A FACTLESS FACT TABLE ASSOCIATING MANAGERS TO THEIR SUBCATEGORIES. TABLE CONSIDERATIONS. FEWER TABLES CAN IMPROVE THE PERFORMANCE AND FRIENDLINESS OF YOUR MODEL. AVOID ONE-TO-ONE CARDINALITY AND CONSIDER CONSOLIDATING WHERE POSSIBLE. WHEN IT COMES TO ACTIVE VERSUS INACTIVE, YES, YOU CAN DO THIS, BUT IF YOU'RE GOING TO HAVE INACTIVE RELATIONSHIPS, THEN YOU'RE GOING TO HAVE TO EXPLICITLY INVOKE THOSE RELATIONSHIPS THROUGH FORMULA. DO TAKE CARE, AS I DID IN THE DATE DIMENSION, TO USE TOOL TIPS TO COMMUNICATE WHAT FILTER PROPAGATION WILL HAPPEN. IF YOU HAVE A GENERIC DATE TABLE THAT SLICES ACROSS MULTIPLE FACT TABLES, YOU REALLY SHOULD DECLARE SOMEWHERE, WHETHER IT'S DOCUMENTATION OR A TOOL TIP, WHAT FILTER PROPAGATION TO ORDER DATE, ET CETERA, IS ABOUT TO HAPPEN. MANY-TO-MANY RELATIONSHIPS. WELL, THE FACT IS THERE'S REALLY TOO VARIANTS OF THIS. AND I SORT OF REFER TO THEM AS A TYPE ONE TYPE TWO, BUT THAT'S A PETER MYERS DEFINITION. THERE'S NO MUCH DOCUMENTATION. WE'RE SEEING THAT THERE'S A CARDINALITY PROPERTY OF THE RELATIONSHIP THAT SUPPORTS A MANY-TO-MANY, AND I'M GOING TO SUGGEST THAT THAT IS USEFUL FOR DIMENSION-TO-FACT WHEN THE DIMENSION CANNOT OFFER A UNIQUE COLUMN TO REFERENCE ACROSS TO THE FACT. IE, OUR CELLS TARGETS ARE AT SUBCATEGORY LEVEL, BUT SUBCATEGORY IS NOT UNIQUE IN EITHER TABLE. WHERE IT'S TWO DIMENSIONS, LIKE MANAGERS AND SUBCATEGORIES, WILL GO FOR THE FACTLESS FACT TABLE AND GET THE ONE-TO-MANY, MANY-TO-MANIES HAPPENING AND THAT'S A CASE TO ALLOW THE PROPAGATION OF FILTERS TO PROPAGATE OVER THE FACTLESS FACT TABLE. SO THE DETAILS ABOUT RELATING FACTS AT HIGHER GRAIN IS THAT, YES, YOU USE WHAT'S KNOWN AS A WEAK RELATIONSHIP, THE MANY-TO-MANY CARDINALITY. BUT DO TAKE SUPER CARE ENSURING THAT YOUR USERS DON'T GO AND USE LOWER GRAIN COLUMNS TO FILTER ACROSS THAT MANY-TO-MANY RELATIONSHIP. AND MEASURES AND EXPLICIT FORMULAS MIGHT HELP YOU SAVING BAD THINGS HAPPENING. AS WE SAW WITH THE RELATIONSHIP BETWEEN TWO DIMENSIONS, CREATE THAT FACTLESS FACT TABLE AND HAVE MULTIPLE ONE-TO-MANIES WITH A BIDIRECTION LL FILTER SOMEWHERE IN THERE. YOU WOULD HIDE THOSE TABLES. THEY ARE THERE TO SUPPORT THE RELATIONSHIP CONFIGURATION. YOU DO NOT WANT REPORT AUTHORS OR Q&A TO HAVE ACCESS TO THEM. BIDIRECTIONAL FILTERING. THIS WAS JUST COVERED. WHILE YOU CAN DO IT, PLEASE DON'T DO IT TO ACHIEVE THAT I WANT MY SLICERS TO REFLECT WHERE VALUES EXIST FOR MEASURES. ALL RIGHT? YES, WE LOVE IT IN EXCEL. I DO BELIEVE THAT IT'S A MUCH-ASKED FOR CAPABILITY THAT THE SLICER SHOULD BE ABLE TO FILTER BY A MEASURE. ONLY -- PRODUCTS WHERE THIS MEASURE IS NOT BLANK. LET'S SEE WHAT HAPPENS. DO BE CAREFUL WITH BIDIRECTIONAL FILTERING. IF YOU'RE USING ROW LEVEL SECURITY, YOU HAVE TO GO TO THE EXTRA STEP OF SAYING ALSO USE THE BIDIRECTIONAL FILTERING FOR ROW LEVEL SECURITY ENFORCEMENT. THAT IS OFF BY DEFAULT. YOU WOULD NEED TO TURN THAT ON, BUT OF COURSE YOU WOULD TEST YOUR ROW LEVEL SECURITY RIGOROUSLY. WHEN IT DOESN'T ACHIEVE WHAT YOU NEED IT TO, IT'S POSSIBLE THAT YOU DIDN'T TURN ON THAT OPTION. ONE THING THAT WE DIDN'T LOOK AT IS DATA INTEGRITY, AND I DID MAKE MENTION THAT THE RELATIONSHIPS IN POWER BI AREN'T REALLY CONCERNED WITH REFERENTIAL INTEGRITY, BUT LET'S TAKE A LOOK AT WHAT HAPPENS WHEN WE'VE GOT MISSING VALUES. SO BACK HERE, WHAT WOULD HAPPEN IS -- AND THIS IS AN EXPECTED CASE WHEN YOU THINK ABOUT IT. THAT WE SOLD SOMETHING IN MAY, BUT IT HASN'T YET SHIPPED. WE HAVE A BLANK SHIP DATE. WHAT DOES THAT MEAN IN THE REPORT? IS THAT AN INTEGRITY ISSUE? WELL, NOT WHEN YOU THINK ABOUT IT. IT'S QUITE REASONABLE. SO WHEN YOU SEE THAT THERE'S A MISSING OR A BLANK ON THE MANY SIDE, SO LONG AS IT MAKES SENSE, LIKE THE ORDER DATE PROBABLY SHOULDN'T BE BLANK WHEN YOU THINK ABOUT IT, BUT SHIP DATE WILL BE BLANK UNTIL SUCH POINT IN TIME THAT SHIPMENT HAS HAPPENED. ON THE MANY SIDE THAT'S OKAY, BUT POWER BI PUSHES A BLANK ROW ON THE ONE SIDE WHICH IS A GOOD THING. OTHERWISE YOU WOULDN'T BE ABLE TO ASK THE QUESTION AND VISUALIZE WHAT IS THE VOLUME OF SALES THAT IS YET TO BE SHIPPED. OKAY? SO YOU WOULD FILTER ON BLANK, ON THE PRODUCT SIDE, TO GET THIS. BUT WHAT WOULD IT MEAN IF WE HAD THE OTHER WAY AROUND? AND WE COULD ACHIEVE THAT BY CREATING A PRODUCT THAT DOESN'T EXIST. IT'S SN-04, AND IF I SAVE THIS AND REFRESH, AND LET'S TAKE A LOOK AT IT FROM A PRODUCT PERSPECTIVE PERSPECTIVE, SHOW ME PRODUCTS AND SALES . WHAT IS IT DOING? SHOW ITEMS WITH NO DATA. DO I HAVE THAT PRODUCT? MY INTENTION IS THAT WE DON'T. THERE WE GO. WHEN YOU'VE GOT THAT ARRANGEMENT, THAT'S A DATA INTEGRITY ISSUE. YOU'VE SOLD A PRODUCT THAT YOU HAVEN'T DECLARED. YOU SHOULD NOT HAVE BLANKS ON THE ONE SIDE. GO BACK TO POWER QUERY, GO BACK TO THE DATA. FIX IT. YOU DO HAVE A PROPERTY THAT ALLOWS THE LOAD TO FAIL. I COULD COME IN HERE AND SAY STOCK-KEEPING UNIT IN THE SALE TABLE, IT IS NOT NULLABLE. BY TURNING THAT PROPERTY OFF, THE REFRESH WOULD FAIL. YOU'RE ENSURING THAT THERE ARE VALUES, AND ALL RIGHT, ALMOST THERE. THAT'S THE CONCEPT OF DATA INTEGRITY. AND I'VE JUST SAID TRY AND AVOID THAT. ON THE MANY SIDE IT MIGHT MAKE SENSE THAT WE DON'T HAVE A VALUE YET, BUT ON THE ONE SIDE IT REALLY SHOULDN'T HAPPEN. DIRECT QUERY MODEL, A QUICK LAST WORD. THE ONLY DIFFERENCE IN DIRECT QUERY IS IF IT HAS TO JOIN MULTIPLE TABLES, IF THERE'S REFERENTIAL INTEGRITY BEING ENFORCED IT WILL USE A MORE EFFICIENT INNER JOIN THAN AN OUTER JOIN. WHERE YOU KNOW IT'S DECLARED IN THE SOURCE, GO AHEAD AND CHECK THAT PROPERTY. FOR COMPOS IT MODELS, MANY MUST BE ENFORCED BETWEEN THE IMPORTED AND OTHER DATA THAT IS IN DIRECT QUERY. ALL RIGHT. WE'VE PRETTY MUCH ARRIVED AT THE END OF THE SESSION, SORT OF ON TIME. SO I HOPE THIS OPENS YOUR EYES TO THE POTENTIALS OF GOOD MODELING AND WHAT RELATIONSHIPS DO AND HOW THEY CAN BE CONFIGURED TO ACHIEVE WHAT YOUR DATA NEEDS TO EXPRESS. THANK YOU VERY MUCH FOR YOUR TIME, YOUR INTEREST AND ATTENDANCE. [ Applause ] THANK YOU. I'LL JUST SAY THAT I'VE GOT ONE WORKSHOP THIS AFTERNOON. I KNOW IT'S DURING THE POWER HOUR, WHICH IS ALSO DON'T MISS IT, BUT IF YOU GET LOCKED OUT OF THERE, 1:00 THERE'S A WORKSHOP, TWO HOURS. COMPUTERS ARE PROVIDED. IF YOU GET THERE AND THERE'S A SEAT, I THINK YOU'RE WELCOME TO IT. I UNDERSTAND THAT REGISTRATION IS FULL.
