columns Archives - Business Intelligence Info

Tag Archives: columns

Hello, I want to create a matrix with a certain number of rows and columns

December 6, 2020 BI News and Info

Recent Questions – Mathematica Stack Exchange

Finding all audit enabled tables and columns for your Common Data Service environment

November 17, 2020 Microsoft Dynamics CRM

As a best practice in maintaining your Common Data Service environments, it is important to review your audit configurations at the table and column levels to ensure you are only auditing records that are necessary. This will prevent excessive growth of your audit partitions. If you are wondering what I mean by table and columns, see this post.

The problem is easily identifying each and every column within every table. Here is how you do it.

Using the webAPI, create the following query:

https://orgname.crm.dynamics.com/api/data/v9.1/EntityDefinitions?$ select=LogicalName,IsAuditEnabled&$ filter=IsAuditEnabled/Value%20eq%20true&$ expand=Attributes($ select=LogicalName,IsAuditEnabled;$ filter=IsAuditEnabled/Value%20eq%20true)

If you run this in your browser, it will return a list of tables and columns that are enabled for auditing in JSON format. Now, you need an easy way to read and analyze the data.

Let’s see how we can do this with Excel.

First, open a new blank workbook in Excel and select the Data tab

Click Get Data | From Online Services | From Dynamics 365 (online)

0336.pastedimage1605292203930v1 Finding all audit enabled tables and columns for your Common Data Service environment

Copy and paste your webAPI query from above and click OK

2402.pastedimage1605292386700v2 Finding all audit enabled tables and columns for your Common Data Service environment

Click Transform Data

5226.pastedimage1605292426512v3 Finding all audit enabled tables and columns for your Common Data Service environment

Click the expand button next to Attributes

0028.pastedimage1605292495188v4 Finding all audit enabled tables and columns for your Common Data Service environment

Uncheck IsAuditEnabled and click OK

4743.pastedimage1605292531049v5 Finding all audit enabled tables and columns for your Common Data Service environment

Finally, click Close & Load

7002.pastedimage1605292551484v6 Finding all audit enabled tables and columns for your Common Data Service environment

You can do the same thing in Power BI. Check out below:

Open a new Power BI report and select Get Data and click More

6087.pastedimage1605292797714v7 Finding all audit enabled tables and columns for your Common Data Service environment

Then, select Online Services and Dynamics 365 (online)

1881.pastedimage1605292831301v8 Finding all audit enabled tables and columns for your Common Data Service environment

Click Connect and paste your webAPI query into the window

1055.pastedimage1605292874686v9 Finding all audit enabled tables and columns for your Common Data Service environment

Click Transform Data

5482.pastedimage1605292901900v10 Finding all audit enabled tables and columns for your Common Data Service environment

Click the expansion next to Attributes

5123.pastedimage1605292940439v11 Finding all audit enabled tables and columns for your Common Data Service environment

Uncheck IsAuditEnabled

0550.pastedimage1605292957861v12 Finding all audit enabled tables and columns for your Common Data Service environment

Click Close & Apply

4452.pastedimage1605292974719v13 Finding all audit enabled tables and columns for your Common Data Service environment

Once it refreshes, you will have a full list of tables and columns for your review.

I hope this helps!

Thank you for reading!

Aaron Richards

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Figure out which columns and measures are used in your SSAS Tabular model

October 22, 2019 Self-Service BI

I got an interesting question about being able to figure out which measures and columns are being used for all tabular models in a server, either by known reports or by self service users using Power BI. To solve this I decided to use Power BI :).

To get the required information I decided to capture and parse the queries being that are being send to AAS and parse the results Power BI desktop over a period of time. In this post I describe how to do it.

Start capturing data

First I started capturing the queries in AAS using Azure Log analytics as I described here. This can also be done using profiler or XEvents as well but I decided to be modern :).

In log analytics I can see all the queries that get send:

Figure out which columns and measures are used in your SSAS Tabular model

For my analysis I don’t need all the columns and events, so let’s filter that down with log analytics to something like this:

AzureDiagnostics
| where OperationName == “QueryBegin”
| project TimeGenerated, TextData_s, DatabaseName_s
| where TimeGenerated > ago(30d)

You can change the time range to any range you want to whatever max range you have data for. You need to keep the TimeGenerated column as we want to filter by it.

