Really awesome. This is the only tutorial I found that discussed what to do if your fields don't have keys. Everyone else assumes the world is perfect!
Hi @Amar, do you still need assistance with this? What you need to do is create a custom column and concatenate the two columns together. You then need to do the same in the dimension table. Then you join from the DIM to the main fact table using the new custom column. hope that helps. Thanks, TBIWK
One question... have you tried this workflow on dataflows? I'm running into an issue very similar to this one: community.powerbi.com/t5/Service/Dataflow-On-Prem-execution-not-supported-for-entity/td-p/1000552
This is so fantastic - Just what I was looking for and it was nicely explained. I'm happy I found this site. I have subscribed and look forward to checking out all your other tutorials. Thank you so very much!
There's a couple of questions here from me, will it be a new file to replace the existing file or additional files to add to the report? If it is the first one then you can simply replace the existing file, as long as the format is exactly the same and it will run through the transformations. If it is a new file then you will need to amend the import of the data to perhaps a folder source, and then create your schema from there. Does that make sense? Thanks, TBIWK
Cheers Ryan, this sort of stuff used to take days, if not weeks to do before Power BI. When I realised Power BI did it I had to share it because it is easy compared to the way it used to be :-)
Greetings from Paris. Thank you so much for this video. Did you make another video to explain how did you perform the dashboards that you showed in the end of this video?
Sir, At 5:36 you said the bottom level distinct value is product which means we should always merge tables by joining the most granular level data in the column. and sir when i am merging tables its showing no preview available is it because i am referencing table rather than duplicating.
Hi @Hitesh, Referencing will not work in this instance because the query from which you are referencing will be changing and so the connection with the new table will always be corrupt. Always create new query by duplicating the query. Hope this helps, thanks, TBIWK.
Hi Heshi, what do you mean exactly? They will automatically appear once you have done the transformations in the query editor. The relationships should also automatically join onto one another. Does that answer your question? Thanks, TBIWK
Hi . Your video helped me a lot. I have a questions about your 'Date Table' I noticed you create a conection using both Order Date and Ship Date.. If i'm not wrong , then i should take the 'Date field' from Date table to manage X axis within my charts to show a trend ove the time. But how PBI select the date from Fact Table ? What if I want to create a trand line by Order Date and other trend line by Ship date ? I only have 1 column called 'Date' on my calendar table.. There is any way to select which date I want to use in my trend line?
Hi thanks for your comment. So what you must remember is that there is only 1 active connection to the date table and this is your main date. For this example it is Order Date so we can simply use the SalesAmount value for example in a trend line and that will show the sales by order date. To create a trend line for Ship date you must create a new measure and use the USERELATIONSHIP() function. In this function you specify to use the ShipDate relationship. Example, Calculate(SUM(SalesAmount), USERELATIONSHIP(DateTable[Date], ShipDate)). This will ensure the trend is based on the Ship date, and you can use the same date table calendar for the x-axis. Remember the relationship must exist but doesn't have to be active. It is activated through this function. Hope that helps. Let me know how you get on, thanks, TBIWK,
That's awesome, now I can transform my star schema model easily from excel to power BI using this video as my reference, thank u for sharing. I just have a question, let's assume I want to connect power BI to live data where the final dashboard will be updated automatically, is this an available option on power BI ? If yes and I believe yes, can we still apply start schema ? Would appreciate any reference that support my cause 🙏
Hey thanks for the comment and glad you like the video. For your scenario, live data I'm assuming means direct connection rather than an import, which means the data source needs to be in the star schema already as you can't transform the data to that level with live data. If it's being imported and refreshed often as almost live data then it will be possible. Hope that helps. Thanks, TBIWK.
Hi Gabor, I would suggest give it a go but essentially if your data changes in any way it will effect the dimension you create. Because it 'references' the data set that connected data set it subject to change and could error. Hope that helps, Thanks, TBIWK.
@@TheBIWizzKid would like to understand the data flow in data model which happens with either single direction relationship /bi directional relationship....ex i have 3 tables Dim_A connected to Dim_B and Dim_B connected to Fact_table How will Dim_A table get data from fact table
I followed your video with a data set that includes three date columns and three-time columns. The star schema turned out correct except my fact table begin to duplicate more rows as I added the key columns. Anyone knows how to fix this issue? I now have like 996,000 rows when I only supposed to have 34,000 rows
Hey, 996k rows from 34k, that's a big jump! Ok so first thing let's find out where the duplication is happening and it's likely at the time where you merge query. So, in your query steps find where you merged the first query and add a rowcount function before the merge, and take note of the count. Then move the rowcount function to after the merge and if this increases then you have found where it starts to duplicate. Let me know when you have done this and come back to me. Thanks, TBIWK
@@TheBIWizzKid I figured it out, I have a lot of null and blank fields that was causing that. However, I need to keep them so I will need to make my tables smaller with less columns so I can make one of them distinct.
Hi, quick question. I am working on a report, and I am currently applying the star schema format. Instead of adding as new query, is it ok to create a new query via the enter data option? The reason for thinking this is when I add as new query it keeps the same applied steps, and I believe this is slowing it down (I have tested with removing the steps as shown in video, but there are lots of steps, so I wondered if this might work as well). I imagine the only downside to this is if I add something new to the original column, I will also have to add it manually to the corresponding column rather than it adding it in automatically. Is this the case? Thanks.
Hi there, Yes you're right in that you will have to manually update the entered data as and when needed. It will likely be when you begin to see blank values in the visuals. What you can do is create an exception report that highlights if there are any blank values at which point you can go and handle that. Remember not to worry about the processing too much as this takes place behind the scenes and the end -user will not notice this happening. Hope that helps. Thanks, TBIWK.
Hi! I've a new question regarding this video ! Thanks for you previous answer on my last question btw. I was working on a project and I've noticed that I also have an extra option called ''Reference "" I've seen that the main difference Between Reference and Duplicate is the fact that ''Reference'' use the previous table as a source so It seems it's better in term of performances for big model because We dont't need to copy and call the Data 2 times and I just create a reference to the previous table. On the other hand, ''Duplicate'' need to create again all the steeps from the beggining What do you think about it ? I've tried both options but I' don't know what is the best option to create Dim tables from a Fact Table as you did in your video Could you help me to answer this question ?
Hey thanks for the comment. In terms of reference the reason I've not used it is because the new table references the source at all times, so when you change the data in that source table it will affect the new table, does that make sense? So creating a dimension and then removing the dimension values from the main source query will cause errors in your referenced table. Hence using Duplicate tables. Hope that helps. Thanks, TBIWK.
Brilliant video, really helped understand the steps in the process. I have set this model up using SSIS and SMSS using multiple sources. I now have a Sales star, I now want to do the same for a Cash Star. BI Wizz Kid what is the best technique to join the two Star models together. Also I see most tutorials advising to avoid Snowflake models can I also ask your thoughts on this please?
Hi Joe apologies for the delay. To join the two stars together you don't actually do that using a relationship. First of all you ensure they both are at the same level of granularity, then join them both onto the same dimensions. Dont join the fact tables together. Then when you create measures in your model you can filter this by any of the connected dimensions giving you a complete view of both fact tables. Hope that helps. And RE snowflake, yes avoid where possible! Too many scenarios to go into detail here. Thanks, TBIWK.
I downloaded the file and tried to follow along with the tutorials but i keep getting errors when i try to change my [order date ] & [ship date ] columns from text type to date type. Any suggestion how i can solve this ? Thank you.
Hi there, it could be that it is using the different location so it may be trying to show 21/01/20 as a US date which will error and vice versa. So check the regional settings in the PBIX file and try refreshing the preview. Let me know how you get on. Thanks, TBIWK
@@NancyMancarious Yes so once you import the main query, reference that query for all subsequent queries such as the fact and dimension tables (similar to the duplicate query I was using in the video) and disable the load for the main query before you load it all in. Does that make sense? Thanks, TBIWK
Hi Ming, if the product name changes that's fine, it will still link back the correct value and still have the correct ID for the connection. That's the great thing about the way I have done this. Try it and see. Let me know how you get on. Thanks, TBIWK
Hi Todd, in that case what you would do is go into the advanced editor for the date table that was invoked, and edit the M-Code to change the date to select todays date (or end of the year from todays date). Hope that helps. Thanks, TBIWK
Hello Sheb, I was wondering if you have an answer to my question; I added some extra UK postcodes in the "Postal Code" column on the file used in your video and I get "Data Format Error" message. What's the best way to solve the issue? Once again thank you for the video.
Hey Q, so if you click on the 'Error' cell, so don't click on the actual word Error but on the side of it in the same cell, it should give you some error details at the bottom of the editor. Do you want to copy that and paste it here? Might give us a better idea as to what is going on.
@@TheBIWizzKid Hey Sheb, the message is here: "DATAFORMATERROR; We couldn't convert to number Details N20 2F12". I suspect the attribute format of the postal code is causing the error since the US postal codes are numerical but the UK ones are a mixture of letters and numbers (N202F123 e.g.).
@@user-hu7ov6fi9y OK I see, so should be an easy fix, on the applied steps on the right hand side, find where the first step for 'Changed Type'. Click on that step so its highlighted, then on the post code column, click on the number format icon, and change it to 'Text' (the icon should be 'abc'). Let me know how you get on.
@@TheBIWizzKid hey, sorry to bother you but I changed the type of postal code from the whole number into text but I am facing the same issue. If you want I can send through the excel file to have a look by yourself, otherwise you can add a couple of rows and with the UK postcodes (a mixture of letters and words)
Thanks for the video! How's the performance for large tables and is there a way to optimize all those merges? Also, have you considered creating junk dimensions?
Hi Adolfo, Yes there are some other ways you can optimise the flow, but ultimately depends on your source. If you can query the data, from SQL for example, you could create your dimensions with a SQL query (select distinct..) and import those separately as dimensions. This will reduce the impact. Other sources may limit the what you can export, and so the only way to handle it is as shown, and limit the data as soon as possible in each query. You can also try the 'reference' option. So in the video where I 'right click' and 'duplicate' the query, on the same menu should be reference. This will stop the duplication of the data sets and instead acts as a direct connection between the two tables. The drawback being that if your dataset changes, the 'referenced' query will also be impacted. Let me know if that helps of if you have any more q's? Thanks BIWK
Great video - one thing I noticed when I tried this is that when you create a dimension from the fact table you assume that the fact table will stay static but when you add a new element like say a new product or you remove one it will flow through to the dimenson table as it will follow the steps each time a refresh and load is done - if you want to keep historic data and add names to it then it migh be worth pointing to an external table?
Hi Rubel, yes for sure if you want to keep the history then you need to have the historic data available for the data set. You can then do some clever transformations to create a historic view of the data. It would be a lot more involved than this video. Thanks, TBIWK
Appreciate the video, good explaining for a newbie. When applying the filters, do I have to create a new column for sum? Let's say I want to only see the total sum of the orders from a specific postal code? So I want to find the added cost to Postal Code 42420? Thanks for the help.
Hi, you can add the total sum to a card visual on the page, then add a slicer for postcode and on the ellipsis menu (...) for the slicer enable the search. You can then search for the postcode. Hope that helps. Thanks, TBIWK.
Suppose that your "Source" is a heavy SQL-beast. Would it be effective in this scenario to change for example a year-month column into a period ID, forcing the PowerBI to do the Source twice?
Hi Bjarke, What you have to keep in mind is that when you connect and refresh the data it happens once a day generally, and out of hours, and once it is done then Power BI is in a better state for reporting. So I would say if its a heavy query and you want to ensure best practise for data modelling and efficiency, then yes do it twice and have a dedicated Date Table. Does that make sense?
@@TheBIWizzKid Thank you for your answer. I obviously agree that the source will be run during the night, and therefor the server will have more ressources to do so. But to understand your answer correctly, when you duplicate your sources, PBI does not understand to run the source only once?
@@bjarkeahm I do understand, PBI does understand to run the one connection, because when you try and change the Data Source settings this only give you the one option. Even if you connect to it twice, there will only be one connection for PBI to refresh. Does that make sense? Thanks, TBIWK.
Hi @@paulgowrie1615 , that's great, you can do the same thing with the budget and actual tables and join to the same dimensions. That would be best practice. Do you know how to do it? Thanks, TBIWK
Hi Chandu, you should always have at least one column for this which will automatically be your bottom level. If there isn't one, then they may be separate dimensions. Hope that helps, TBIWK
@@tonyseno Yes, this way will ensure that all the possible options for the Star Schema DIMENSIONS are available. If you base the model, or star schema on a single table, there may be values in the other 29 tables that will show up blank. Hope that helps? Thanks, TBIWK
@@pokedigimaniaco Hey, see my reply below, there may be a number of queries you need to run, merge, append and join, and you can organise that in the Query Editor into folders and of course 'exclude from import'. You can also take care of all this in a DataFlow and connect to this as your data source, so that you don't need to repeat this process for new reports. Does that make sense? Thanks, TBIWK
Awesome presentation, Can you have a two or three star schemas model when the report views depend on multiple data sources, for instance, you a CSV, SQL server as sources for a report views
Hi, the best and ideal scenario is that you have a number of FACT tables, so perhaps one for Sales, one for Stock and another for Finance, and that all the Dimensions you have are able to relate to more than one fact table. In some cases you may have only 1, for example, Finance will not have a customer dimension it will be more DIM Accounts, whereas DIM Accounts won't link to Stock. Supplier will link to Stock and may be Sales, but not Finance. So yes you can have two or three models in the same file.
If the columns are the same renamed the file to there original source name and that should work automatically. If it has different columns you'll have to handle it on the transformations query editor. Hope that helps. Thanks, TBIWK
@@TheBIWizzKid Thanks for replying.so we need to do entire exercise again ? My question is how the dimension data will be refreshed. For example we had 100 unique rows in the product. Now the original flat fact file has 2 new rows with new product names. How to get two new rows in the product? dimension.
Ok I see, so if the data has more rows of products for the product dimension this will automatically feed through and assign the correct id's to the tables. So you don't need to change anything. Does that help? Thanks, TBIWK
I guess you are getting confused when I am saying new rows for the product. If the product already exists in the dimension of the product id which we created using the index column will work. But suppose a new product is added in fact a table which doesn't exist in our product dimension. How to update/refresh the index column for this Newly added product. For example, we have two products P1 and P2 in the original flat file .We created product dimension by adding the index column 1 P1 2 P2 then merged product id with Fact table & removed product name from fact. Now if we have a new row in Fact table product P3 .then how to get it automatically in product dimension with id 3 P3.
@@bikertejas7987 I understand what you are saying and it will add the new item in the dimension. That's the best thing about this solution is that it always uses the data in the file. So if like you said added products appear that were not there before, they will appear once the data has been refreshed. Try it, see what happens but it should work if you have followed the process above. Thanks, TBIWK
Thanks for the comment, I understand that it was recorded for a live event in which I had 20 mins to go over it. I can answer any questions you might have tho or look to do a similar video if that will help? Thanks, TBIWK
Hi! again I have a case where I have 3 sql database that has no common key to be used and I need to join them either in sql (and create a Star Schema as you have suggested) or in powerbi and incorporate the report refresh from there. Again appreciate your help
Hi Jeff, you can manipulate columns together by concatenating them so that they create a unique column. For example, country and postcode can be concatenated together to make a unique column for multiple countries. Not sure if that helps but if you can tell me what columns etc you have I can perhaps elaborate.
@@TheBIWizzKid Thanks I get your suggestion :-) In my case I have 20 columns (same number of field names) for each 3 tables but no common key to paired with and will need to join them into one. In your video (awesome! video tutorial) you have 1 dataset/source, can I apply this approach in my case that I have 3 tables particularly on adding the "Index Number" and joining them into 1 table? I was planning to do the same approach initially in SQL and/or then in PowerBI and create the schema
@@jeffrey6124 See if you can get me a snapshot/screen shot of the data and what it is you are trying to join. If you had a date in each table you could create a date table and that could be your connection between the three?
@@TheBIWizzKid I'm all set :-) I did make use of the UNION function and NOW I can proceed with creating the Star Schema .... The BI Wizz Kid way :-) Many thanks!
Sir, First of all thanks for the video learnt alot. Secondly If I want to load a New Flat file with same column name...do I need to develop Star Schema again or If we Upload a Flat File with same column name and New Data in it will get automatically updated in the developed star Schema?
Hi Shrikant, If you have new file with the same column names it will automatically update all the star schema dimensions. If there is a new column, or columns with different names, you will need to develop dimensions as per the previous steps. Does that help? Thanks, TBIWK.
Is it best to create a Star Schema in SQL Server before connecting it to PowerBI or connect the SQL Server datasource and create the Star Schema in PowerBI? Appreciate any response :-) Thank you in advance
Hi Jeff, by this I'm assuming you mean the Fact and Dimension tables? If so, then it's better for you to use SQL to create all the relevant fact and DIM tables, and then import them into Power BI to simply connect them up.
@@TheBIWizzKid Will creating an SQL view be enough in exchange of creating the Fact/Dimension tables in SQL Server given that there are no common fields including date to reference with and just import them to Power BI and then create the Star Schema?
Great Tutorial! Glad I found it. Question, Is it possible to create a multiple reports from different Business Units and creating multiple data model since they are not related? I need to combine all the reports from different departments and put it into one. Thanks
Hello Smile, thanks for your comment. I believe the answer is yes if I understand you correctly. You can bring in additional data sources into your Query Editor, and they don't have to relate to any other tables. If they relate to some, great, if not they can be used as stand alone data set for use on another page or in another visual. Does that makes sense? It may not be the most efficient way of doing it but its an option.
Thanks. My case is that I have many excel coming from SharePoint Folder and SharePoint List as a data source. It has a few columns for each excel like date,actual, target and descriptions that's why i didn't create another table except for a date table. Only a date table are connected to each excel data source. What do you think? Example: HR Data (Actual, Target, Date and Description) Finance Data (Actual, Target, Date and Description) Supply Chain Data (Actual, Target, Date and Description) etc They don't have relationship to each other but I only made one date table and connected to those tables.
@@harlynbugarin6183 That seems fine, the data sets you've mentioned there will be your 'fact' tables as per the star model and the connected Dimension will be the date. How much data do you have coming into the model (ie, 100 rows, 1000, 1M)?
@@TheBIWizzKid Thanks for your quick response. It has thousands of rows since the data is already summarize. And also we do have another model coming from different database should I connect it to date dimension like what other fact tables have? Meaning one pbix file has a multiple model and connected to only one date dimension? As of now I have 40+ report pages in one pbix file.
@@harlynbugarin6183 Wow 40 + pages in a pbix file, that a decent amount! Hehe. So what might be worth considering is Data flows. What this will allow you to do is create a model, similar to the one in the video, but outside a pbix file. So you essentially create a location to store your multiple models and connect to is as a data source. Remember that a report should have a focus and answer/prompt questions. Users may not have the time to view many pages of reports so its also key to see it through the end-user perspective.
Hi, really great video, was looking for this for some time now. Have a question tho. Why create index columns in de DIM tables to create a relation between the DIM table and the Fact table and not just use the specific field value form that DIM tabel to connect with the field in the FACT table. I understand the reason to create a DIM table, but why have ShippingMethode and ShippingID in DIM table, so you can make a relation between DM and Fact based upon ShippingID instead of making a relationship between DIM - ShippingMethode and Fact - ShippingMethode ?
Hi @Erik thanks for your comment. The reason to create an ID column is that Power BI, (and SQL server) perform ALOT quicker when using integer values. So when you reference a number is simple looks for the number that corresponds to that relationship. If you had a text value, that is unique lets say, it must trawl through each and every text value front to end and as you can imagine this take more processing power than just searching for a number right. So although you may not see the difference straight away, when you add more data and more more values to the FACT and DIM tables this will slowly become less efficient over time. Does that make sense? Thanks, TBIWK
@@TheBIWizzKid One more question. What if you have empty values in a column that you want to convert to a DIM column. After removing the duplicates from the DIM table, you will have a null value. Do you keep this in the table, or remove it from the DIM table? Thx again
@@ErikWesseling In this instance what you do is duplicate the column in the DIM, and replace the null value with a value that you want to replace it with, eg, "N/A". Then in your main table, the fact table, search and replace the null values with "N/A". Then join on the new duplicated column and this should handle the null values. Let me know how you get on, thanks, TBIWK
Hi Kevin, not a dumb question at all. If you get a new flat file, then you simply replace the existing file and 'Refresh' the data in Power BI, it should apply the same steps to that file (given they have the same columns headers). It could be that you mean a completely new file? Let me know.
@@TheBIWizzKid Thanks for that info. I tried and it worked. The only problem I had was that the flat files had index columns and it duplicated all of them. I just added a step to remove dupes on that column and it fixed
That's a great question and it is something you should do. The session in which I created this data model was a live session with a 20 minute time frame, the date field (and may be the order reference field) are those that were left out. Given another 5 minutes I would have created an ID for those too!
@@TheBIWizzKid Thank you again for getting back on this and apologies for the late response. Following you previous response I created a single sql view for 3 databases in sql server and connected it to PowerBI .... I will now attempt to do the Star Schema in PowerBI following this great video :-) Normally I would just enter the Server Name and search for the sql view but this time I did make use of the Advanced options and paste a script for my reporting requirements. But initially was prompt with an error requiring me to enter a "database name", so I did place 1 of the 3 database name that I was working on and it did run and produce the report I needed :-) My question is will it make any impact by placing either 1 of the 3 existing database name? while performing this? or it just uses that existing database name to display the script properly since It won't run without placing any existing database name? Below are the steps I performed: From Get Data > Server > "Database (optional)" > Select Import > Advanced options > SQL Statement > "Script" > OK
Hi @@jeffrey6124, thanks for your comment, and RE your question, can you explain a bit more about the 'impact by placing 1 of 3 databases' sentence? If you've ran the script successfully in SQL server, note the server name and make that the server you connect to from Power BI. That will ensure the script will run in Power BI. Adding a database name is fine, one of them will work you may need to try it, but you will need to put the full [database.dbo.tablename] reference in your script. Have you attempted to refresh your data? If not, try that as it will indicate if your current set up will work. Come back to me with a bit more detail if you can. Thanks
@@TheBIWizzKidWhen selecting the SQL Server Dbase under Get data initially I would just enter the server name then "Database" field entry is optional but If I would be selecting the Advance option since I have a script to use I would be prompt to enter a database name otherwise the OK button to proceed will be disabled, so I did place an existing Database name and I was able to proceed and got the query I needed in line with the script . Does placing an existing database name on the "Database (optional) field acts only as a trigger to run the script and make a connection to sql server? :-)
@@jeffrey6124 Because security in SQL can be at various levels, Power BI determines this at the connection time and queries are generally ran against a database. So it doesn't just act as a connection trigger it determines your access rights and ensures you can run the script against the database. Does that answer your question?
Hey, The flat file is the CSV file I used. What I mean by flat file is a data set that is in a single table with no relationships or connected tables. Hope that helps, thanks, TBIWK
Really awesome. This is the only tutorial I found that discussed what to do if your fields don't have keys. Everyone else assumes the world is perfect!
Hi Beta thanks for your comment much appreciated!
FINALLY a video class with the dataset, several classes from other channels don't have data.
Thanks for the comment Marcos! Hope it helped. TBIWK.
Thanks a million for the simple, detailed, straight-to-the-point explanation of the flat file-to-star schema model. Yes, you are great.
You are great too! Thanks, TBIWK
AWESOMNESS ! Star schema from scratch and in such a simple detailed explanation .
Glad you liked it! Thanks, TBIWK
This video finally connected all the pieces. I was creating my dimension tables and not merging my index back to the source. Thank you!
You are welcome Legal Brother.
1 million likes to this video, thanks bro for sharing such an amazing trick!
2:15 3:45 Dim customer
5:08 Creating product ID
Thanks @Saul much appreciated, TBIWK
There are only 2 videos on internet that is the best one is definately this one!!!
Thanks much appreciated! Let me know what else you need!
@@TheBIWizzKidjust provide the dataset
Thanks for this tutorial needed help with merging with multiple columns
Hi @Amar, do you still need assistance with this? What you need to do is create a custom column and concatenate the two columns together. You then need to do the same in the dimension table. Then you join from the DIM to the main fact table using the new custom column. hope that helps. Thanks, TBIWK
Excellent tutorial! Took forever to finally find this, but I'm so glad I did. Thank you!
Hey Randy thanks for the comment much appreciated, glad it was of use!
One question... have you tried this workflow on dataflows? I'm running into an issue very similar to this one: community.powerbi.com/t5/Service/Dataflow-On-Prem-execution-not-supported-for-entity/td-p/1000552
Hey Randy what is it that you were searching for when trying to find this video?
Now feels like : " I have the Power!!!"
Thanks Mate! :) cheers
Hey @Kumar yes you do have the power! Nice work! Thanks, TBIWK
thank you so much it was so helpful
No problem. Thanks, TBIWK
Omg, insane tutorial. Thank you so much!
You are very welcome Michael.
Excellent, exactly what i was looking for. Straight to the point and clearly demonstrated.
Thanks @Atif much appreciated 👍🏼
Great video, well explained. Thanks for this.😊
This is so fantastic - Just what I was looking for and it was nicely explained. I'm happy I found this site. I have subscribed and look forward to checking out all your other tutorials. Thank you so very much!
Thank you Joanne, your comment made me smile!
Thank you so much for this video! Very useful. Looking forward to the next one
Thanks much appreciated, there is more in progress!
Great video. Question: once this star schema is created, how do I append new data into these tables every-time I get new sales data file.
There's a couple of questions here from me, will it be a new file to replace the existing file or additional files to add to the report? If it is the first one then you can simply replace the existing file, as long as the format is exactly the same and it will run through the transformations. If it is a new file then you will need to amend the import of the data to perhaps a folder source, and then create your schema from there. Does that make sense? Thanks, TBIWK
Crazy power bi skills :) Awesome.
Thanks Sonet much appreciated!
Awesome explanation & thank you so much for sharing this kind of videos & we are expecting more videos from your end
Thanks for your comments! I'll be sharing more for sure!
Excellent Tutorial. Thanks for sharing your knowledge with us.
My pleasure!
Thank you. This was Iwas looking for.
Thanks!
Nice guide, you make it look so easy.
Cheers Ryan, this sort of stuff used to take days, if not weeks to do before Power BI. When I realised Power BI did it I had to share it because it is easy compared to the way it used to be :-)
Greetings from Paris. Thank you so much for this video. Did you make another video to explain how did you perform the dashboards that you showed in the end of this video?
Hi Sam, see this video, it may help with some report design. ruclips.net/video/Oo9G-zXxNDg/видео.html . Thanks, TBIWK.
Brilliant. Thank you so much!!
Glad it was helpful!
This video makes me your power BI Fan☺
Thanks for the comment!
Awesome stuff! This is exactly what I need to understand what's a good data model looks like. Love your video, subscribed.
Thank you, lovely comment appreciate that, Let me know of any real world Power BI challenges you have!
Pretty slick! Hope there is another video with multiple set of tables from SQL. Modeled in Star schema. Awesome content!
Thank Rolly V, tell me more about these SQL tables?? TBIWK
I love it! life saverrrrrr!!! Thank you!
You're welcome! Thanks, TBIWK
great video, finally now I can do my college work
Great news, hope it goes well! TBIWK
thank you so much for this tutorial!
You're welcome!
very helpful Sir! thank you!
Not a problem glad it was of help!
Thank you so much!
You're welcome, TBIWK
Loved it thoroughly.
Thanks Sidarth! Regards, TBIWK
Sir, At 5:36 you said the bottom level distinct value is product which means we should always merge tables by joining the most granular level data in the column.
and sir when i am merging tables its showing no preview available is it because i am referencing table rather than duplicating.
Hi @Hitesh, Referencing will not work in this instance because the query from which you are referencing will be changing and so the connection with the new table will always be corrupt. Always create new query by duplicating the query. Hope this helps, thanks, TBIWK.
Love you man
Love you too bro!
Thanks. After applying queries did the folders for fact and dim table come automatically?
Hi Heshi, what do you mean exactly? They will automatically appear once you have done the transformations in the query editor. The relationships should also automatically join onto one another. Does that answer your question? Thanks, TBIWK
Thanks for such an awesome tutorial😊👍
Thanks Adnan!
Thanks for the video dude
No problem 👍 Anytime! Thanks, TBIWK
This helped me so much. Thank you!
You're welcome! If you have any other challenges please ask!
Superb ! this is what i was searching for, from a long time
Glad it helped! To help me, what was it that you were searching for before you found this? I may need to update my search tags? Thanks!
Perfect! It's just that I needed. Thanks !
Great glad it helped!
Super awesome! I found this tutorial very helpful. One of the best out there for someone wanting to learn. Thank you so much!
Thanks much appreciated!
Hi .
Your video helped me a lot. I have a questions about your 'Date Table'
I noticed you create a conection using both Order Date and Ship Date..
If i'm not wrong , then i should take the 'Date field' from Date table to manage X axis within my charts to show a trend ove the time. But how PBI select the date from Fact Table ?
What if I want to create a trand line by Order Date and other trend line by Ship date ? I only have 1 column called 'Date' on my calendar table.. There is any way to select which date I want to use in my trend line?
Hi thanks for your comment. So what you must remember is that there is only 1 active connection to the date table and this is your main date. For this example it is Order Date so we can simply use the SalesAmount value for example in a trend line and that will show the sales by order date. To create a trend line for Ship date you must create a new measure and use the USERELATIONSHIP() function. In this function you specify to use the ShipDate relationship. Example, Calculate(SUM(SalesAmount), USERELATIONSHIP(DateTable[Date], ShipDate)). This will ensure the trend is based on the Ship date, and you can use the same date table calendar for the x-axis. Remember the relationship must exist but doesn't have to be active. It is activated through this function. Hope that helps. Let me know how you get on, thanks, TBIWK,
Fantastic!
Thanks!
That's awesome, now I can transform my star schema model easily from excel to power BI using this video as my reference, thank u for sharing.
I just have a question, let's assume I want to connect power BI to live data where the final dashboard will be updated automatically, is this an available option on power BI ? If yes and I believe yes, can we still apply start schema ? Would appreciate any reference that support my cause 🙏
Hey thanks for the comment and glad you like the video. For your scenario, live data I'm assuming means direct connection rather than an import, which means the data source needs to be in the star schema already as you can't transform the data to that level with live data. If it's being imported and refreshed often as almost live data then it will be possible. Hope that helps. Thanks, TBIWK.
great video!! but one thing i don't get is why dont you use reference table instead of duplicates?
Hi Gabor, I would suggest give it a go but essentially if your data changes in any way it will effect the dimension you create. Because it 'references' the data set that connected data set it subject to change and could error. Hope that helps, Thanks, TBIWK.
Thank you for your Data modelling explanation. It was saved my lot of valuable time. Pls upload Dax related videos also
Thanks Rakesh, tell me more about DAX videos you want? TBIWK
I need DAX to generate Sales Reports like Profit, Loss, Discount with their Percentages.
Pls explain about Relationship Functions.
Great video ...! super clean and to the point...Would be glad if you explain circular dependency relationship :-)
Thanks Husna! What is it you would like to know, what exactly are you trying to do?
@@TheBIWizzKid would like to understand the data flow in data model which happens with either single direction relationship /bi directional relationship....ex i have 3 tables
Dim_A connected to Dim_B and Dim_B connected to Fact_table
How will Dim_A table get data from fact table
Great stuff....
Thanks Henry!
hi, any chance for an online tutor session, maybe a couple of hours, I have specific questions on my data set
Hi Sadaf, sure this can be arranged. What are you looking to achieve and when do you want to do it? Thanks TBIWK
I followed your video with a data set that includes three date columns and three-time columns. The star schema turned out correct except my fact table begin to duplicate more rows as I added the key columns. Anyone knows how to fix this issue? I now have like 996,000 rows when I only supposed to have 34,000 rows
Hey, 996k rows from 34k, that's a big jump! Ok so first thing let's find out where the duplication is happening and it's likely at the time where you merge query. So, in your query steps find where you merged the first query and add a rowcount function before the merge, and take note of the count. Then move the rowcount function to after the merge and if this increases then you have found where it starts to duplicate. Let me know when you have done this and come back to me. Thanks, TBIWK
@@TheBIWizzKid I figured it out, I have a lot of null and blank fields that was causing that. However, I need to keep them so I will need to make my tables smaller with less columns so I can make one of them distinct.
Amazing.
Thank you! Cheers!
Hi, quick question. I am working on a report, and I am currently applying the star schema format. Instead of adding as new query, is it ok to create a new query via the enter data option? The reason for thinking this is when I add as new query it keeps the same applied steps, and I believe this is slowing it down (I have tested with removing the steps as shown in video, but there are lots of steps, so I wondered if this might work as well). I imagine the only downside to this is if I add something new to the original column, I will also have to add it manually to the corresponding column rather than it adding it in automatically. Is this the case? Thanks.
Also, the video was very helpful. Thank you!
Hi there, Yes you're right in that you will have to manually update the entered data as and when needed. It will likely be when you begin to see blank values in the visuals. What you can do is create an exception report that highlights if there are any blank values at which point you can go and handle that. Remember not to worry about the processing too much as this takes place behind the scenes and the end -user will not notice this happening. Hope that helps. Thanks, TBIWK.
Hi!
I've a new question regarding this video ! Thanks for you previous answer on my last question btw.
I was working on a project and I've noticed that I also have an extra option called ''Reference ""
I've seen that the main difference Between Reference and Duplicate is the fact that ''Reference'' use the previous table as a source so It seems it's better in term of performances for big model because We dont't need to copy and call the Data 2 times and I just create a reference to the previous table. On the other hand, ''Duplicate'' need to create again all the steeps from the beggining
What do you think about it ? I've tried both options but I' don't know what is the best option to create Dim tables from a Fact Table as you did in your video
Could you help me to answer this question ?
Hey thanks for the comment. In terms of reference the reason I've not used it is because the new table references the source at all times, so when you change the data in that source table it will affect the new table, does that make sense? So creating a dimension and then removing the dimension values from the main source query will cause errors in your referenced table. Hence using Duplicate tables. Hope that helps. Thanks, TBIWK.
You are a star!
So are you!
Brilliant video, really helped understand the steps in the process. I have set this model up using SSIS and SMSS using multiple sources. I now have a Sales star, I now want to do the same for a Cash Star. BI Wizz Kid what is the best technique to join the two Star models together. Also I see most tutorials advising to avoid Snowflake models can I also ask your thoughts on this please?
Hi Joe apologies for the delay. To join the two stars together you don't actually do that using a relationship. First of all you ensure they both are at the same level of granularity, then join them both onto the same dimensions. Dont join the fact tables together. Then when you create measures in your model you can filter this by any of the connected dimensions giving you a complete view of both fact tables. Hope that helps. And RE snowflake, yes avoid where possible! Too many scenarios to go into detail here. Thanks, TBIWK.
I downloaded the file and tried to follow along with the tutorials but i keep getting errors when i try to change my [order date ] & [ship date ] columns from text type to date type. Any suggestion how i can solve this ? Thank you.
Hi there, it could be that it is using the different location so it may be trying to show 21/01/20 as a US date which will error and vice versa. So check the regional settings in the PBIX file and try refreshing the preview. Let me know how you get on. Thanks, TBIWK
Wouldn't PBI reload the same query for each of these dimension tables? Is there a way to get around that and only load the query once?
Yes you're right, since this video I have looked to improve this process and using the reference function can improve the load time for this.
@@TheBIWizzKid you mean the refernce option of a source in Power Query?
@@NancyMancarious Yes so once you import the main query, reference that query for all subsequent queries such as the fact and dimension tables (similar to the duplicate query I was using in the video) and disable the load for the main query before you load it all in. Does that make sense? Thanks, TBIWK
Do you a file for the Data set so I can follow alone?
Great stuff
Thanks Jitendra, let me know what was most useful in the video?
what if one of the product name change? will the index assigned to it changes as well?
Hi Ming, if the product name changes that's fine, it will still link back the correct value and still have the correct ID for the connection. That's the great thing about the way I have done this. Try it and see. Let me know how you get on. Thanks, TBIWK
When using methods like the one you used to make your date table. How do I use those if my data gets updated every day?
Hi Todd, in that case what you would do is go into the advanced editor for the date table that was invoked, and edit the M-Code to change the date to select todays date (or end of the year from todays date). Hope that helps. Thanks, TBIWK
Thanks 🎉
Nice video!!
Thanks Oladipo, what did you like about the video?
Hello Sheb, I was wondering if you have an answer to my question; I added some extra UK postcodes in the "Postal Code" column on the file used in your video and I get "Data Format Error" message. What's the best way to solve the issue? Once again thank you for the video.
Hey Q, so if you click on the 'Error' cell, so don't click on the actual word Error but on the side of it in the same cell, it should give you some error details at the bottom of the editor. Do you want to copy that and paste it here? Might give us a better idea as to what is going on.
@@TheBIWizzKid Hey Sheb, the message is here: "DATAFORMATERROR; We couldn't convert to number Details N20 2F12". I suspect the attribute format of the postal code is causing the error since the US postal codes are numerical but the UK ones are a mixture of letters and numbers (N202F123 e.g.).
@@user-hu7ov6fi9y OK I see, so should be an easy fix, on the applied steps on the right hand side, find where the first step for 'Changed Type'. Click on that step so its highlighted, then on the post code column, click on the number format icon, and change it to 'Text' (the icon should be 'abc'). Let me know how you get on.
@@TheBIWizzKid hey, sorry to bother you but I changed the type of postal code from the whole number into text but I am facing the same issue. If you want I can send through the excel file to have a look by yourself, otherwise you can add a couple of rows and with the UK postcodes (a mixture of letters and words)
@@user-hu7ov6fi9y Hey, yes send me the file I can take a look, if you can post a link to a onedrive I will download it.
Thanks for the video! How's the performance for large tables and is there a way to optimize all those merges? Also, have you considered creating junk dimensions?
Hi Adolfo,
Yes there are some other ways you can optimise the flow, but ultimately depends on your source. If you can query the data, from SQL for example, you could create your dimensions with a SQL query (select distinct..) and import those separately as dimensions. This will reduce the impact.
Other sources may limit the what you can export, and so the only way to handle it is as shown, and limit the data as soon as possible in each query.
You can also try the 'reference' option. So in the video where I 'right click' and 'duplicate' the query, on the same menu should be reference. This will stop the duplication of the data sets and instead acts as a direct connection between the two tables. The drawback being that if your dataset changes, the 'referenced' query will also be impacted.
Let me know if that helps of if you have any more q's?
Thanks BIWK
@@TheBIWizzKid aq1
Is the powerBI tool in Microsoft free?
Yes you can download this from the Microsoft store or search for Power BI online. Search for Power BI Desktop. Thanks, TBIWK.
Great video - one thing I noticed when I tried this is that when you create a dimension from the fact table you assume that the fact table will stay static but when you add a new element like say a new product or you remove one it will flow through to the dimenson table as it will follow the steps each time a refresh and load is done - if you want to keep historic data and add names to it then it migh be worth pointing to an external table?
Hi Rubel, yes for sure if you want to keep the history then you need to have the historic data available for the data set. You can then do some clever transformations to create a historic view of the data. It would be a lot more involved than this video. Thanks, TBIWK
Appreciate the video, good explaining for a newbie. When applying the filters, do I have to create a new column for sum? Let's say I want to only see the total sum of the orders from a specific postal code? So I want to find the added cost to Postal Code 42420? Thanks for the help.
Hi, you can add the total sum to a card visual on the page, then add a slicer for postcode and on the ellipsis menu (...) for the slicer enable the search. You can then search for the postcode. Hope that helps. Thanks, TBIWK.
Suppose that your "Source" is a heavy SQL-beast. Would it be effective in this scenario to change for example a year-month column into a period ID, forcing the PowerBI to do the Source twice?
Hi Bjarke, What you have to keep in mind is that when you connect and refresh the data it happens once a day generally, and out of hours, and once it is done then Power BI is in a better state for reporting. So I would say if its a heavy query and you want to ensure best practise for data modelling and efficiency, then yes do it twice and have a dedicated Date Table. Does that make sense?
@@TheBIWizzKid Thank you for your answer. I obviously agree that the source will be run during the night, and therefor the server will have more ressources to do so. But to understand your answer correctly, when you duplicate your sources, PBI does not understand to run the source only once?
@@bjarkeahm I do understand, PBI does understand to run the one connection, because when you try and change the Data Source settings this only give you the one option. Even if you connect to it twice, there will only be one connection for PBI to refresh. Does that make sense? Thanks, TBIWK.
Can this method be applied to multiple data tables ?
Hi Paul, yes it can be, what sort of thing are you thinking?
Hi @@paulgowrie1615 , that's great, you can do the same thing with the budget and actual tables and join to the same dimensions. That would be best practice. Do you know how to do it? Thanks, TBIWK
why don't you create a blog where you can post small articles replying to some of the most frequently asked questions. that would be a great idea
for example... what is the difference between duplicate and reference and when to use it
@@chandu-mu2cg Thanks for the suggestion!
Hi Chandu, you should always have at least one column for this which will automatically be your bottom level. If there isn't one, then they may be separate dimensions. Hope that helps, TBIWK
But if your company wants a dashboard with 30 differents tables what do u recommend?
Hi Jose what do you mean exactly? 30 separate tables that have been imported?
@@TheBIWizzKid exactly, the information comes really segregated and i cant see how to build a decent model for it
I have similar issue, can we first joining/merging all the 30 tables into one flat table, and then convert it into star ?
@@tonyseno Yes, this way will ensure that all the possible options for the Star Schema DIMENSIONS are available. If you base the model, or star schema on a single table, there may be values in the other 29 tables that will show up blank. Hope that helps? Thanks, TBIWK
@@pokedigimaniaco Hey, see my reply below, there may be a number of queries you need to run, merge, append and join, and you can organise that in the Query Editor into folders and of course 'exclude from import'.
You can also take care of all this in a DataFlow and connect to this as your data source, so that you don't need to repeat this process for new reports. Does that make sense? Thanks, TBIWK
Can someone provide me the link for Dataset...?
Hi Ahmed, you can find all the content in this link, thanks! powerbisupport.co.uk/wp-content/uploads/2020/06/20-04-22.zip
@@TheBIWizzKid Thank you it is going to be very helpful for me to do a practice
Awesome presentation, Can you have a two or three star schemas model when the report views depend on multiple data sources, for instance, you a CSV, SQL server as sources for a report views
Hi, the best and ideal scenario is that you have a number of FACT tables, so perhaps one for Sales, one for Stock and another for Finance, and that all the Dimensions you have are able to relate to more than one fact table. In some cases you may have only 1, for example, Finance will not have a customer dimension it will be more DIM Accounts, whereas DIM Accounts won't link to Stock. Supplier will link to Stock and may be Sales, but not Finance.
So yes you can have two or three models in the same file.
Also did you find the link to the calendar script?
If we get any new data in source excel file .how to refresh the dimensions and fact tables created
If the columns are the same renamed the file to there original source name and that should work automatically. If it has different columns you'll have to handle it on the transformations query editor. Hope that helps. Thanks, TBIWK
@@TheBIWizzKid Thanks for replying.so we need to do entire exercise again ? My question is how the dimension data will be refreshed. For example we had 100 unique rows in the product. Now the original flat fact file has 2 new rows with new product names. How to get two new rows in the product? dimension.
Ok I see, so if the data has more rows of products for the product dimension this will automatically feed through and assign the correct id's to the tables. So you don't need to change anything. Does that help? Thanks, TBIWK
I guess you are getting confused when I am saying new rows for the product. If the product already exists in the dimension of the product id which we created using the index column will work. But suppose a new product is added in fact a table which doesn't exist in our product dimension. How to update/refresh the index column for this Newly added product. For example, we have two products P1 and P2 in the original flat file .We created product dimension by adding the index column 1 P1 2 P2 then merged product id with Fact table & removed product name from fact. Now if we have a new row in Fact table product P3 .then how to get it automatically in product dimension with id 3 P3.
@@bikertejas7987 I understand what you are saying and it will add the new item in the dimension. That's the best thing about this solution is that it always uses the data in the file. So if like you said added products appear that were not there before, they will appear once the data has been refreshed. Try it, see what happens but it should work if you have followed the process above. Thanks, TBIWK
Good video, but you went a little too fast and did not really explain the finer points of what you were doing and why. It made it hard to follow.
Thanks for the comment, I understand that it was recorded for a live event in which I had 20 mins to go over it. I can answer any questions you might have tho or look to do a similar video if that will help? Thanks, TBIWK
Hi! again
I have a case where I have 3 sql database that has no common key to be used and I need to join them either in sql (and create a Star Schema as you have suggested) or in powerbi and incorporate the report refresh from there.
Again appreciate your help
Hi Jeff, you can manipulate columns together by concatenating them so that they create a unique column. For example, country and postcode can be concatenated together to make a unique column for multiple countries. Not sure if that helps but if you can tell me what columns etc you have I can perhaps elaborate.
@@TheBIWizzKid Thanks I get your suggestion :-)
In my case I have 20 columns (same number of field names) for each 3 tables but no common key to paired with and will need to join them into one.
In your video (awesome! video tutorial) you have 1 dataset/source, can I apply this approach in my case that I have 3 tables particularly on adding the "Index Number" and joining them into 1 table?
I was planning to do the same approach initially in SQL and/or then in PowerBI and create the schema
@@jeffrey6124 See if you can get me a snapshot/screen shot of the data and what it is you are trying to join. If you had a date in each table you could create a date table and that could be your connection between the three?
@@TheBIWizzKid I'm all set :-) I did make use of the UNION function and NOW I can proceed with creating the Star Schema .... The BI Wizz Kid way :-) Many thanks!
Great stuff!
Sir, First of all thanks for the video learnt alot.
Secondly If I want to load a New Flat file with same column name...do I need to develop Star Schema again or If we Upload a Flat File with same column name and New Data in it will get automatically updated in the developed star Schema?
Hi Shrikant, If you have new file with the same column names it will automatically update all the star schema dimensions. If there is a new column, or columns with different names, you will need to develop dimensions as per the previous steps. Does that help? Thanks, TBIWK.
Is it best to create a Star Schema in SQL Server before connecting it to PowerBI or connect the SQL Server datasource and create the Star Schema in PowerBI? Appreciate any response :-) Thank you in advance
Hi Jeff, by this I'm assuming you mean the Fact and Dimension tables? If so, then it's better for you to use SQL to create all the relevant fact and DIM tables, and then import them into Power BI to simply connect them up.
@@TheBIWizzKid Yes you are correct and again THANK YOU for the response will work on your recommendation c",)
@@TheBIWizzKid Will creating an SQL view be enough in exchange of creating the Fact/Dimension tables in SQL Server given that there are no common fields including date to reference with and just import them to Power BI and then create the Star Schema?
@@jeffrey6124 Yes you could do that, get the data you need in a single view, import and model into star schema in power bi, jobs a good one!
Great Tutorial! Glad I found it.
Question, Is it possible to create a multiple reports from different Business Units and creating multiple data model since they are not related? I need to combine all the reports from different departments and put it into one. Thanks
Hello Smile, thanks for your comment. I believe the answer is yes if I understand you correctly. You can bring in additional data sources into your Query Editor, and they don't have to relate to any other tables. If they relate to some, great, if not they can be used as stand alone data set for use on another page or in another visual. Does that makes sense? It may not be the most efficient way of doing it but its an option.
Thanks. My case is that I have many excel coming from SharePoint Folder and SharePoint List as a data source. It has a few columns for each excel like date,actual, target and descriptions that's why i didn't create another table except for a date table.
Only a date table are connected to each excel data source. What do you think?
Example:
HR Data (Actual, Target, Date and Description)
Finance Data (Actual, Target, Date and Description)
Supply Chain Data (Actual, Target, Date and Description)
etc
They don't have relationship to each other but I only made one date table and connected to those tables.
@@harlynbugarin6183 That seems fine, the data sets you've mentioned there will be your 'fact' tables as per the star model and the connected Dimension will be the date. How much data do you have coming into the model (ie, 100 rows, 1000, 1M)?
@@TheBIWizzKid Thanks for your quick response. It has thousands of rows since the data is already summarize. And also we do have another model coming from different database should I connect it to date dimension like what other fact tables have? Meaning one pbix file has a multiple model and connected to only one date dimension? As of now I have 40+ report pages in one pbix file.
@@harlynbugarin6183 Wow 40 + pages in a pbix file, that a decent amount! Hehe. So what might be worth considering is Data flows. What this will allow you to do is create a model, similar to the one in the video, but outside a pbix file. So you essentially create a location to store your multiple models and connect to is as a data source. Remember that a report should have a focus and answer/prompt questions. Users may not have the time to view many pages of reports so its also key to see it through the end-user perspective.
Hi, really great video, was looking for this for some time now. Have a question tho. Why create index columns in de DIM tables to create a relation between the DIM table and the Fact table and not just use the specific field value form that DIM tabel to connect with the field in the FACT table. I understand the reason to create a DIM table, but why have ShippingMethode and ShippingID in DIM table, so you can make a relation between DM and Fact based upon ShippingID instead of making a relationship between DIM - ShippingMethode and Fact - ShippingMethode ?
Hi @Erik thanks for your comment. The reason to create an ID column is that Power BI, (and SQL server) perform ALOT quicker when using integer values. So when you reference a number is simple looks for the number that corresponds to that relationship. If you had a text value, that is unique lets say, it must trawl through each and every text value front to end and as you can imagine this take more processing power than just searching for a number right. So although you may not see the difference straight away, when you add more data and more more values to the FACT and DIM tables this will slowly become less efficient over time. Does that make sense? Thanks, TBIWK
@@TheBIWizzKid One more question.
What if you have empty values in a column that you want to convert to a DIM column.
After removing the duplicates from the DIM table, you will have a null value. Do you keep this in the table, or remove it from the DIM table?
Thx again
@@ErikWesseling In this instance what you do is duplicate the column in the DIM, and replace the null value with a value that you want to replace it with, eg, "N/A". Then in your main table, the fact table, search and replace the null values with "N/A". Then join on the new duplicated column and this should handle the null values. Let me know how you get on, thanks, TBIWK
@@TheBIWizzKid Works perfect, thx
Thx for this. Is this the same way of working for non flat files --> e.g. implementingg star schema on mysql data?
Hello, yes you should attempt to create a Star Schema whenever possible, even from non flat files.
Can you please share the dataset
Excellent stuff my friend! Is it possible to download the dataset that you use in the video?
Yes of course, you can find the files here! powerbisupport.co.uk/2020/04/22/season-1-episode-1/
bro do you still have the dataset or the csv bcs i cant access the link anymore please help
@@TheBIWizzKid bro do you still have the dataset or the csv bcs i cant access the link anymore please help
@@jeshennathanel8636 This is the direct download link for the files. Enjoy! powerbisupport.co.uk/wp-content/uploads/2020/06/20-04-22.zip
Great video. Dumb question, but is there a way to automatically apply these steps if I get a new flat file so that I don't have to do all over again?
Hi Kevin, not a dumb question at all. If you get a new flat file, then you simply replace the existing file and 'Refresh' the data in Power BI, it should apply the same steps to that file (given they have the same columns headers). It could be that you mean a completely new file? Let me know.
@@TheBIWizzKid Thanks for that info. I tried and it worked. The only problem I had was that the flat files had index columns and it duplicated all of them. I just added a step to remove dupes on that column and it fixed
@@GodParticleZero Ah I see yes that makes sense and good glad it worked OK. Give us a shout for any other challenges!
First day with Power BI, here it comes a Newbie question. Why didn't you create a Date ID in the Fact table for Order and shipping?
That's a great question and it is something you should do. The session in which I created this data model was a live session with a 20 minute time frame, the date field (and may be the order reference field) are those that were left out. Given another 5 minutes I would have created an ID for those too!
Can you share the link to this csv file??
Hey Aadrash, the files can be downloaded from this link! powerbisupport.co.uk/season-1-episode-1/
Thank! Q! c",)
Please create a video to link SQL Server databases with existing Stored Procedure, Gateways and PowerBI Portal
Hey Thanks Jeff, let me know if you still need this and what it is you're trying to do. Thanks
@@TheBIWizzKid Thank you again for getting back on this and apologies for the late response. Following you previous response I created a single sql view for 3 databases in sql server and connected it to PowerBI .... I will now attempt to do the Star Schema in PowerBI following this great video :-)
Normally I would just enter the Server Name and search for the sql view but this time I did make use of the Advanced options and paste a script for my reporting requirements.
But initially was prompt with an error requiring me to enter a "database name", so I did place 1 of the 3 database name that I was working on and it did run and produce the report I needed :-)
My question is will it make any impact by placing either 1 of the 3 existing database name? while performing this? or it just uses that existing database name to display the script properly since It won't run without placing any existing database name? Below are the steps I performed:
From Get Data > Server > "Database (optional)" > Select Import > Advanced options > SQL Statement > "Script" > OK
Hi @@jeffrey6124, thanks for your comment, and RE your question, can you explain a bit more about the 'impact by placing 1 of 3 databases' sentence?
If you've ran the script successfully in SQL server, note the server name and make that the server you connect to from Power BI. That will ensure the script will run in Power BI.
Adding a database name is fine, one of them will work you may need to try it, but you will need to put the full [database.dbo.tablename] reference in your script.
Have you attempted to refresh your data? If not, try that as it will indicate if your current set up will work.
Come back to me with a bit more detail if you can. Thanks
@@TheBIWizzKidWhen selecting the SQL Server Dbase under Get data initially I would just enter the server name then "Database" field entry is optional but If I would be selecting the Advance option since I have a script to use I would be prompt to enter a database name otherwise the OK button to proceed will be disabled, so I did place an existing Database name and I was able to proceed and got the query I needed in line with the script . Does placing an existing database name on the "Database (optional) field acts only as a trigger to run the script and make a connection to sql server? :-)
@@jeffrey6124 Because security in SQL can be at various levels, Power BI determines this at the connection time and queries are generally ran against a database. So it doesn't just act as a connection trigger it determines your access rights and ensures you can run the script against the database. Does that answer your question?
You did not show flat file. I am sure you created all columns and data in one csv or plain text
Hey, The flat file is the CSV file I used. What I mean by flat file is a data set that is in a single table with no relationships or connected tables. Hope that helps, thanks, TBIWK
Dataset link?
Share excel file
4:55 'remove other columns'
Hi Solo Life, what was your question on the 'remove other columns'? Thanks, TBIWK.
thanks, dude... great video
No problem!
Share excel file