Video není dostupné.
Omlouváme se.
Data Analyst Portfolio Project - Clean Data - Power BI & SQL
Vložit
- čas přidán 18. 08. 2024
- Welcome to the third video in my series on How to Build a Data Analyst Portfolio Project from scratch!
In todays video I focus on data cleaning and transformation of tables that we need for dashboard and reports which is part of the data analyst portfolio project. The purpose is just to show you some different types of cleansing and transformations I think are good to showcase and include in a data analyst portfolio project.
Click here to subscribe! bit.ly/3xL8Dm6
Video Content:
0:00 Welcome
0:29 - Fact Table vs Dimension Table
2:29 - Identify necessary tables
5:11 - Data Cleansing & Transformation
23:21 - Wrap Up & Ending
Some of the following tings are exemplified:
- AS Statement (Renaming Columns)
- Combining columns
- Commenting in SQL Script
- Formatting of SQL statements
- WHERE Clause
- ORDER BY
- LEFT JOIN
- Case() Function
- IsNull() Funtion
You can find all the T-SQL statements below and the exported data that was created based on the SQL statements. This is the data we will use to create dashboards and reports for the data analyst project.
Budget File And More: github.com/Ana...
If you want to learn more about FACT and DIMENSION I can recommend this video here:
• What is Dimension and ...
The formatter which I used in the video for the SQL statements can be found here:
codebeautify.o...
A data analyst portfolio project can be a great supplements to a resume when you apply for a job. The resume lists up which skills you have, but the data analyst project can showcase it and be something you can use to your advantage as you are trying to get a job.
Entire series:
#1 - Intro & Setup: • Data Analyst Portfolio...
#2 - Business Request & Planning: • Data Analyst Portfolio...
#3 - Data Clean & Transformation: • Data Analyst Portfolio...
#4 - Create Dashboard: • Data Analyst Portfolio...
#5 - Putting Portfolio Together: • Data Analyst Portfolio...
#6 - Final Result & Portfolio Summary: • Data Analyst Portfolio...
// Ali
________________________
Let’s stay in touch:
📸 Instagram - @iamaliahmadd
🔗 LinkedIn - / aliahmad1987
#DataAnalyst #DataAnalytics #DataAnalysis #Data #Analysis #AliAhmad #PowerBI
I'm an aspiring Data Analyst. This series is really helpful than many online courses. I'm following this project and learning a lot. Keep it going👍. I will share with my friends too. Thanks for your time and effort.
I am really happy to hear this gives you value and you are learning as you go :) Pure motivation for me to think of new series. I try to go into some details without making it too much as the goal is to give an idea of the process A to Z for a portfolio. Wish you all your best with your endeavors as a aspiring data analyst :) If you are willing to do this, you are already ahead of so many others and putting your foot forward in a great way!
And thank you for the support (!)
Are you working as a data analyst now?
I am in my junior year of a data science degree and your videos are helping me build my first project to hopefully get an internship! Keep making content!
Hello Kathryn,
How exciting! It is awesome to see how much more education options out there now. Glad to hear that this helps and I will continue to do my best :)
// Ali
Me too. I am also a junior and my major is Data Science
Thank you for putting together this Data Analyst Portfolio guide. It is definitely very helpful for beginner like me to learn how to complete a project from beginning to end. Your effort is greatly appreciated. Keep up the good work! :)
That is awesome!
I will...one day hah, maybe even put together another one ;)?
Best,
Ali
So informative Project, there is no doubt that your channel will grow in the coming days ...Keep the good work, stay motivated and inspiring
Thank you again for the kind words! I am doing my best, and feedback like this motivates me even more :) Thank you
Underrated channel, it should have more subscribers! Very good contents
Hi Geian! Thanks for the kind words! Hopefully more over time and thanks for supporting!
Thanks for this. Even thought this is two years old it is helping me in ways that paid courses never have. Also appreciate your attention to detail!
Thank you again for the kind words.
Keep up the good work :)
// Ali
I never commented under others' video but urs are so informative and helpful!!! THANK YOU!!! Can't wait for more along my way of looking for jobs!
Hi Yi,
Really happy to hear that it helps and hopefully I can create more videos that help you a long the way :)
// Ali
Thanks so much for this series. It's very helpful to aspiring data analysts like myself!
I am happy that you like it! :)
You are a legend. Such a clear and concise video. You will grow on YT definitely. Thank you for this wonderful work man! Seriously.
Thank you! :) I am happy you enjoy the series!
Proud of you Ali!
This series is really helpful, I have gained valuable insights into how to create my portfolio.
Thank you.
You are incredible Ali, thats pretty much what i can say. Thank you for this great tutorial. Learning alot!!!!
Happy to hear that you are learning a lot from it :) Keep up the good work!
This series is very helpful to those who want to make a leading project in short time frame.
Great thank you Ali
Hi Subhankar!
Thank you :) I am happy that you enjoyed this series and videos.
Best,
Ali
Thank you so much sir your videos are just awesome crisp and clear. keep the work going waiting for more such Data Analyst Projects.
Thank you Avinesh! I am hoping to make another project on API + Power BI or Excel +Power BI, but still in planning.
I'm so excited about this playlist. I love the approach.
Thanks Ali for these resources.
I'm glad to hear that Chibuike 👌🏽😁 makes me happy that it gives you value. Keep me up to date on your progress and thank you for supporting!
it's really helpful, I am following this for my project. Thank you so much for your work!
You're very welcome Brian!
Glad to hear that you are learning from it :)
Great content, the explanations are clear and concise. We don’t mind the length of the video, It’s insightful 😊. Looking forward to the next. 🙌🏽👍🏾
Thanks again Emmanuel 😊
@@aliahmad1987 you welcome🙏🏿
hi ALi, first i wanted to say that i have done different project on youtube an d this is so far the best and realistic project i ever seen Thanks!
would be wonderful if you can make another project would be beneficial for all and for the chanel thanks again man.
Hi!
First of all thank you for the kind words, it means a lot to me!
I am going to make some more next year (I hope!) on Microsoft Fabric, but they take a lot of work and I am so stubborn on the output so I want it to be really good.
None the less, wish you all the best in your data endeavors and thank you for watching!
// Ali
This is Amel from Tunisia, want to be a data analytics consultant and i love ur explanation way
Thank you!
Perfect Ali sir, this is what i was looking for, Thanks for ur time,,
Hi Vik,
You are welcome :)
// Ali
What an excelent content!!
This tutorial is being amazing for me... You're very didatic.
Thank you so much for this material!
Thank you for the kind words!
Thank you so much, sir. It's very helpful for me as beginner
I am happy to hear and all the best :)
Thank you very much for the resources. Its super comprenhnsive, well organized and clair.
Thank you!
Just finished this, do you think I can get away with visualizing in tableau? Also great content. This series so far definitely upgraded my mindset when it comes to reporting and what stakeholders want- also organizing your data with fact/dimension tables is definitely major sauce! And the code beautifier.. i don’t see this in anyone else’s tutorials! So many gems💎 . You deserve a lot more views. I feel way more confident in just downloading a dataset and cleaning and querying on my own accord thanks to you.
Hi Trenth!
Thank you for the kind words and I am really glad you find this useful. The short answer is, yes, of course you can!
Seeing as you are here, let me just offer some thoughts. The fact that you are asking if you can or can not visualize this in Tableau already tells me that you are somewhat misguided, because your impression is that you need to model to satisfy a specific visualization tool, which inherently means that you have misunderstood the purpose of a data model in relation to a visualization tool in a larger scale setting.
You don't need to worry about this now, but it is important to keep in mind that if you model according to a tools strengths and weaknesses, you are more or less learning to model for only one specific tool, and that is not how it should be. You should be learning the concepts and how to apply them irregular of which tool you use to visualize it, which is why when you get a job the real question isn't about which tools you know or don't know, but how can you apply the concepts and adapt depending on what the company has of tools.
The model you are creating here can be used by Qlik, Tableau or Power BI or whatever other tool that can handle a dimensional model.
I am not criticizing, just pointing something out as I want you to learn things the right way ;) Sometimes you have to make a compromise, but I just wanted to try and explain a but as that was your initial question which makes it seem like a yes or no answer, which it isn't.
All the best,
Ali
@@aliahmad1987 You’re right - I should focus on communicating the concepts that I know to interviewers more than the tools I know! I’ll think about that more. Interview time is coming up quick too so thanks for the insight
@@aliahmad1987 Coming back to this I see why it is important to use PowerBi. I get what primary and foreign keys work with dimension and fact tables and this helps me visualize and conceptualize to employers better what I have in my head. Thanks again. Also tried joining my Product keys to internet sales and the keys are different, I wondered why for a while but couldn't get a decent solution. Just thought I'd point it out. But this is definitely a good roadmap to how I should conduct later projects. Thanks again Ali!
Note you will need to Alter Table dbo.DimProduct Alter Column Status from NVARCHAR (7) to (8) in order for IS NULL (Status, 'Outdated") to reflect correctly in query result. 😀💪
Good catch for sure! :)
Your channel is a life saver. Thank you so much
Cheers :)
thank you so much Ali, this video is a life changing. I hope you nothing but the best in dunya & hereafter.
Thank you for your kind words Amir :)
Great tutorial brother! Love from India....Looking forward to more end-to-end projects
More to come Dattatreya and thank you for the kind words :)
// Ali
@@aliahmad1987 I am facing an issue while inserting the slicer. I figured out there is some issue in connecting the data tables of Product Category and Facts table. The values are updating for other slicers but not for category and sub category. I faced an issue while making relations with those tables like one to one, one to many, many to many.
Could you please help it out brother?
No doubt amazing channal for aspiring data analysts
Thank you Muhammad :)! And thank you for supporting!
Good work Ali, a suggestion that may add some more value to the quality of your videos, i hope you consider in your coming videos.
Please zoom in, when you show a script, formula or in general anything you want your audience to focus on. I could not read most of the scripts on my mobile, had to take screenshot and then enlarge.
Hi Salman!
I am aware of this inconvenience! It takes time to edit and I have not prioritized this, but at a later time I will make a course and then I will make all these details edited in.
Thank you for supporting and wish you all the best! Ali
Thank you for these tutorials
Glad you like them!
// Ali
Hi Ali Great content .... Could you also do another video in the same playlist on how will you handle the situation when another business request comes in that does not uses the same data that is already in Power BI .. Like when you need other tables ... do you include that data in the same power bi file or do you create another power bi file ... etc... A video on that situation will be very helpful ...
Also on video if possible on different KPIs and Business terminology used in Sales analysis domain ...
Hi there! Thank you :) I haven't planned to expand on this, I have planned quite a bit other videos first, but eventually I am thinking of doing another portfolio project, but with something else than Sales.
Hi Ali! I am having a problem when exporting the sql tables as a csv file. After I follow your steps and even use your code, after I export as a csv and open it in PBI/Excel, the column names are not showing. I have downloaded your csv files from your github and when I open those in PBI/Excel I am able to see the column names. Do you have any suggestions on how I can fix this problem? P.S: this video series is amazing! Thank you for taking your time to create this guide.
Hi,
Think you need to double check the expor tsettings here ;)
very nice project i really appreciate
but for future video pls use magnifier for writing code that will help us all to understankd the minor parts
Hi Shivasandhu! I am glad you like the project. at this time I don't prioritize editing that much because I want to create quality content. Maybe at a later time I will create a online course, then I will deliver top notch editing all around! Thank you for supporting and hope it wasn't too bad! I appreciate the feedback.
Where did you get the budget table?
Hello there,
They are on the GitHub account!
Best,
Ali
❤️❤️It's the best playlist I found ❤️❤️
Please help me as I am not able to get column of PRODUCT CATEGORY Name in Data base due to which my not able to make proper Power BI report.
Please help as I need to summit my project as soon as possible 🙏🙏
Hi,
Glad you enjoyed it!
I am not sure about that product category though?
Is we you in the next video, great value content 💯👍💯
Thank you 😊😊😊!
Hello Ali, I have some question now this 2019 dataset have only 2005:2014 years in DimDare. Why are the years coming out so small, did they microsoft reduce the dataset?
Hello,
No - it is just not adjusted for the new years!
Best,
Ali
when I download a SalesBudget file. Should I change the year in the date column ( I update the year of Adventure Data to 2024) ?
Yes!
I would recommend to do that to make it more recent in terms of years etc.
Ali, does your role overlap with that of a business analyst? I really enjoyed this video. I just wonder if DA in the US would need to carry out such activities (exp:use case, etc.) or is that more common in Norway/Europe?
Hi,
Yes and no, it depends on the company and the requirement they set, either way all of this is necessary to be successful, how much you spend on different tasks is dependent on the company.
I am not a BA or a DA, I head a team of BI and DWH professionals, with some individual data analyst profiles also in the company.
Best,
Ali
How did you do c.customerkey could you please explain. It is throwing error for me
Hi,
Renaming during SQL statement creation. Nothing special really. Did you figure it out?
Best,
Ali
There are only 4 excel files on your github and their corresponding csv files, I cannot see rest files which u are using in this project
Hi,
All the files should be there. Which one is the one you can't find?
Best,
Ali
Hello Ali, the headers are not being saved in CSV. Could you please help me with this?
Check your export settings ;)
Thanks for the content. Why not do all the cleansing and transformation within PowerBI?
Hi Christian,
Because sometimes you want to use the business logic outside of Power BI and Power BI isn't necessarily the best at transforming data.
Best,
Ali A
Thank you Mr Ali I really like your presentation and explanation, but please I need your help when I added full name to the stacked bar chart is showing blank I tried other charts still the same thing.
Hi,
Hmmm - It sounds like something is off with the measure.
Best,
Ali
Hi Ali,
Firstly, I want to express my gratitude for this helpful series. Then, I would like to ask you a question regarding my confusion. I am unsure whether these SQL statements are primarily used for data transformation rather than data cleansing as we're not explicitly identifying and clearing data issues, if that's the case, should I describe this step on Wordpress as "Data transformation" instead of "Data cleansing and transformation"? I hope to hear from you soon. Thanks in advanced!
Hi,
I would say it is a mix! Is there anything specific you were thinking about?
Hi. I know this is an old video. But I was really inspired by your series. Since I am a budding Business Analyst, I thought this portfolio might aid me. While practicing I found that the Sales Budget xlx file is missing from the repository. Could you help me with it
Hi,
Glad to hear you like it! It is on the GitHub!
Best,
Ali
For some reason my "AdventureWorksDW2019" only has data till 20141231. Any idea why ? How to get the remaining data ? Plz help.
Hi Sourav!
Please not the part where you have to update the data with the date script.
That will take care of this :)
// Ali
@@aliahmad1987 Thank you so much for the help, Sir. I guess I missed the Update Data part from the 1st video in the series.
I just want you to know that your videos are really helpful to the aspiring Data Analysts. Plz keep making them. You are an amazing person. :)
Love from India
Baraka Allah fik my bro 🤍
Thank you! Same toy ou :)
hi ali i think u should create a discussion channel for us to ask doubts clearly and to have help for each other.can u guide me how to copy or download the sql file .
Hi,
I have a discord where some people discuss and help each other, but it is on the GitHub. Does the link not work?
Hii everyone! Can anyone let me know where do i get the salesbudget data please? I might have missed it.
Please check the GitHub, it is there :)
thank you so much for this video. Sir are you there on linkedin I wanted to talk to you.
I am on LinkedIn yes :)
I'm not able to see any data when I use the where clause with >= 2019 but when I remove that statement I am able to see data, can anyone guide me on this. Thank you.
Hi,
Most likely due to the script using todays date to generate some data so as we move further in time (years) you can't use >= 2019 but maybe 2021 or 2022 as values.
Best,
Ali
Where can I get a budget spreadsheet?
It is on his Git, the link is on the description.
Is the last file: Epsiode 3 - Sent Over Data - SalesBudget.xlsx
I do have a question, if we are given multiple csv doc, how do one upload them and do data Modeling, cleaning and analysis. The changes we make on power bi we see them as it goes but for sql we have to run them, it troubles me that i don’t know if you have a series for this instance
Hi,
You would do that either in a datawarehouse and get one combined table at the end (which is in a database most likely). Keep in mind that a .csv is regular source that could be read into a datawarehouse, structured and read into Power BI as one singular source.
Or,
You would read in multiple .csv, you would just have to ensure that they have the same formatting, column names etc. and combine the .csv's into one table and proceed from there.
Your question is sound, but it is overthinking it a bit :)
For a technical example look here:
czcams.com/video/sjibYZ6r6j4/video.html
Best,
Ali
Hi ..ali ..you have done amazing series but do you have any video regarding Postgres sql .
Hello Harpreet!
Why would I :) Concepts are more important than getting too caught up in the technology ;)
In time you will see!
Best,
Ali
Hi Ali, Can you tell where i can find the budget table. You are using it on the next video but i couldnt find under any of your videos
Hi,
Yes it is also on GIt!
Best,
Ali
great Job bro , Just a question , if we need a dynamic data what we have to do ?
Hola,
What do you mean by dynamic data?
Hi Ali, Thank you so much for the video. I', trying to land a job as a Business Analyst and I believe this project as an addition to my portfolio will help me to showcase my skills even better.
I have a question about the Aliases, I can't see where you give aliases to the Dim tables such as DimCustomer and DimGeography. I would really appreciate it if anybody else could also answer my question.
Thanks in advance
Hi,
I am trying to remember, but I think I name the files that? Otherwise you can just do it under the Data Pane or in Power Query (Transformations).
Best of luck in your future endeavors as a Business Analyst. I am rooting for you (!)
Have a great week!
// Ali
This is exactly the same difficulty I'm having that has me stuck and I still don't understand his reply to it.
Hello Ali, thanks for this great tutorial. My CSV file doesn't have column names, do you know what could I did wrong? Thanks!
Hi Yifu,
I am glad you are finding it useful.
Check this out: solutioncenter.apexsql.com/sql-server-management-studio-ssms-how-to-save-results-with-headers/
Best,
Ali A
@@aliahmad1987 It works, thank you! You are awesome, thanks for getting back so fast with solutions!
@@yifucoe3138 No worries 👌🏽👌🏽 keep up the good work!
@@aliahmad1987 Thanks, it worked like a charm!!
Hi Ali, i experienced trouble as i tag along watching. The syntax ''ISNULL (p.status, 'Outdated') AS [Product Status] kept giving me error as what am I not right. i look forward to your feedback
Hi,
I think you need to review the ISNULL statement here :)
Best,
Ali
I have a question on 5:22, how did you restore sql statements from management studio meanwhile how did you create an sql file in the transformation and cleasning folder? Anyone please answer this for me. Thank you so much!
Hi,
I am not sure if I understand this question...
Best,
Ali
Great video, but I keep getting an empty set when I write a WHERE clause >= 2019. I don't see a year bigger than 2008 when I select top 100 rows. I'm pretty sure I have the same dataset as you do...
Hi,
Not sure what you are doing wrong here ;) Did you update the dataset to be for recent years?
Hey i got the same problem - i just ran the Query from Github to update but i get the same problem as @@aliahmad1987
Love the series but is it available without the background music?
No,
Unfortunately not :(
Hello, I need to create a Data Mart from the adventureworks2012 database within 2 weeks. How to create fact and dim tables in SSIS and SQL server? Can you please make an example or educational video?
Hi,
I think you are going to have to solve this one yourself ;) A lot of info on this one out there! Even real examples!
Best,
Ali
@@aliahmad1987 Help me, if I see it once, then I will do it myself. I need this job very much. I pray to you in my prayers.
@@aliahmad1987 Or can you leave a link?
I have a question on 13:42, the Dim Customer table. Why did he use "c.customerkey as [Customer Key]" but not just let it as it is? What "c.xxxx" does? Anyone please answer this for me. Thank you so much!
Is that because of the Space we want between [First Name] instead of [FirstName]?
Hi,
I am simply just creating an alias for the table. Notice in the from statement I wrote "Customer as c" so that means I can refer to columns from customer with ".c" as that is now the alias for that table.
www.w3schools.com/sql/sql_alias.asp
Go down to "Alias for Tables Example"
Hope that helps ;)
// Ali
@@aliahmad1987 Oh I got it. Thank you so much!
Thank you love you 😘
Glad you enjoyed it! :)
Hello thanks for amazing video's. I want to ask question
As a PowerBI developer we need to learn detailed SQL ? Means all complex stored proc bla bla coz acc to me this is SQL developer role? How much detailed we need to learn SQL ? Please?
Hello,
I would say knowing SQL well is key yes for this role.
Best,
Ali
@@aliahmad1987 Thanks for replying sir. Yes I know SQL server in detailed. All command, function,stored procedure l, triggers,dwh concept but I have done one online course of SQL server in that I have learnt a lot. One Bank project also we have completed. In this project we create database, create triggers, functions ,stored procedures also. In this project we write lot of complex stored procedure having parameters (example user insert atm card and enter pin and pass this parameter)
This are very complex and sincerely i not understand many of them. So data analyst required this detailed SQL ? They really need to write such complex procedure? Our goal is to analyse data so why this ? If we write such complex procedure and what SQL developer do ? According to me this is SQL developer role? Please explain in simple.
I know this is an older video, but I had one little question. When I was at the ISNULL() section, I noticed my NULL's were turned to 'Outdate' and not 'Outdated'. I then noticed on this video the same. I tried a little test and instead of 'Outdated', I put in '12345678'...and the result stopped at 7. I am somewhat new at SQL. I was wondering why it was stopping at 7 characters.
Hi,
My first guess is maybe the type set on the column, but I can't remember this all the way back hah. You can use something else also instead out of "Outdated" if you want?
because character length was set to 7, need to change to 8
use alter command to change the string length
alter table DimProduct
alter column Status
nvarchar(8)
Where can I get this dataset so that I can practice for myself ?? Thank you in advance
Check Part 1 :)
// Ali
I need to know how data analyst is given access to the data to write sql wehther are they given access to live database or is given access to views by IT dept?
It depends on your company and governance, but not that often you get access to the live DB, but a DWH or View could be given by IT or Servicedesk, yes.
Hi Ali , Loved your video and have one doubt . As this Microsoft sample dataset . did you write this query by yourself. I am sorry as i couldn't catch up with you at some place . If in real world i want to write query how can i approach that . Please help . Have a good day Ali.
Hi,
Yes I did write this query myself. To do these types of queries you'd have to look into SQL and just start practicing combining different queries.
Best,
Ali
Where can I get the Sales Budget excel file?
Hi,
It is on the GitHub!
Best,
Ali
Hi ali thanks for such a making great video i have a doubt in real time should we work on warehouse database or oltp could you pls help me on that and pls help me on creating views
Hi,
It depends on the company Sudhir how much ownership you get of this, but this will come with time.
Best,
Ali
Hello! I couldn't thank you enough for this valuable and inspiring videos :)
I'm following your instruction step by step but I got the different result when cleansing the DIM_Date table. When I make a query SELECT, FROM, everything worked well. When I state WHERE CalendarYear >= 2019, it stated that query executed successfully but there is no result showing. Any suggestion?
Hmmm,
Bit hard to say without seeing it, but maybe it is the type on the column isn't being picked up as a integer?
@@aliahmad1987 It's because I miss updating data as you suggested in the 1st video. Whoops! Looking forward to your next video
all is good but can not see clearly what you are written.
Written where :)?
Shouldn't we only use data where CurrencyKey = 100 (USD) or convert the other currencyKeys to USD? Otherwise the SalesAmount is inaccurate to viz and use (Unless the SalesAmount already defaults to USD)
Hi! You are actually totally right! The purpose of the video & series is just to show an example, and I didn't go that into depth on what all the keys related to the fact table was. I thought it was defaulted to USD, and the Currency Key was a key meant to connect to an exchange table and from there you could calculate over to another currency.
If it is important to you that it is only USD then you can add the WHERE clause or calculate over.
The visualization itself isn't inaccurate, my interpretation of Currency Key is inaccurate (which is my mistake). Either way I think the example works fine with or without changing it.
I appreciate the attention to detail and you pointing it out :)
@@aliahmad1987 Thanks for the quick reply and great content! Any plans on expanding this series with intermediate concepts/projects for a portfolio such as a full ETL process or SCDs? Seems like the portfolio would be the main talking point for those who are self studying and has no relevant work experience such as myself. Thanks again Ali!
@@Innovation-Dev No worries and thank you for that! I don't think so as that is closer to data engineering. I might do something which touches on SCD2, incremental load and creating fact and dimension tables, but then it would be closer to data engineering. If you can handle those kind of topics I'd say you've gotten quite far. What is your goal?
@@aliahmad1987 Just trying to get an entry level data analyst job! I cant handle those type of topics, but people on reddit recommended them as projects for some reason on /r/buisnessintelligence. Actually a good idea might be popular datasets for us to do projects on as thats something im struggling to find!
@@Innovation-Dev I see. If you got that recommended on a BI forum, then I understand why. It is more towards the data engineering side I would say, but if you can understand them and talk around them it will make you a more *qualified* data analyst when you are asking for integrations or new data, which is always great for speed of delivery. I am making a video soon on where to find good datasets! Also looking into a video which will show different data analyst workflows depending on company architecture!
Wouldn't it be faster to do all the transformations and joins in Power BI, instead of playing in SSMS ?
Hi Matt,
It's an interesting question and the fact that you are asking it tells me you are thinking ahead of the curve.
Let me offer some of my thoughts :) Keep in mind that my job entails thinking from an enterprise mindset.
What are the purpose of the transformations and joins? Are they made for wide spread usage or only a one off analysis that only you benefit, or could it be pushed further back into the process so that more could re-use what you have created? Then you could turn it into a view and instead of 1 person getting value (as all transformations and joins are in a .pbix file vs. a layer in the data where it is available to everyone).
Another point worth making is what is the amount data? Do you think SQL server or Power BI locally (on your laptop) handles that better?
I can continue with other points in terms of governance etc. I think what you should ask yourself do you want to create great analysis and be an enabler, or create great analysis and be a data silo. Other factors are your data access rights, purpose of the analysis etc.
What are your thoughts?
@@aliahmad1987 You work with Sql server data in Power BI via connection only, so the actual data amount should't matter. Also, I'm curious about your thoughts about learning python for data science. Does it still make sense when you have all those AutoML libraries available (Pycaret, H20, Pandas-UI) ?
@@mattmatt245 It definitely matters ;) Try it out! And one of the most important parts for a company is governance and aligned metrics, so if you can do that in an ETL layer before, that carries great benefits.
Uhm, as we get more self-serviced alternatives it will probably face some heavy competition from those kind of services.
People tend to forget that you are not only competing with only other that are learning the DS area, but also againts Moores law and technology to make it easier to perform.
None the less, the UNDERSTANDING of it will still be very important. You have to be able to explain to someone what the software is doing, if you can do that then you are bueno and you have to do less coding.
It baffles me how much people underestimate the value of presentation skills, but everything comes with experience and real-life challenges.
Cheers for some solid questions Matt!
subscribed Ali vi
Thank you and welcome! :)
I didn't got 2019 data from date table
Hi Muhammed,
Not sure if I fully understand?
I am also facing same problem but I have solve this , for this you go to 1st video of tutorial and implement last part of the topic , you will get you result
I couldn't find any data after 2019
There isn't any after :)
Sales DataMart
Are you hiring?
Hello,
No - unfortunately not :(
not a good quality video
Still appreciate you :)
First of all, I need to say that ur videos are very helpful thanks for everything, and very helpful in uploading the right queries on GitHub to check I hope I can be like you one day in analytics Ali! I will follow all ur videos and the way you handle your tasks. I hope we can be friends too any time you come to Egypt :D. So keep making powerful series like these. Thanks!
Hello Moaz!
Thank you very much for these kind words :) I have been to Egypt once. It was truly an amazing and beautiful country!
Best,
Ali
c.customerkey -- How is this come ?? I am getting error for this "not found" error, can you help me ..
Hi,
Examine the SQL a bit more and do some testing, I am confident you will figure it out :)
Best,
Ali
when I try to load the DIM_product table script I keep getting an error that says: Msg 102, Level 15, State 1, Line 46
Incorrect syntax near 'ascp'.
Completion time: 2022-08-25T01:27:33.5007258-04:00
over these two lines
LEFT JOIN dbo.DimProductSubcategory AS ps ON ps.ProductSubcategoryKey = p.ProductSubcategoryKey
LEFT JOIN dbo.DimProductCategory AS pc ON ps.ProductCategoryKey = pc.ProductCategoryKey is there a possible fix? When I load the data into PowerBI as well it shows the unedited data without cutting out any of the columns.
Hi,
It sounds like a typo somewhere...hmmmm did you find it?
@@aliahmad1987 Yes there was a typo, thanks a lot
Hey guys,can someome help me when i type the code i seem to have a problem with g.city AS......
Did you find out?
@@aliahmad1987 Yes,i watched the video twice and realized the mistake
When I am running file-
SELECT
c.customerkey AS CustomerKey,
-- ,[GeographyKey]
-- ,[CustomerAlternateKey]
-- ,[Title]
c.firstname AS [First Name],
-- ,[MiddleName]
c.lastname AS [Last Name],
c.firstname + ' ' + lastname AS [Full Name],
-- Combined First and Last Name
-- ,[NameStyle]
-- ,[BirthDate]
-- ,[MaritalStatus]
-- ,[Suffix]
CASE c.gender WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' END AS Gender,
-- ,[EmailAddress]
-- ,[YearlyIncome]
-- ,[TotalChildren]
-- ,[NumberChildrenAtHome]
-- ,[EnglishEducation]
-- ,[SpanishEducation]
-- ,[FrenchEducation]
-- ,[EnglishOccupation]
-- ,[SpanishOccupation]
-- ,[FrenchOccupation]
-- ,[HouseOwnerFlag]
-- ,[NumberCarsOwned]
-- ,[AddressLine1]
-- ,[AddressLine2]
-- ,[Phone]
c.datefirstpurchase AS DateFirstPurchase,
-- ,[CommuteDistance]
g.city AS [Customer City] -- Joined in Customer City from Geography Table
FROM
[AdventureWorksDW2019].[dbo].[DimCustomer] as c
LEFT JOIN dbo.dimgeography AS g ON g.geographykey = c.geographykey
ORDER BY
CustomerKey ASC -- Ordered List by CustomerKey
Getting error-
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.dimgeography'.
However, I copy pasted the same code from Github page.
Hello,
For you to learn I recommend to use the code as a starting point, and then create this code yourself. Copy paste will teach you very little...
The error from what I can see is your table name?
Best,
Ali
@@aliahmad1987 Thanks a lot Ali. Going through this project series is really very enriching experience. I'm pursuing an SQl course on Udemy and have completed CRUD operations so far. Request you to kindly make more Project videos as it will help people like me who want to Break into Data Analysis field.
regards
Syed