Total newbie to Power BI, basic use case, seeking modelling and visualization advice

[Faz86]

Hi all,

I am a brand new user to Power BI and trying to fulfill a few use cases. I was wondering if I could get some pointers on how best to model and visualise this use case as I begin my Power BI journey. The goal is to learn it and then get the business using it too, once they see the power of the tool.

I have a database table right now that shows artifact deployment data, loosely described as follows:

environment - Values can be (DEV, QA, PRE-PROD, PROD) etc

project code (which project, the artifact is associated with)

artifact group

artifact name

artifact version

artifact type - Values can be (SNAPSHOT, RELEASE)

deployment time

There are tonnes of metrics I want to visualise but I would like help with the following:

1. SNAPSHOTS are not allowed on any environments other than DEV therefore I want to show which SNAPSHOTS have mistakenly been pushed to the other environments, for each project.

2. Which RELEASES have been pushed to PROD that have not gone through DEV, QA, PRE-PROD, for each project.

3. Average time spent in each environment for each module, per project before it is promoted to the next environment

For these 3 use cases, I would greatly appreciate advice on the best way to model this data in Power BI and the best visualises to display this data for report users.

Thanks in advance

[Greg_Deckler]

@Faz86 Welcome. Sorry, having trouble following, can you post sample data as text and expected output?

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Faz86]

Hi Greg,

Thanks for the reply.

So here is some sample data, showing 10 rows from my deployments database table, with column headers.

# loadId, stageCode, envCode, tacCode, type, projectCode, projectName, label, active, description, requestor, deployer, deployType, applicationType, taskType, id, idQuartzJob, jobServerLabelHost, virtualServerLabel, execStatisticsEnabled, triggersStatus, artifactGroupId, artifactId, artifactVersion, lastRun, comments
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'EAOO', 'BI_BBS_E-AOO', 'EAOO_DemoRESTwithBasicAuth', '', '', '', '', '', 'ROUTE', '', '7260', NULL, 'RPD1DE-EAOO', '', '', '', 'org.example', 'DemoRESTwithBasicAuth', '0.1.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_IAM_Service_API', '', '', '', '', '', 'SERVICE', '', '3632', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.IAM_Service.rest', 'IAM_Auth_Services_API', '1.5.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_IBDP', '', '', '', '', '', 'SERVICE', '', '4937', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.IBDP.rest', 'Job_IBDP_API', '1.18.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_Job_Employee_API', '', '', '', '', '', 'SERVICE', '', '8554', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.Employee.rest', 'Job_HR_Employee_API', '0.2.0', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_Job_SellOut_API_2-1', '', '', '', '', '', 'SERVICE', '', '8709', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.SellOut.rest', 'Job_SellOut_API', '1.67.0', '0000-00-00 00:00:00', ''
'20200207', 'QA', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_NetSales_SellIn_ROUTE', '', '', '', '', '', 'ROUTE', '', '76', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.netSales', 'Route_SellIn_JMS', '1.13.0', '0000-00-00 00:00:00', ''
'20200207', 'PROD', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_PortalOne', '', '', '', '', '', 'SERVICE', '', '5002', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.PortalOne.Rest', 'Job_PortalOne_API', '1.1.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'PROD', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_PortalOne_2-0', '', '', '', '', '', 'SERVICE', '', '8376', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.PortalOne.Rest', 'Job_PortalOne_API', '1.2.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_PriceRFC_API', '', '', '', '', '', 'SERVICE', '', '4728', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.price.rest', 'job_PriceAPI_RFC', '1.6.0-SNAPSHOT', '0000-00-00 00:00:00', ''
'20200207', 'DEV', 'D1', 'TPD1DE', 'ESB', 'DIBRAZIL', 'BI_CS_DATAINTEGRATION_BRAZIL', 'DIBRAZIL_Product_API', '', '', '', '', '', 'SERVICE', '', '6', NULL, 'RPD1DE-DIBRAZIL', '', '', '', 'com.bi.dibrazil.product.rest', 'Job_API_Products', '2.9.0-SNAPSHOT', '0000-00-00 00:00:00', ''

In terms of what I need to do, I don't know what the data should look like in terms of how it should be formatted. That is part of my question really. From this data I need to be able to show the below metrics and I am looking for guidance on the best way to model the day so I can visualise these 3 metrics in a report:

1. Column 'artifactVersion' can contain the words 'SNAPSHOT' or 'RELEASES'. Any rows for this column which have the word 'SNAPSHOT' should not exist in rows where the column value for column 'stageCode' is 'PROD'. This is because we do not allow 'SNAPSHOT' releases to go to Production. Therefore I want to show 'artifactGroup', 'artifactId', and 'artifactVersion' combinations where the artifactVersion column contains the word 'SNAPSHOT' are associated with 'PROD' 'stageCode' column values. per project (ProjectCode column).

2. Similar to 1 above, for any rows where the column 'artifactVersion' contains the word RELEASES, and the column value for 'stageCode' is 'PROD', I want to show visually which of these RELEASES have no corresponding rows where the 'stageCode' column value is not 'PROD'. This visualisation therefore is showing which artifacts have made it to PRODUCTION but have not gone through DEV, QA, PRE-PROD, for each project.

3. I want to also show the measure of the average time spent in each environment (StageCode column) for each artifact (artifactGroup, artifactId, artifactVersion columns), per project (projectCode column), before it is promoted to the next environment. So for example, how long was artifactGroup Id 'com.bi.dibrazil.price.rest' with artifactId 'job_PriceAPI_RFC', and artifactVersion '1.6.0-SNAPSHOT' which is associated to projectCode 'DIBRAZIL' in the 'QA' environment (stageCode column) before, it was moved to the 'PROD' environment.

Hopefully that makes a lot more sense?

[Faz86]

@Greg_Deckler 

Does the extra detail help?

Any help is appreciated.

[FrankAT]

Hi @Faz86 

I think you can solve much of your expected results in using Power Query first.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)