Creation translation system of labels with Power BI

In an Power BI Desktop data models, you can’t do multiple translations of a caption to provide a culture-specific. Today we will see For how perform translations Power BI models.
A translation consists of a translated caption for the name of fields in the table a binding to a column that provides data values in the target language. You can have multiple translations. There is no theoretical limit on the number of translations you can embed in model.
To perform this using:

In this post we get data from three csv file: product, sales territory  and sales from AdventureWorks. We will create a model in the file model.xlsx and we will create the dictionary in the Dictionary.xlsx. See below the file in the folder

So, we have a series of label, and want to look them up in this table (lookup table) in the dictionary.xlsx with two columns one for it-IT label and one for en-US label, see below

Now we will import into Power Query into model.xlsx file then territory’s lookup table

Now we will import into Power Query into model.xlsx file then product’s table from SalesTerritory.csv file. The objective is change the label with a correct translation for examples SalesTerritoryRegion change in Region or in Italian language “Regione“. After importing it we will can observed in power query the following structure:

Now we will create a query for perform the translation. The M formulas is:

let
    Source = Csv.Document(File.Contents(PathFile & "\SalesTerritory.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Removed Columns2" = Table.RemoveColumns(Source,{"Column2", "Column6"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns2",{{"Column3", "Column2"}, {"Column4", "Column3"}, {"Column5", "Column4"}}),
    #"Kept First Rows" = Table.FirstN(#"Renamed Columns",1),
    #"Removed Top Rows" = Table.Skip(#"Renamed Columns",1),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Column1", type number}}),
    #"Transposed Table" = Table.Transpose(#"Kept First Rows"),
    #"Invoked Custom Function" = Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel([Column1], lbTerritory, 2, false)),
    #"Removed Columns" = Table.RemoveColumns(#"Invoked Custom Function",{"Column1"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns"),
    #"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"}),
    #"Promoted Headers" = Table.PromoteHeaders( #"Table Combine", [PromoteAllScalars=true])
in
    #"Promoted Headers"

Begin analyzing the formula from row 4 and 5. In this two row set two variables “Kept First Rows” e “Removed Top Rows”  the first contains the first line where there are labels that I have to replace

the second contains the all line that they will need later

The First result (“Kept First Rows“) is now ready to be transposed using the Table.Transpose() function

Here is the key step where the call the lkpLabel  function for create a colum with the new translate label. This function use 4 parameters:

    #"Invoked Custom Function" = Table.AddColumn(#"Transposed Table", "lkpLabel", each lkpLabel([Column1], lbTerritory, 2, false))
  • Lookup_value  => the value of the column which must be converted ([Column1])
  • Table array => table array is the lookup table (lbTerritory)
  • column = >number of column for lookup
  • approximate_match => it’s optional

and see bleow the new column (lkpLabel):

Once we got the new value, transpose the table again to restore the headers as a row


So now remains to connect the new header with the body of the table that contains all rows and that we previously saved to a variable “Removed Top Rows” . The table with the single header row needs to be combined with the original table with Table.Combine function

    #"Table Combine" = Table.Combine({#"Transposed Table1",#"Removed Top Rows"})

Once the tables are combined, you can see the altered headers to promote the first row to headers using the Table.PromoteHeaders() function.

   #"Promoted Headers" = Table.PromoteHeaders( #"Table Combine", [PromoteAllScalars=true])


you can download the folder with all of the demo file from this link.

Optimizing DAX

SQL Saturday 589 Interview

Tweet SQL Saturday 589 with Power BI

Basic Data Modelling with DAX by Marco Pozzan on Parma’s Univeristy

Power BI: basic data modelling with DAX by Marco Pozzan from UGISS on Vimeo.

Cloud Conference 2016 My video on Power BI + Flow

My new video on Dax Fundamentals

SSRS Branding File Editor

A new feature of branding has been introduced for SSRS in SQL Server 2016. This feature is useful for customizing the SSRS Report Manager Portal with Display colors and a logo also can be added to the portal.

The tool is aviable on http://ssrsbrandingfileeditor.codeplex.com/

Performance Issues with Number of Virtual Log Files In SQL Server

Overview

Each transaction log file is made of smaller parts, known as virtual log files. The number of virtual log files per transaction log files is not fixed. Its size is determined dynamically when the transaction log file is executed. The number of virtual log files cannot be set or configured by the database administrator. If the auto-growth settings are not properly managed then, database can be forced to auto-grow that can cause serious performance issues. In the following section, we will discuss the causes and number of virtual log files can exist.

Impact of VLF on SQL Server Performance

In SQL Server, Transaction log files are set at 2MB at initial size. In addition, 10% of current size is the default growth value. At the time of creating SQL Server database, these options can be modified to accommodate the needs of database. The auto-growth option is optional which is turned on by default. SQL Server creates a database with unrestricted file growth. If the settings are not properly managed, then it will create an issue. Until, the auto-growth is finished, the server will stop all the processing. The auto-growth will take up the space that is not physically close to previous one due to physical organization of hard drive. It leads to physical fragmentation that causes the slower response in performance.

The Server should not have an excessive number of virtual log file inside the translation log. Large number of small virtual log file slows down the process of recovery, which database goes through on startup otherwise after restoring a backup. The threshold for significantly affecting the recovery performance appears to be around ten thousand virtual log files. When there are about hundred thousand virtual log files then, symptoms become significantly noticed.

Tips to Fix this Perfomance Issue:

  • One can determine the no. of VLFs in specific database by checking the no. of records that are returned as resulted of the executed DBCC command within the text by using DBCC LOGINFO
  • The number of virtual log files can be decreased by running the DBCC SHRINKFILE command.

Conclusion

In the discussion, performance issue with large number of Virtual Log Files in SQL Server Transaction is discussed. It provides guidance for users to have proper understanding about the virtual log file impact on SQL Server that results in slow down of the performance of the server.

Proficient Way For Giving and Removing Permissions in SQL Server

Overview

While working with SQL Server Database, users has the right to assign or remove permission, to allow which user can perform particular tasks on the database. Due to security reasons, only some of the default database users such as db_owner, db_datawriter, etc. are granted access to the database. However, some database needs to be accessed by other users at times. For this purpose, some commands can be used to grant or deny access in SQL Server. Through this page, we will learn the technique for giving and removing permissions in SQL Server.

SQL Server Commands

There are many commands supported by SQL Server, however in this case we will define only the three commands that can be used to give and remove access to the SQL Server users. They are as follows:-

i. GRANT– It is used to give users permission to perform some tasks on the SQL Server Database objects.

ii. DENY– It is used to deny any access to an user from performing certain tasks on database objects.

iii. REVOKE– It is used to remove grant or deny permission from the user that was earlier assigned to perform any tasks on the SQL database objects.

Giving Permissions in SQL Server

As stated in the previous paragraph, for giving permissions we will use GRANT command. The following syntax will be used for granting privileges on a table in SQL Server database:-

GRANT privileges ON object TO user;

Privileges: Some privileges that can be assigned to the user are as follows:

  1. SELECT Able to perform SELECT statements on table.
  2. INSERT Able to use INSERT statements on table
  3. UPDATE Able to use UPDATE statement on table
  4. DELETE Ability to perform DELETE operation on the table
  5. ALTERTo perform ALTER TABLE statements to modify table definition

Object: It denotes the name of the database object that needs to be given access. For example, if we want to grant access on a SQL Server table, object will be the table name.

User: It will define the name of the user to which we need to grant access over the object.

Some Examples:

  • GRANT INSERT, UPDATE ON Students TO John, Jessica; [Here, the command will allow John and Jessica to insert or update data in table Students]
  • GRANT SELECT ON Students TO public; [Here, we will grant only SELECT access on the Students Table to all users by giving access to public]

Removing Permissions in SQL Server

Similarly, for removing some or all permissions on a table in SQL Server database we will use REVOKE command on some or all the privileges. Privileges can be combination of SELECT, INSERT, UPDATE, DELETE or ALL. The following syntax will be used for revoking privileges on the SQL Server Table:

REVOKE privileges ON object FROM User;

Here, Privileges can be any of the privileges discussed earlier in GRANT section. Object will the name of the database object from where we are removing permissions. User will be the name of the users whose permissions that was already assigned will be removed.

Some Examples:

  • REVOKE DELETE ON Students FROM Alex; [Here, User Alex has been removed permission to perform delete operation on Students table]
  • REVOKE ALL on Students FROM Maria; [Here, it is used to remove ALL permissions i.e., SELECT, INSERT, UPDATE, DELETE & REFERENCES from Maria User in Students Table]

Denying Permissions in SQL Server

A DENY command can be used to deny the DELETE any access or privileges on a SQL Server database object.

Example: DENY UPDATE ON Students to Oliver; [Here, the statement will deny the UPDATE operation on Students Table by user Oliver.]

Conclusion

The content has been aimed to guide users of SQL Server database in giving or removing permissions in SQL Server database objects using some commands. Additionally, we can grant users permissions not only on objects, but also on other tasks like creating tables, views or stored procedures. Using these commands, we can give access to perform tasks on database by the desired users instead of using the default database roles.

Switch to our mobile site