The 1nf0rmant

Calendar Table In this post we will describe how can we create a calendar table on Vertica db using Timeseries analytics. TIMESERIES Clause Provides gap-filling and interpolation (GFI) computation, an important component of time series analytics computation. More information you can find on  timeseries info . Our query consists of 3 parts in order to have all the necessary fields. CREATE TABLE IF NOT EXISTS calendar AS 👉    SELECT ROW_NUMBER() over(partition by 1) AS id   , tmp.* FROM ( 👉     SELECT  slice_time                                                   AS  actual_timestamp,                      CAST(slice_time AS DATE)                      ...

#shared Say (say, say), say the magic word SHARED  - S.H.A.R.E.D.  In Power BI , there is Power Query M which is functional language. Knowing as many as possible functions you can, it's the best assistant when you have to do something writing M language. In this post we wil l talk about the shared keyword.  There are many times that you need to go the  manual of M language    and search for the syntax of a function.  👉What if you have the entire manual in a single query, inside the Power BI in order to read the syntax and test the function ? Open Power Query editor, go to New Source and choose Blank Query . Then go to query, right-click and choose Advanced Editor .            At the advanced editor pane,  delete all content here and only type in: #shared ,  and then press done. The r esult set here is a query that has functions, enumerators, and queries in each item of the record.      ...

SQL Server Management Studio  shortcuts At the following video you can see some shortcuts for writing faster and more efficient SQL queries, at Synapse or SQL Database. Environment  SQL Server  2019 Developer Edition. SQL Server Management Studio v18.10 You can download the client from the following 🔗    SQL Server Management Studio   🠟 .

 Presentat ion of  Azure Analytics  Components In the following video you can see a "5-Minute" presentation about Azure platform and a few words about analytics components of Azure platfrom such as: Azure Data Factory Azure Analysis Service Azure Data Lake Analytics Power BI

  Week By Week Report Analysis If you are a Data Warehouse Analyst, it might be a request from your manager, director or CEO to do a report to see the growth of a KPI during the time. How KPI “X” evolve during Years, Months, Dates , etc. The tricky part comes when you must do a week-by-week analysis. Let’s see below the steps that must be performed by Data Warehouse Analyst. Environment  SQL Server  2019 Developer Edition. SQL Server Management Studio v18.10 Database: DWREAD Schema: FORMULA_STAG Table:  RACES The table is from Kuggle   Formula 1 World Championship (1950 - 2020)   👈 Steps We go to MS SQL Database and execute the following statement: SELECT @@DATEFIRST; This statement shows us that the first day of the week is Sunday (default).  So, we understand that the week is [Sunday, Monday, …, Saturday].  All the dates between Sunday and Saturday is grouped into one week. Our table has a date column: date If we want to identify in which ...

Catalog Views Catalog views contains info for metadata for each user table. Catalog views belong to the sys schema, so you have to use the schema name when you access one  of the objects.  This section describes some of the most important catalog views. Applies to : SQL Server, Azure SQL Database, Azure Synapse Analytics  Views sys.schemas Contains info for each database schema. Columns: name: Name of the schema. Unique within the database schema_id(int): ID number of the schema. Unique within the database sys.tables Contains info for each user-table in SQL server. Columns: name: Table Name object_id(int): Object id number. Is a unique number for each table in the database. schema_id(int): ID number to identify which schema the table belongs.   sys.columns Contains info for each column of a table. Columns: object_id(int): ID  number to identify which table this column belongs. system_type_id(tinyint): ID of the system type of the c...