Góc thư viện kiến thức sưu tầm

  Problem How can we create a date dimension table in Power BI? We need data such as month name, year, financial quarter, etc.  Also, how can we access this data directly with DAX? Solution A date dimension is an integral part of a data warehouse. A date dimension will have a range of dates with attributes such as Month Name, Year, Financial Quarter, Financial Semester and Financial Year. In this tip, I will detail a method to create a Date Dimension in Power BI. Solution Overview In this approach, I will make use of a Power BI calculated table to generate date values. Also I will be adding attributes such as Month Name, Financial Year, Financial Semester and Financial Quarter with the help of DAX. Calculated Tables In a calculated table, the table values are generated by Data Analysis Expression (DAX) and the values are stored in the Power BI model. DAX Calendar Function The calendar function returns a table with a single column that contains a continuous set of dates. The st...

 IF OBJECT_ID('GenerateTriggers','P') IS NOT NULL        DROP PROC GenerateTriggers    GO        CREATE PROC GenerateTriggers     @Schemaname Sysname = 'dbo'    ,@Tablename  Sysname    ,@GenerateScriptOnly    bit = 1   ,@ForceDropAuditTable   bit = 0   ,@IgnoreExistingColumnMismatch   bit = 0   ,@DontAuditforUsers NVARCHAR(4000) =  '' ,@DontAuditforColumns NVARCHAR(4000) =  '' AS        SET NOCOUNT ON        /*    Parameters    @Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'.    @Tablename            - TableName for which the procs needs to be generated.    @GenerateScriptOnly - When passed 1 , this will generate the scripts alone..    ...

  Audit is always a kind of standard requirement in all the trasactional system. For auditing a particular table ,we need to create a similar structure audit table and create Insert , Update and Delete triggers to keep track of the changes in the table. This is utility procedure will create below objects in the database of the passed table. 1. Creates an audit table with name <Tablename>_audit. It has some additional fields to capture the changes on the table                   AuditDataState   -- Stores the data state whether "New" or "Old"                   AuditDMLAction   -- Stores the DML Action like "Insert","Update","Delete"                   AuditUser        ...