Add comments to Power BI Visuals and the data by using Analysis Services Cubes?

If you use Power BI reports, the users can add comments to a report and to a visual. But mostly, the business users want to add report to the data, e.g., to say “This data is not complete” or something like that. So, the use case for this blog post is to add comments to a report which is using a OLAP Cube as the data source.
Firstly, we need a simple data source for the cube. I’ve created two table. The first one contains the data for the cube, and the second one is used to store the comments:


CREATE TABLE dbo.factData (
	id int not null identity(1,1) primary key
	, category nvarchar(255) not null
	, project nvarchar(255) not null
	, amount int not null
);
GO
INSERT INTO dbo.factData (category, project, amount)
VALUES 
	('Cat A', 'P 1', 1000)
	, ('Cat A', 'P 2', 2000)
	, ('Cat B', 'P 3', 1100)

GO

CREATE TABLE dbo.comments (
	id int not null identity(1,1) primary key
	, project nvarchar(255) not null
	, comment  nvarchar(1000) not null
	, createtiondate datetime default GETDATE()
);
GO

INSERT INTO dbo.factData (category, project, amount)
VALUES 
	('Cat A', 'P 4', 1000)
GO

INSERT INTO dbo.comments (project, comment)
VALUES ('P 4', 'a comment ...')

After creating the tables, I added some sample data.
Next, I have created a SSAS model like this:

After deploying the cube, I created a Power BI report to display the data:

To show the comments, I added a Tooltip page to show the comments for each project of the first page:

Now, we need a simple way to comment. For this purpose, I created s Power App with a SQL connection to the source database:

After creating the app, which points to the comment table, I embed the APP to the Power BI report. Now, a user off the report can create a comment to each project.

Now we need a technique to refresh the cube to show the comments. If your cube contains many data, it is not good idea to refresh the whole data. But with SSAS you can refresh only one table, and this can be done by using the Azure Data Factory every X minutes. This means, when a user add comment, the user must wait a minute to see the comment.

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "Comments",
        "table": "comments"
      }
    ]
  }
}

Categorized: Allgemein

Comments are closed.