Query Voyage

  What are Flat Files?     Flat files are common type of data source that can be used for ETL or extract, load, and transform in Power BI. Examples of flat files include CSV, TXT, and Microsoft Excel files.     In Power BI, datasets and data sources are important terms and you must understand the differences between the two. A dataset is what you get when you use the Get Data feature to bring in data from a file, template app, or live source. It includes information about the data source, credentials, and a portion of the data itself. When you create reports and dashboards, you review the data from the dataset.     However, the data in dataset comes from a data source. A data source can be an online service, a cloud database, or a local file or server. So, to put it simply, a dataset is like a container that holds some of the data from a data source. The data source where the data comes from. An example of a data source could be a cloud database like A...

 The data analysis process 1- Prepare the data 2- Model 3- Analyze - Descriptive Analysis: Describe what the data looks like in its basic form. - Exploratory Analysis: Dig deeper to try and find interesting patterns or relationships between different parts of the data. - Inferential Analysis: Use available data to make guesses or predictons about things outside the data. - Predictive Analysis: Use statistics to predict what might happen in the future based on what's happened in the past. 4- Visualize 5- Manage Gathering the right data 1- Identify the Analysis Purpose for example:  Drive product development 2- Type of Data for example: Market trends data                                Competitor data                                Sales Data 3- Scope of Data For example: Dates: last 5 years         ...

 Text Functions   LEFT,RIGHT AND MID: These functions help extract specific parts of text from a cell . For instance, if you have a dataset with full names in a single column, you can use these functions to separate first and last names into different columns. TRIM: This function removes extra spaces from a text string. UPPER,LOWER AND PROPER: These functions convert text to uppercase, lowercase, or proper case(proper case being where the first letter is capitalized, and the rest are in lowercase). This can be helpful when dealing with data that has inconsistent capitalization. Lookup Functions Lookup functions help you find and retrieve data from other parts of the worksheet or other worksheets based on specific criteria.  VLOOKUP AND HLOOKUP: These functions search for a value in the first column or row of a table and return a corresponding value from another column or row. They're useful for merging data from multiple sources or filling in missing data. INDEX AND MAT...

 What is a stored procedure?    -> A set of of SQL statements stored and executed on the database server. Benefits of stored procedures : 1- Reduction in network traffic 2- Improvement in performance 3- Reuse of code 4- Increase in security How to work with stored procedures: Call from: a) Dynamic SQL statements b) External applications For example: CALL UPDATE_SAL("E1001", 1)