Aggregation and composite model: Microsoft Power BI fast and furious - THR3016

Video Statistics and Information

Video
Captions Word Cloud
Reddit Comments
Captions
EVERYONE IN THE BOOK. IN THE FRONT? GREAT. THANK YOU FOR COMING TO THIS SESSION. IT'S A VERY QUICK RUN THROUGH OF SPECIFIC FEATURE IN POWER BI CALLED COMPOSITE MODEL. CAN'T HEAR PROPERLY ? CAN YOU MOVE THE -- A LITTLE BIT LOUDER I THINK? ONE, TWO, THREE. BITTER? BETTER? PERFECT. THANK UP. RIGHT. REALLY QUICK RUN THROUGH OF SPECIFIC FEATURE IN POWER BI CALLED COMPOSITE MODEL AND AGGREGATIONS. WE'LL GO THROUGH THAT REAL QUICKLY. LOT OF DETAILS THAT I WILL ESCAPE BECAUSE 20 MINUTES I CANNOT TALK ABOUT ALL OF THOSE BUT I HAVE SOME LINKS TO SOME ARTICLES I HAVE WRITTEN WHICH YOU CAN FOLLOW ALONG LATER ON AND SLIDES ARE ALREADY UPLOADED YOU CAN DOWNLOAD IT FROM -- APPLICATION AND WEBSITE. A LITTLE BIT ABOUT ME . I'M REZA RAD BASED IN NEW ZEALAND, OTHER SIDE OF THE WORLD I'M ALSO AUTHOR OF SOME POWER BI BOOKS, ONE OF THEM IS ACTUALLY AVAILABLE IN THE BOOK STORE ALREADY OVER THERE . AND THESE ARE MY CONTACT INFORMATION IN CASE YOU HAVE ANY QUESTIONS. I'M A CONSULTANT AND TRAINER ON POWER BI. IF YOU HAVE USED POWER BI ONE OF THE REALLY INTERESTING FEATURES OF POWER BI IS THAT POWER BI IS QUITE FAST, SUPER FAST. WHEN YOU LOAD DATA INTO POWER BI WHEN YOU WANT TO WORK WITH IT, AWESOME, IT'S FAST, JUST DRAG AND DROP EVERYTHING WORKS PERFECTLY FINE. NOW THE PROBLEM IS THAT SOMETIMES YOU DON'T HAVE JUST LIKE 10, 000 ROWS, YOU HAVE REALLY BIG DATASET. WHEN IT COMES TO BIG DATASET THEN IT'S NOT FAST, RIGHT? WHEN IT'S BIG DATASET THEN YOU GET PROBLEM THAT, OKAY, I CANNOT LOAD THAT MUCH DATA INTO POWER BI SO WHAT IS THE SOLUTION FOR THAT? RIGHT? THEN YOU HEAR ABOUT DIRECTED QUERY. DIRECTQUERY IS WAY THAT YOU CAN CONNECT TO DATA SOURCE WITHOUT IMPORTING THAT INTO POWER BI AND EVEN IF IT IS PETABYTE OF DATA IT WOULD WORK, RIGHT? SO, PROBLEM SOLVED. BUT THEN AFTER WORKING WITH DIRECTQUERY, YOU REALIZE THAT, DIRECTQUERY -- I MEAN IT'S POSSIBLE OPTION BUT IT IS NOT FAST, IT'S VERY SLOW, VERY, VERY SLOW. HOW YOU CAN GET DIRECTQUERY TO WORK. NOW, THAT IS THE TIME THAT YOU MIGHT HEAR ABOUT, OKAY THERE IS SOMETHING CALLED COMPOSITE MODEL THERE'S SOMETHING CALLED AGGREGATION HOW THESE TWO CAN WORK TO HELP IN THAT SITUATION. THAT IS WHAT I'M GOING TO SHOW YOU. WE ARE GOING TO TALK ABOUT COMPOSITE MODEL AND AGGREGATION AGGREGATIONS, RIGHT? JUST A LITTLE BIT ABOUT IMPORT TODAY R DATA AND DIRECTQUERY IN POWER BI I'LL EXPLAIN THAT A LITTLE BIT. WHEN YOU IMPORT DATA INTO POWER BI YOU ARE RESPONSE TIME IS VERY FAST BECAUSE EVERYTHING LOADS INTO THE MEMORY, IT'S SUPER FAST , EVERYTHING JUST LIKE CLICK, DRAG, DROP. BUT YOUR DATA IS NOT FRESH BECAUSE YOU HAVE TO REFRESH THAT DATA, RIGHT? IT DOESN'T WORK FOR LIKE BIG DATASET. IF YOU HAVE LIKE PETABYTES OF DATA YOU CANNOT USE IMPORT DATA. YOU GO TO DIRECTQUERY BECAUSE YOU HAVE REALLY LARGE DATASET AND BECAUSE YOU WANT DATA TO BE FRESH, RIGHT? NOW, IMAGINE A REPORT LIKE THIS. IN A REPORT LIKE THIS YOU HAVE LIKE FOUR VISUALS, ALL DIFFERENT VISUALS AND WHEN YOU USE IT IN A DIRECTQUERY SCENARIO IT'S NOT JUST ONE QUERY SENT TO THE DATA SOURCE. IT'S NOT JUST ONE QUERY, IT'S SIX QUERIES. SOMETIMES YOU HAVE PAGES THAT EVEN HAVE LIKE 15 VISUALS, RIGHT? NOW, IMAGINE THAT SCENARIO IF WE USE IT WITH COMPOSITE MODEL, COMPOSITE MODEL WILL GIVE YOU COMBINATION OF THINGS. WITH COMPOSITE MODEL YOU WILL HAVE THE FEATURES OF DIRECTQUERY WHICH MEANS WITH COMPOSITE MODEL YOU GET LARGE DATASET WORKING WITH SUPER LARGE DATASET BUT YOU ALSO GET A GOOD PERFORMANCE, RIGHT? THAT IS WHAT COMPOSITE MODEL OFFER, RIGHT? NOW, HOW THIS IS GOING TO WORK, RIGHT? IMAGINE YOU HAVE A DATA MODEL, A LOT OF FABLES, PRODUCT TABLE, SALES SCENARIO, CUSTOMER TABLES, SALES TABLE ITSELF, RIGHT? WHEN YOU WANT TO BUILD A SOLUTION RIGHT NOW LET'S SAY IMPORT OR DIRECTQUERY YOU EITHER IMPORTING ALL OF THESE TABLES OR YOU SET THEM ALL AS DIRECTQUERY, RIGHT? NOW, THE PROBLEM IS THAT THESE TABLES ARE NOT ALL AT THE SAME SIZE. A SALES TABLE IS BIG TABLE THIS IS PROBABLY 200 MILLION ROWS. YOU DON'T WANT TO IMPORT THAT INTO POWER BI YOU WANT TO KEEP THAT AS DIRECTQUERY BUT YOU'RE, LET'S SAY , PRODUCT TABLE YOU HAVE 1, 000 PRODUCT , 2, 000 PRODUCT, 10, 000, 100, 000, MILLION, RIGHT? A TABLE LIKE SALES TABLE WHICH IS BIG TO BE DIRECTQUERY, RIGHT? AND THAT IS WHAT COMPOSITE MODEL WILL GIVE YOU, WITH COMPOSITE MODEL YOU HAVE SOME OF YOUR TABLES IMPORTED, SOME OF THE TABLES DIRECTQUERY, RIGHT? IT'S COMBINATION OF DIRECTQUERY AND IMPORT, RIGHT? I'M GOING TO SHOW YOU FIRST HOW THIS IS GOING TO WORK, YOU MIGHT HAVE SEEN THIS KIND OF VIDEO BEFORE, BUT THE MAIN THING THEN AFTER THAT I'LL SHOW YOU HOW THIS IS IMPLEMENTED, RIGHT? SO, HERE I HAVE DATASET THAT IS LIKE ONE TRILLION ROWS OF DATA YOU MIGHT HAVE SEEN THIS DEMO, CHRISTINE SHOWED IT COUPLE OF SECTIONS BEFORE. YOU SEE WE ARE WORKING WITH ONE TRILLION ROWS OF DATA AND IT'S JUST AS FAST AS THIS. DRAG AND DROP. I'M NOT RUNNING IT JUST RIGHT NOW, THIS IS A RECORDED VERSION BECAUSE IF I WANT TO RUN IT, I HAVE TO SPIN UP A VM THAT TAKES LIKE 25 MINUTES TO COME UP, A LOT OF SCENARIOS LIKE THAT. BUT YOU SEE HOW FAST THIS THIS SCENARIO IS, RIGHT? IT'S JUST MATTER OF JUST CLICK, EXPAND, GO TO THAT SCENARIO, I'LL JUST DRAG IT OVER HERE IT ALL WORKS SUPER FAST. HIGHWAY THIS IS WORKING THAT IS A EXAMINATION IT MODEL. THIS IS WORKING WITH SOMETHING CALLED AGGREGATION . THAT AGGREGATION WHAT I'M GOING TO SHOW YOU HOW YOU CAN GET IT WORKING. YOU CAN GET IT WORKING EVEN IN YOUR EXISTING MODELS, LET'S GO TO THAT EXAMPLE. THIS IS WORKING FAST BECAUSE OF THE AGGREGATION. NOW IMAGINE WE HAVE DATA MODEL LIKE THIS, RIGHT? IN THIS SAMPLE DATA MODEL WE HAVE TRANSACTIONAL TABLE IN THE MIDDLE, SALES TRANSACTION TABLE. IMAGINE THIS IS A HUGE TABLE LET'S SAY 300 MILLION ROWS IN THIS TABLE, RIGHT? AND YOU WILL OF THE IRRITATION MIGHT NOT BE THAT. BACK TO THE SAME SCENARIO THAT WE HAD THAT REPORT WITH SIX VISUALS. THESE ARE ALL SENDING DIRECTQUERY TO THE SOURCE, RIGHT? WHEN YOU ARE BUILDING A REPORT ON DATA MODEL LIKE THISU ARE YOU LOOKING AT EVERY SINGLE TRANSACTION EACH TIME YOU ARE LOOKING AT A VISUAL, NO. YOU ARE LOOKING AT AGGREGATED DATA . THIS IS ONE OF MY VISUALS, RIGHT ? THIS VISUAL IS SHOWING SALES BY YEAR, RIGHT? FIVE DIFFERENT COLUMNS, FIVE DIFFERENT YEARS AND SALES AMOUNT OF EACH YEAR. THAT IS AGGREGATED DATA. THIS IS NOT EVERY SINGLE TRANSACTION IN A TABLE WITH 300 MILLION ROWS, RIGHT? IF I'M LOOKING AT AGGREGATED DATA WHY SHOULD I GO AND LOOK AT THAT TRANSACTION TABLE, RIGHT? I CAN BUILD AGGREGATED TABLE LIKE THIS, FIVE ROWS, BECAUSE WE HAVE LIKE FIVE VALUES HERE. FIVE ROWS AND ANY OTHER MEASURES OR COLUMNS THAT I HAVE. THIS IS A TABLE WITH FIVE ROWS. A TABLE WITH FIVE ROWS IS NOTHING YOU CAN JUST IMPORT IT IN POWER BI, RIGHT? THIS IS AN AGGREGATED TABLE. NOW, THE WHOLE CONCEPT FOR AGGREGATION IN POWER BI IS THAT YOU BUILT THE AGGREGATED TABLE, YOU PUT IT IN POWER BI , THEN WHEN REQUEST LIKE THIS COMES, THERE IS AGGREGATED TABLE. IT WILL NOT GO TO THE ACTUAL TRANSACTIONAL DATABASE TABLE IT WILL JUST GIVE YOU THE RESULT BASED ON THAT AGO GENTLEMAN GATED TABLE. IF THE AGGREGATED TABLE IS NOT THERE IT WILL COME TO THE TRANSACTIONAL DATABASE. USING POWER BI TO MAKE YOUR MODEL SUPER FAST TO BUILD THESE AGGREGATED TABLES, RIGHT? HOW YOU BUILD THIS AGGREGATED TABLE, LET ME SHOW YOU. FIRST STEP IS TO CREATE THAT AGGREGATION. THIS AGGREGATION CAN BE DONE ANYWHERE. FOR EXAMPLE, IF YOUR DATA SOURCE IS SQL SERVER DATABASE YOU CAN DO LIKE VIEW, GROUP BY, WHATEVER OVER THERE TO BUILD YOUR AGGREGATED TABLE. IF IT'S NOT THERE, IF YOU USE POWER QUIVER TEE FOR YOUR DATA TRANSFOR MAKES. SUMMARIZED ALL OF THOSE THINGS. AND BUILT YOUR AGGREGATED TABLE. WHEN YOU BUILD YOUR AGGREGATED TABLE THEN YOU HAVE TABLE LIKE THIS, I'LL SHOW YOU ALSO IN THE MEANTIME SO YOU CAN SEE THIS. HERE YOU SEE I HAVE FACT TABLE IMAGINE THIS IS MY BIG TABLE YOU SEE HERE THAT IT IS AN AGGREGATED TABLE. I'LL ALSO ZOOM IN SO YOU CAN SEE BIGGER. SO MY FACT TABLE IS DIRECTQUERY TABLE WHEN I HOVER ON IT YOU'LL SEE THIS IS A DIRECTQUERY TABLE. BUT I ALSO HAVE ANOTHER TABLE SALES AG , WHEN I HOVER ON THIS, THIS IS AN IMPORT DATA TABLE. IT IS AN AGGREGATED TABLE IT'S MUCH SMALLER COMPARED TO MY SALES TABLE. HOW I BUILT THIS AGGREGATION, THIS EXAMPLE I BUILD IT WITH POWER QUIVER TEE BUT YOU CAN DO IT IN ALL OTHER METHODS THAT I MENTIONED TO YOU. HERE FOR BUILDING THIS I USED A GROUP BY OPTION IN POWER QRTY. THIS IS HOW IT WORKS. YOU CHOOSE ALL THE COMBINATION OF BUILDS THAT YOU WANT TO BE GROUPED YOU CHOOSE ALL -- AGGREGATION FUNCTION, IS THAT YOU WANTED TO. WHAT WAS THE NEXT STEP. THE NEXT STEP AFTER DOING THAT IS TO -- GOING BACK TO THAT MODEL. THIS IS THE SAME MODEL THAT WE HAD. WE NOW HAVE NEW TABLE, AGGREGATED TABLE. NOW, THE NEXT STEP IS TO SET STORAGE MODE OF THESE TABLES. WE HAVE THREE STORAGE MODES. STORAGE MODE ONE IS IMPORT DATA. EVERYONE KNOWS THAT, RIGHT? WHEN YOU SAY IMPORT DATA IT IMPORTS DATA INTO POWER BI . THE NUMBER TWO IS DIRECTQUERY. IT WON'T COPY DATA INTO MEMORY IT WILL KEEP THE COPY -- IT WILL KEEP THIS DATA VERY -- IN THE DATA SOURCE. NUMBER THREE IS THE IMPORTANT ONE. THAT IS EXACTLY BECAUSE OF THIS AGGREGATION TABLE. LET'S SEE HOW IT IS WORKING. CONSIDER THAT AGGREGATED TABLE THAT WE BUILT IT'S CALLED SALES AG. THIS IS AGGREGATED TABLE THAT I BUILT. THIS IS IMPORT TABLE, IT'S A SMALL IMPORTED TABLE . NOW, I ALSO WANT A TABLE LIKE MY DATE FABLE LIKE MY CUSTOMER TABLE, THOSE SMALL DIMENSION TABLES ALSO TO ACT LIKE IMPORT TABLE WHEN I QUERY THESE DO, RIGHT? ON THE OTHER HAND SIDE, IF I HAVE MY ACTUAL TRANSCAN TABLE WHICH IS A DIRECTQUERY, I WANT MY DIMENSION TO ACT LIKE A DIRECT QUERY OPTION FOR THOSE. IMAGINE WHAT WE HAVE SAME DIMENSION, DATE DIMENSION IN THIS SCENARIO SHOULD WORK IN AN IMPORT DATA SCENARIO. IT SHOULD NOT SEND QUERY TO THE DATA SOURCE. IT SHOULD JUST, ALL WORK IN THE MEMORY. IN A SCENARIO LIKE THIS, THIS SHOULD WORK LIKE A DIRECTQUERY. IT SHOULD SEND THE QUERY TO THE DATA SOURCE. I HAVE ONE TABLE, MY DIMENSION TABLE, SOMETIMES IT SHOULD ACT LIKE IMPORT, SOMETIMES IT SHOULD ACTED LIKE DIRECTQUERY. AND THAT IS WHAT IT MEANS BY DRAW. DRAW MODE OF THE STORAGE IS THAT THESE TABLES THAT ARE CONNECTED TO IMPORT AND DIRECTQUERY AT THE SAME TIME, DEPENDS ON WHICH TABLE YOU USE IN YOUR SLICING AND DICING THEY MIGHT DO EITHER IMPORT OR DIRECTQUERY, RIGHT? ALL OF THOSE TABLES THAT YOU USE IN YOUR MODEL LIKE IN THIS SCENARIO, THIS IS MY DIRECTQUERY TABLE. ALL OF THE TABLES THAT ARE ONLY CONNECTED TO THAT, THEY ARE DIRECTQUERY . THAT IS MY IMPORT DATA TABLE. ALL OF THOSE THAT ARE CONNECTED ONLY TO THAT ARE IMPORT, BUT THOSE TABLES THAT ARE CONNECTED TO THIS AS WELL YOU SEE CUSTOMER HERE, OUR DATE HERE IS DRAW. ANY OTHER COMBINATION OF THOSE, THIS IS RELATED TO ANOTHER TABLE , THIS WOULD BE DRAW. DRAW IS STORAGE MODE THAT MEANS DEPEND ON YOUR VISUALIZATION IT MIGHT SEND QUERY TO THE DATA SOURCE, DIRECTQUERY OR MIGHT KEEP EVERYTHING IN THE MEMORY IMPORT . WHERE YOU SET THIS CONFIGURATION HERE IS WHERE YOU SET THE CONFIGURATION IN THE POWER BI. IN THE SOLUTION THAT YOU HAVE. WHEN YOU SELECT THE TABLE I'LL SHOW YOU OVER HERE BECAUSE IT'S JUST EASIER TO SEE IT HERE. WHEN YOU SELECT THE TABLE, UNDER ADVANCED SECTIONS OF THAT TABLE YOU SEE WHAT IS THE STORAGE MODE LIKE CUSTOMER IS -- SALES AG IS IMPORT, FACT INTERNET SALES IS DIRECTQUERY, ALL OF THESE THREE OPTIONS OVER HERE. SECOND STEP IS IS TO SET THIS STORAGE MODE . THIRD STEP AFTER DOING THAT STORAGE MODE IS TO SET THE AGGREGATION, RIGHT? NOW, YOUR POWER BI SOLUTION SHOULD WORK LIKE THERE IS NO AGGREGATED TABLE. BECAUSE USER DOESN'T KNOW THAT THERE IS AGGREGATED TABLE AND THEY SHOULD GO TO GET FROM THE AGGREGATED. THEY GO AND USE THE DIRECTQUERY TABLE THAT THEY HAVE. BUT BEHIND THE SCENE YOU ARE GIVING THEM THE RESPONSE OF AGGREGATED TABLE. HOW DOES THAT WORK? IN POWER BI WHEN YOU SET AGO GENTLEMAN GATED TABLE LIKE THIS TABLE THAT WE HAVE, ON THAT TABLE IT WILL GO TO MANAGE AGGREGATION S YOU'LL SET THAT UP, YOU SAY THAT, FOR EXAMPLE, FOR THIS FIELD WE DO GROUP BY, FOR FIELD LIKE THIS WE GET SOME OF SALES. YOU SET ALL OF THESE AGGREGATIONS, RIGHT? IF THERE IS A QUERY COMING, THIS IS THE AGGREGATION THAT WILL BE DONE ON THAT. YOU'LL SET ALL OF THESE EXACTLY SIMILAR OF HOW YOU DID THE GROUP ING, RIGHT? AND AFTER THAT, YOU HIDE THIS TABLE. YOU SEE THIS IS A HIDDEN TABLE, I'LL SEE IT HERE BUT I DON'T SEE IT HERE, YOU SEE THERE IS NO SALES AG TABLE. THERE IS AGGREGATED TABLE BEHIND THE SCENE, USER WON'T SEE THAT. HOW DOES IT WORK? LET ME SHOW YOU AN EXAMPLE. THIS IS A POWER BI SOLUTION, YOU SEE INTERNET SALES HERE WHICH IS A DIRECTQUERY SOLUTION AS YOU SEE WHEN I HOVER ON IT. NOW I'M RUNNING TOOL BEHIND THE SCENES, LET ME OPEN IT. THIS TOOL ALLOWS ME FORM FACTOR CATCH QUERIES SENT TO THE DATA SOURCE, IT'S CALLED SQL PROFILER , IF YOU WORKED WITH SQL , YOU MIGHT BE FAMILIAR WITH THAT. I RUN THIS TOOL. NOW THIS IS MONITORING EVERY SINGLE QUERY SENT TO THE DATA SOURCE. I BRING MY POWER BI REPORT UP. IF I BRING A FIELD FROM INTERNET SALES BECAUSE IT'S DIRECTQUERY SOURCE IT SHOULD SEND QUERY TO THE DATA SOURCE. LET ME BRING LIKE SALES AMOUNT. I'LL BRING IT HERE. IT COMES UP WITH THE RESULT. NOW WHEN I GO HERE, NO QUERY SENT TO THE DATA SOURCE. WHY? BECAUSE THAT AGGREGATED TABLE IS BEHIND THE SCENE. BECAUSE I HAVE CREATED AGGREGATED TABLE AND POWER BI KNOWS THAT THERE IS AGGREGATION FOR THIS, IT WILL GIVE ME THE RESPONSE BASED ON THAT AGGREGATION. NOW IF I SLICE AND DICE STILL THAT AGGREGATION MIGHT BE WORKING. FOR EXAMPLE, I'LL SLICE AND DICE THIS BY GENDER. STILL NO QUERY TO THE DATA SOURCE. BECAUSE REMEMBER MY AGGREGATED TABLE HERE, MY AGGREGATED TABLE IS AN AGGREGATED TABLE BY DATE, MY CUSTOMER AND SUB CATEGORY, RIGHT? SO FOR EVERYTHING THAT I GOT IS AMONG THIS COMBINATION. EVERYTHING SO FAR IS IN MEMORY. NOW I BRING SOMETHING WHICH IS NOT IN THIS AGGREGATION LIKE PROMOTION. I HAVE A TABLE CALLED PROMOTION. AND PROMOTION TABLE IS NOT PART OF MY AGGREGATED TABLE. AS SOON AS I BRING THAT THERE, I COME HERE, I SEE QUERY SENT TO THE DATA SOURCE. THIS RESPONDED FAST BECAUSE MY DATA SOURCE IS NOT SLOW. IT'S JUST SAMPLE LIKE 100, 000 ROWS, RIGHT? THIS IS JUST FOR THIS SAMPLE. BUT IMAGINE IF THIS WAS A REAL BIG DATA THIS WAS TAKING LONG TIME BECAUSE IT WAS DIRECTQUERY . NOW HOW DO YOU GET THIS TO WORK FOR OTHER COMBINATION. BECAUSE FOR EXAMPLE, THIS IS NOW GOOD FOR THIS COMBINATION, THIS IS GOOD FOR IF I HAVE SOMETHING BY DATE, BY CUSTOMER AND BY SIB CATEGORY BUT WHAT IF I WANT TO GET THAT ALSO WORKING FOR PROMOTION. I DON'T WANT THAT TO RUN DIRECTQUERY AGAIN. SO WHAT I WILL DO IS I WILL CREATE ANOTHER AGGREGATED TABLE. THIS IS A SECOND AGGREGATED TABLE. THIS IS OUR ACTUAL FACT TABLE THAT IS AGGREGATED TABLE. THAT IS SECOND AGGREGATED TABLE BY ANOTHER COMBINATION OF THESE FIELDS. YOU CAN HAVE MORE THAN ONE AGGREGATED TABLE. THAT EXAMPLE OF ONE TRILLION ROW S HAD SEVEN AGGREGATED TABLES. IT'S LIKE LAYERS. YOU PUT SMALLER TABLE FIRST LIKE A TABLE WHICH IS PROBABLY LIKE ONE MILLION ROWS. THAT IS YOUR FIRST LAYER OF AGGREGATION . SECOND TABLE WITH FIVE MILLION ROWS, SECOND LAYER, 20 MILLION ROWS, RIGHT? IT GOES ALL THE WAY TO OTHER LEVELS. HOW YOU SET THIS UP THAT IT WORKS WITH OTHER LEVELS, THERE IS SOMETHING IN POWER BI CALLED PRECEDENCE, I'M GOING TO CLOSE THIS BECAUSE THAT EXAMPLE IS NOT HERE. SOMETHING CALLED PRECEDENCE. WHEN YOU SET PRECEDENCE YOU SAY, OKAY, THIS TABLE WHICH IS ONLY SALES BY DATE IS MY FIRST AGGREGATION , PRECEDENCE OF THAT ZERO. SECOND TABLE IS A TABLE THAT IS BY DATE AND CUSTOMER, PRECEDENCE OF THAT IS ONE. THEN TWO, THREE, FOUR, ALL THE WAY TO THE END. AS A RESULT IT WOULD WORK LIKE THIS. LET'S SAY I HAVE A VISUAL THAT IS SALES BY CUSTOMER THAT CAN GET OUTPUT FROM THE FIRST AGGREGATED TABLE. OR I HAVE ANOTHER VISUAL IN THIS SCENARIO THAT HAS SOMETHING LIKE PROMOTION, IT'S NOT IN THERE BUT IT IS IN THERE, RIGHT? IN ANOTHER AGGREGATED TABLE. IT WILL STILL WORK IN MEMORY, EVERYTHING WORKS IN MEMORY THAT WOULD STILL BE FAST. IT WON'T BE THAT FAST, BUT IT WILL BE FAST, RIGHT? IF I HAVE A VISUAL THAT HAS SOMETHING, NOT IN ANY OF THOSE AGGREGATED TABLE IT WILL COME WITH ACTUAL DIRECTQUERY TABLE, THAT IS QUITE RARE SCENARIO BECAUSE YOU MANAGE TO CREATE A LOT OF LAYERS OF AGGREGATION. MORE LAYERS OF AGGREGATION, BETTER OUTCOME YOU WILL HAVE, RIGHT? THIS IS HOW YOU BUILT ENTIRE SCENARIO , HOW I HAVE EXPLAINED ALL OF THESE IN ARTICLES, THESE ARE ALL FREE ARTICLES AVAILABLE ON WEBSITE, THAT QR CODE IF YOU USE THAT GOES TO POWER BI BOOKS THAT I WROTE FOR FREE YOU CAN USE THAT. AND ALL OF THESE ARTICLES ARE OVER THERE. THESE ARE MY CONTACT INFORMATION IN CASE YOU HAVE ANY QUESTIONS, I HAVE SOME STICKERS HERE AND MY BUSINESS CARD FEEL FREE TO STOP BY. THANK YOU.
Info
Channel: Microsoft Power BI
Views: 9,692
Rating: undefined out of 5
Keywords: mbas19, mbas2019, Aggregation and composite model: Microsoft Power BI fast and furious - THR3016, Power BI, 20-minute Theater, Advanced (300), Architect (Technical), Business Analyst, IT Professional / Developer, Consultant (Technical), Infrastructure Architect, Solution Implementer, Community Session
Id: HXuvdgQRS8o
Channel Id: undefined
Length: 20min 48sec (1248 seconds)
Published: Tue Jun 11 2019
Related Videos
Note
Please note that this website is currently a work in progress! Lots of interesting data and statistics to come.