This will get us the data we can use in Power BI:

To get this data into Power BI you can click Export, “Export to Power BI” in the query log screen.

Bringing it into Power BI

This gets you a txt file with the M expression that is downloaded to your PC. You can then paste the M query into a blank Power BI query. Running it gets me the data I want:

From this I need to parse the columns and measures that are used over the time period I captured the trace. This requires some tricks in Power Query (and it might not work for all scenario’s). These are the steps I took (you can download the full M query here):

I got rid of all diagnostic queries by filtering out Select.
The time column is not needed for the analysis so I remove it, you need it for log analytics to filter on time.
Now here comes the main trick. I need to extract column, measure and table names from the query. I do this by replacing many special characters like ( ,[ ] EVALUATE by ~ and then split the columns. Thanks to Imke for the idea :). That starts to get us somewhere:

Next I Unpivot and clean up the data a bit
I filter to only show rows that start with ‘ or [. Which keeps all column, table and measure references.
To add more information I add a column to show what the type of field it is, measure, table or column.
Finally I group the table and add a count.

This gets me the data I want:

With the data I can start doing some reporting:

Now you have an overview of which fields are used and how often they are referenced. You can extend this solution much further of course, for example you could add a username to see who accessed what and how often or you could compare it with the schema that you can get from other discovers to see which fields are never used so you can clean them up.

You can download the full solution here as PBIT. When you open it up for the first time it asks for the URL, you can get it from the M query that you download from Log Analytics.

Let’s block ads! (Why?)

Kasper On BI

#PowerQuery – Control the expand columns so it includes new columns

October 9, 2019 Self-Service BI

Image a scenario where your column in your PowerQuery that contains a table with a set a columns that you know at some point will have more columns added.

In the case above I know that we at some point will add more columns in the merged ProductAttributes table.

How can we make this dynamic using PowerQuery

When we click the icon for expanding the table, we might just select this and move on

But notice the formula created in

It says

= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”, {“Brand”}, {“Brand”})

This means that even though we might add new columns to the ProductsAttributes table – it will still only be Brand that is expanded and only that column.

The bolded arguments is 2 lists that contains the Column names to expand and the new names of the columns – the last argument is optional so we can actually skip that if we want the original names – https://docs.microsoft.com/en-us/powerquery-m/table-expandtablecolumn

Now by changing the formula to this

= Table.ExpandTableColumn(#”Merged Queries”, “ProductAttributes”,List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})
)

We can make the table dynamically expand when adding new columns in the table ProductAttributes

We get the new column included as well

The magic formula does this

Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0})

Will return a list of column names from the step before
expansion (note I use the step name and column name) – and I use the {0} to extract the column names only form the first row – otherwise the formula will fail.

But as we cannot have the same column names twice (i.e. ProductKey needs to go away) so we need to use the List.RemoveItems functions

