Insights/PowerBi question - Merges

Usage and Config Insights
1

Beginner level PowerBI/SQL person here
I have my Insights setup and performing ETLs fine. Have tested the sample reports provided for both PowerBI and Tableau and I think I am ready to attempt to start editing the queries/tables to tailor them to our environment.

Question: If I make a table change, will it be overwritten by the next replication of BFEnterprise onto BFInsights db. I currently have replication running nightly.

For example: The Device Inventory visualization for Device Count by OS. I have filtered that down to Windows OS only. The way the query is written, it then lists each sub-version of Win10/2019 as a separate pie chunk(1607/1809/1903). I would like to have a single pie piece for 2019 and 1 for Win10. I think I can do that by merging the data fields in the db, but will it be overwritten nightly? Is there a better way?

Also, can we add a “Insights” group to this forum, since it is another app now?

2

Hey Meydey -

You should look at altering table structures purely as a last resort. Lots of bad things can happen if you do that. There are ways to accomplish what you want to do through SQL queries that don’t affect table structures in any way.

In regard to your example, here’s how to change the query that’s pulling the Device information in that report (qryMain):

  • Change the CASE statement that creates the calculated column [OS_Group] to be more granular, like:

            CASE 
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win2008%' then 'Windows 2008'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win2012%' then 'Windows 2012'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win2016%' then 'Windows 2016'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win2019%' then 'Windows 2019'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win10%' then 'Windows 10'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Win7%' then 'Windows 7'
                                        WHEN UPPER( DDim.[os] ) LIKE 'Mac OS%' then 'Mac'
                                        WHEN UPPER( DDim.[os] ) LIKE 'VMWare%' then 'VM Ware'
                                        ELSE ‘Unknown’
           END AS OS_Group

Then, base the visualization on [OS_Group]. This way, you get the groups that are relevant to you without touching the tables.

That’s a great idea at the end, but I don’t run the forum :smile:

Good Luck!

  • Steve
1 Like
3

Cool. As a sql noob, it seemed easier to edit the tables on the BI side, but then it wanted to convert the whole mess from DirectQuery to an import in to PowerBI.

This info needs to go into a new repository on Bigfix.me at least.

Thanks

1 Like