Data Discoveries - Analyzing Formula 1 Race data using SQL Server and Power BI (How to)

[@ins0mniacwastaken]

Could you guide me how you converted your data type from nvarchar(max) and used it for production. Without this step its not displaying all the data from 1950-2020. Any help would be appreciated. Thank you.

[@SQLDevDBA]

Hey there, of course! You’ll need to first identify the correct data types for each column. Then you’ll need to run an alter statement on the table. ALTER TABLE [tablenamehere] ALTER COLUMN [columnnamehere] VARCHAR(30) Replacing the VARCHAR(30) with the datatype you decide for that column. learn.microsoft.com/en-us/sql/relational-databases/tables/modify-columns-database-engine?view=sql-server-ver16

[@SQLDevDBA]

Also, you may be able to take a shortcut by casting the year as an int WHERE CAST(Raceyear AS INT) > 1990 Or in an order by ORDER BY CAST(RaceYear AS INT) DESCRIBE

[@ins0mniacwastaken]

@@SQLDevDBA Hey! I shall implement these changes thank you for the support! the main error i recieved was "CREATE INDEX statement failed. Column 'Column0' has a data type that cannot participate in a columnstore index. Omit column 'Column0'." for the command "CREATE CLUSTERED COLUMNSTORE INDEX [IX_race_results_1950-2020_CCS] ON DBO.[race_results_1950-2020];" Thank you for the help!

[@SQLDevDBA]

@@ins0mniacwastaken no worries, glad I could help! Honestly a Clustered column store index is not 100% necessary, it’s just a shortcut. Brent Ozar has a site called columnscore.com which helps you determine whether it’s needed. You can absolutely sub with another index.

[@ins0mniacwastaken]

@@SQLDevDBA Thank you so much! Really appreciate all the help I received. Cheers!

[@siddhantjain490]

import dbaCsv statement not working for me. Its my first time using it can you help me?

[@SQLDevDBA]

Hi there, you'll want to use the Guide on DBATools.io to get started with it. You'll need to install via PowerShell ISE once, then you'll be good to go. dbatools.io/download/ I would also recommend this book, "DBATools in a month of lunches," as it's a great guide: www.manning.com/books/learn-dbatools-in-a-month-of-lunches

[@siddhantjain490]

@@SQLDevDBA i downloaded dbatools but getting trust certificate issue. I'm trying to resolve the problem but it's not working

[@SQLDevDBA]

Hey @siddhantjain490. I want to say you’ll need to make sure you use the -InstallationPolicy Trusted command that is identified in the “Method 2” section of the downloads page. Also you’ll want to click “Yes to All” when you’re prompted to review/accept the certificates. Unfortunately I’m not tied to DBATools in any way (I just really like it) but they are quite helpful and may be able to help you diagnose the install if you contact them: dbatools.io/contact/

[@siddhantjain490]

Hey, thanks for the reply and this video was really helpful.

[@SQLDevDBA]

@@siddhantjain490 so happy to hear, thank you very much!