Usercube
When working with Power BI, it is essential to connect to the right data source to obtain the necessary information. Microsoft’s Power Query is an excellent tool for this purpose, and it uses the M language to query and transform data from various sources. In this article, we will discuss how to connect to the Usercube data source using Power Query’s M language code.
Usercube Data Source Overview
Usercube is a powerful identity and access management (IAM) solution that simplifies user management across various applications and platforms. It provides a centralized platform to manage user identities, roles, and access rights, ensuring better security and compliance.
Usercube stores data in different tables, including user tables, role tables, and identity tables. To access this data, we need to connect to the Usercube data source using Power Query M language code.
Connecting to Usercube Data Source
To connect to the Usercube data source, follow these steps:
1. Open Power BI Desktop and click on “Get Data” on the Home tab.
2. Select “SQL Server” and click on “Connect.”
3. In the SQL Server database window, enter the server name, database name, and credentials for the Usercube data source.
4. Once connected, click on the “Advanced options” link and select the “Transform Data” option.
5. This will open the Power Query Editor, where we will write our M language code.
Writing M Language Code
To write the M language code, we need to understand the data structure of the Usercube data source. As mentioned earlier, it consists of tables containing user, role, and identity data.
Let’s say we need to extract all the user data from the Usercube data source. Here’s how to write the M language code:
1. In the Power Query Editor, click on “New Source” and select “Blank Query.”
2. In the “Query Settings” pane, rename the query to “User Data.”
3. Click on “Advanced Editor” to open the M language editor.
4. In the editor, write the following code:
let
Source = Sql.Database(“ServerName”, “DatabaseName”, [Query=”SELECT FROM Users”]),
#”Renamed Columns” = Table.RenameColumns(Source,{{“UserID”, “User ID”}, {“UserName”, “User Name”}, {“Email”, “User Email”}, {“RoleID”, “Role ID”}})
in
#”Renamed Columns”
In this code, we first connect to the Usercube data source using the SQL.Database function, specifying the server name, database name, and the query we want to run (which in this case is “SELECT FROM Users,” which selects all user data).
Next, we rename the column names using Table.RenameColumns to make them more readable.
Conclusion
In conclusion, Power Query M language code is a powerful tool for connecting to various data sources, including the Usercube data source. By following the steps outlined in this article and understanding the data structure of the Usercube data source, you can extract the necessary data and use it to create insightful reports in Power BI.