List.RemoveItems(Table.ColumnNames(#”Merged Queries”[ProductAttributes]{0}), {“ProductKey”})

Thereby removing the ProductKey Item in the list

And this means that when we get more columns in the table “ProductAttributes” table they will automatically be included in the expanded columns

Hope this can help you power queries even more dynamic.

Here is an example file – Link

Power Query On !

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Determine columns you don’t need using DMV’s in Power BI

November 27, 2018 Self-Service BI

Today a quick one that I came across while writing a different blog post that I will blog later. I know we have talked about it again and again but a good best practice is to remove any high carnality columns you don’t necessary need. This trick is not new and has been blogged about before in different places but I wanted to emphasize it again due to the importance.

I was finishing up my next blog post and wanted to upload the sample file. While doing that I noticed the file was 150MB large. That is rather large for such a simple file. The largest table has 500,000 rows and none of them are unique. What is going on?

DAX studio

So I whipped open DAX Studio and connected to my Power BI desktop file. There I switched to DMV in the query pane. DMV stands for Dynamic Management Views and come to us from the Analysis Services world giving us a ton of interesting information. about our model.

To figure out what is going on I started out by looking at the storage tables. You can double click the query name in the pane and it generates a query.

What I need here is to look at the row count field and find out which one is the largest. Row count will give us the unique row count for a column. Unique rows do not compress well and are responsible for a large percentage of the memory usage of the model. The good thing is that the query langue used for DMV’s is like SQL. Therefor I can change the query to the following: “select * from $ SYSTEM.DISCOVER_STORAGE_TABLES order by rows_count desc”.

Running this query gives me the following result:

Immediately we spot the culprit, we have an ID column in the sales table that is responsible for the majority of the space needed. After removing the column the model size went from 156 MB to 13.6 MB. Talk about a simple optimization here!

More DMV’s?

This is just the tip of the iceberg on DMV’s, you can also do things like:
– Getting all your measures with:
$ SYSTEM.TMSCHEMA_MEASURES
– Determining the measure dependency:
$ SYSTEM.DISCOVER_CALC_DEPENDENCY
– Get all the tables:
$ SYSTEM.DBSCHEMA_TABLES
– Get all the columns:
$ SYSTEM.DBSCHEMA_COLUMNS

Finally as mentioned before you can write SQL like queries, for example if you want to get a list of all the columns in the dimDate table you can do:
select * from $ SYSTEM.DBSCHEMA_COLUMNS where table_name=’$ Dimdate’

Pretty cool right!

Update: Daniel Otykier on twitter also suggests using TabularEditor’s Best Practice Analyzer. It considers if columns are being referenced in any DAX expressions (even RLS filters), if they participate in relationships, or are used as hierarchy levels etc. Then you know for sure if you can remove a column. Great idea!

Let’s block ads! (Why?)

Kasper On BI

#PowerQuery – Filter by a dynamically set of columns

November 26, 2018 Self-Service BI

A few days ago, I ran into a problem where I had to filter a product table by a dynamically set of columns – so in one solution it could be filtering by field x and y and in other solutions it had to be by field a, b and c.

In this case only rows that where not blank sin either of these columns should be included in the result.

Hmmm… how can we do this using Power Query –

Here is the demo data

#PowerQuery – Filter by a dynamically set of columns

And if we only want the rows where Field A and Field B has the value “xx” we could add a filter statement (this can only by done if you modify the SelectRows formula manually! )

= Table.SelectRows(#”Changed Type”, each ([Field A] <> “” or [Field B] <> “”))

#PowerQuery – Filter by a dynamically set of columns

But what if we wanted to make the fields dynamically

Let’s start by creating a list containing the field names we want to filter

#PowerQuery – Filter by a dynamically set of columns

This list could also be retrieved by extracting data from a config file or a database if you would make it even more flexible – just remember it must be at list

Now you should know that any row in a table is a Record

#PowerQuery – Filter by a dynamically set of columns

And each record has fields and Power Query has functions related to Records.

https://docs.microsoft.com/en-us/powerquery-m/record-functions

One of this is the Record.SelectFields ( https://docs.microsoft.com/en-us/powerquery-m/record-selectfields ) and this function can be used to select specific fields in a record

So lets add a custom column with

= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.SelectFields(_, FieldList))

The first argument uses the _ statement and thereby referring to the current record – and the second is over list of fields

#PowerQuery – Filter by a dynamically set of columns

And as you can see when selecting a cell the record in the column now contains only of the fields we have specified in our list.

Now we turn this in to a list by using the function Record.ToList in our AddColumn statement

= Table.AddColumn(#”Filtered Rows”, “FilterMultipleColumns”, each Record.ToList(Record.SelectFields(_, FieldList)))

#PowerQuery – Filter by a dynamically set of columns

Notice that we now have a list in the column instead of a record

Next step is to use the function List.MatchesAny to check whether the contains our filter

= Table.AddColumn(#”Changed Type”, “FilterMultipleColumns”, each List.MatchesAny(Record.ToList(Record.SelectFields(_, FieldList)), each _ <> “”))

#PowerQuery – Filter by a dynamically set of columns

The second argument is then our filter for all fields and it will return TRUE or FALSE if the criteria is met.

And finally, we can filter the rows using the added Column and only include rows that return true

#PowerQuery – Filter by a dynamically set of columns

And if we want to filter by other columns/fields, we change the list

#PowerQuery – Filter by a dynamically set of columns

And it works

#PowerQuery – Filter by a dynamically set of columns

Hope you find this useful and let me know if you do

Query On !

Let’s block ads! (Why?)

Erik Svensen – Blog about Power BI, Power Apps, Power Query

Inserting multiple columns to a list at once

November 9, 2017 BI News and Info

I’m working with a nested list (labeled here as allEntries) and I’m trying to insert some additional columns. My list has 12 categories, each category has a different number of entries, and each entry starts with 15 elements.

I’m trying to add 3 more elements, and currently I’m doing it one element at a time using table and insert. For instance the first thing I want to add is the day of the week explicitly

allEntries  = 
Table[
    Insert[
        allEntries[[j, i]],
        DateString[{allEntries[[j, i, 2]], {"Month", "Day", "Year", "Hour", "Minute"}}, "DayName"],
         3],
{j, 1, Length[shapes]},
{i, 1, Length[allEntries[[j]]]}
];

Here j goes from 1 to 12, and then i goes from 1 to the number of entries in the category (anywhere from a few hundred to a few 10s of thousands). I’m simply converting the date into a simple day of the week to make it easier to select later on, and I want this added at position 3.

This works fine, but I’m doing it two more times to add other data in the same fashion, and it seems like there should be a way to add this in one step.

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Displaying Columns of Metadata in the Azure Portal

May 28, 2017 Self-Service BI

This is just a quick tip about displaying columns in the Azure portal. If you’ve not been using this little feature, they can be helpful.

Here’s an example of what part of my portal looks like for virtual machines:

AzurePortal ColumnsDisplayed Displaying Columns of Metadata in the Azure Portal

Let’s block ads! (Why?)

Blog – SQL Chick

Applying functions to some columns in a Dataset and keeping only those

November 21, 2016 BI News and Info

Take the following example Dataset:

data = Table[Association["a" -> i, "b" -> i^2, "c" -> i^3], {i, 4}] // Dataset

PZSgO Applying functions to some columns in a Dataset and keeping only those

Picking out two of the three columns is done this way:

data[All, {"a", "b"}]

LG3jU Applying functions to some columns in a Dataset and keeping only those

Now instead of just returning the “a” and “b” columns I want to map the functions f and h to their elements, respectively, and still drop “c”. Based on the previous result and the documentation of Dataset I hoped the following would do that:

data[All, {"a" -> f, "b" -> h}]

Wvwml Applying functions to some columns in a Dataset and keeping only those

As you can see, the behavior is not like the one before. Although the functions are mapped as desired, the unmentioned column “c” still remains in the data.

Do I really need one of the following (clumsy looking) alternatives

data[All, {"a" -> f, "b" -> h}][All, {"a", "b"}]
data[Query[All, {"a", "b"}], {"a" -> f, "b" -> h}]
Query[All, {"a", "b"}]@data[All, {"a" -> f, "b" -> h}]

to get:

q62un Applying functions to some columns in a Dataset and keeping only those

or is there a more elegant solution?

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Tech Tip Thursday: Displaying text columns in tool tips

October 14, 2016 Self-Service BI

On Thursdays, we like to provide tips and tricks for Power BI and Business Intelligence.

Tech Tip Thursday is slightly different today — usually we feature a video from the very helpful Guy in a Cube, but this week we wanted to highlight a support post by Microsoft Senior Program Manager and Power BI webinar guru Charles Sterling. Back in May we added the ability to show custom tool tips in reports to Power BI Desktop, but by default tooltips display the count of a column. What happens if you want to display the text instead? Charles has you covered in a short step-by-step guide.

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

Tag Archives: columns

Hello, I want to create a matrix with a certain number of rows and columns

Finding all audit enabled tables and columns for your Common Data Service environment

Figure out which columns and measures are used in your SSAS Tabular model

Start capturing data

Bringing it into Power BI

#PowerQuery – Control the expand columns so it includes new columns

How can we make this dynamic using PowerQuery

Determine columns you don’t need using DMV’s in Power BI

DAX studio

More DMV’s?

#PowerQuery – Filter by a dynamically set of columns

Displaying Columns of Metadata in the Azure Portal

Applying functions to some columns in a Dataset and keeping only those

Tech Tip Thursday: Displaying text columns in tool tips

Recent Posts

Categories

Archives

Tag Archives: columns

Start capturing data

Bringing it into Power BI

Share this:

How can we make this dynamic using PowerQuery

DAX studio

More DMV’s?

Share this:

Recent Posts

Categories

Archives