locked
New to PowerPivot RRS feed

  • Question

  • Hi All,

    I am new to powerpivot, I have the below structure on a SQL Server database. This is basically tracking  when a desktop was rebooted, rebooted user name, createddate (scheduled to run daily 3 times).

    Based on the data below, how to create powerpivot report, there are few questions end user would be interested.

    1. Top 10 Desktop rebooted over the time
    2. Top 10 Rebooted user over the time
    3. Top 10 offline Desktops
    5. Pivot chart by selecting a server name - displays the latest uptime, how many times rebooted, who rebooted.

    CREATE TABLE [Server].[DesktopActivity](

    [Server_Name] [nvarchar](128) NOT NULL,
    [Uptime] [varchar](50) NULL,
    [RebootedUser] [varchar](50) NULL,
    [CreatedDate] [datetime] NULL
    ) ON [PRIMARY]

    ALTER TABLE [Server].[DesktopActivity] ADD  CONSTRAINT [DF_DesktopActivity_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]

    and the sample data as follows

    CSR-35R5M02,9/24/2014 10:36:58 AM,NULL,2014-09-24 23:01:14.363
    CSR-35J4M02,Offline,NULL,2014-09-24 23:01:41.893
    CSR-34K5M02,9/24/2014 2:01:49 AM,NULL,2014-09-24 23:02:01.007
    CSR-34P3M02,9/24/2014 2:01:49 AM,NULL,2014-09-24 23:02:20.117
    CSR-34Q4M02,9/20/2014 11:07:01 AM,NULL,2014-09-24 23:02:39.257
    CSR-35H4M02,9/24/2014 10:36:26 AM,NULL,2014-09-24 23:02:58.773
    CSR-35R4M02,9/24/2014 5:11:44 PM,NULL,2014-09-24 23:03:16.230
    CSR-35Z2M02,9/24/2014 8:17:00 PM,NULL,2014-09-24 23:03:39.420
    CSR-3656M02,Offline,NULL,2014-09-24 23:03:59.900
    CSR-3662M02,Offline,NULL,2014-09-24 23:04:20.900
    CSR-3663M02,9/24/2014 10:33:01 AM,NULL,2014-09-24 23:04:28.060
    CSR-36N5M02,9/24/2014 10:32:39 AM,NULL,2014-09-24 23:04:47.657
    CSR-3607M02,9/24/2014 3:22:02 AM,NULL,2014-09-24 23:05:06.770
    CSR-34Q1M02,Offline,NULL,2014-09-24 23:05:28.403
    CSR-3626M02,9/24/2014 4:19:50 AM,NULL,2014-09-24 23:05:47.670
    CSR-3642M02,9/24/2014 2:18:10 PM,NULL,2014-09-24 23:05:54.893
    CSR-35C4M02,9/24/2014 5:07:41 PM,NULL,2014-09-24 23:06:04.603
    CSR-36D2M02,9/24/2014 10:34:03 AM,NULL,2014-09-24 23:20:00.053
    CSR-34H3M02,9/24/2014 10:34:23 AM,NULL,2014-09-24 23:20:18.190
    CSR-34S6M02,9/21/2014 7:59:33 AM,NULL,2014-09-24 23:20:56.640
    CSR-3615M02,9/16/2014 12:19:05 PM,NULL,2014-09-24 23:21:08.527
    CSR-35D5M02,Offline,NULL,2014-09-24 23:21:25.443
    CSRS-D5HKVY1,Offline,NULL,2014-09-25 07:00:40.623
    CSRS-5WKKVY1,Offline,NULL,2014-09-25 07:00:49.123
    CSR-34F4M02,9/24/2014 7:24:59 AM,NULL,2014-09-25 07:01:17.377
    CSR-3563M02,9/25/2014 2:01:49 AM,NULL,2014-09-25 07:01:40.923
    CSR-35P2M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 07:02:00.390
    CSR-34Q4M02,9/25/2014 4:48:46 AM,NULL,2014-09-25 07:02:21.007
    CSR-35H4M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 07:02:39.280
    CSR-35R4M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 07:02:55.990
    CSR-35J3M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 07:03:15.500
    CSR-34H5M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 07:03:35.613
    CSR-36N2M02,9/25/2014 2:01:52 AM,NULL,2014-09-25 07:03:59.180
    CSR-3627M02,Offline,NULL,2014-09-25 07:04:31.133
    CSR-36H1M02,9/25/2014 2:01:51 AM,NULL,2014-09-25 07:04:55.837
    CSR-35G5M02,Offline,NULL,2014-09-25 07:05:19.133
    CSR-3626M02,9/25/2014 4:33:55 AM,NULL,2014-09-25 07:05:36.423
    CSR-34M4M02,9/25/2014 2:01:49 AM,NULL,2014-09-25 07:06:02.407
    CSR-3565M02,9/24/2014 10:34:39 AM,NULL,2014-09-25 07:06:25.737
    CSR-3676M02,Offline,NULL,2014-09-25 07:06:50.137
    CSR-34S6M02,9/21/2014 7:59:33 AM,NULL,2014-09-25 07:07:07.180
    CSR-35B4M02,9/24/2014 6:41:01 PM,NULL,2014-09-25 07:07:34.383
    CSR-6K00J02,9/24/2014 4:56:26 PM,NULL,2014-09-25 07:07:58.527
    CSR-34H1M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 15:07:07.943
    CSR-35S1M02,9/25/2014 2:01:58 AM,NULL,2014-09-25 15:07:12.697
    CSR-35D7M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 15:07:34.050
    CSR-34Q2M02,Offline,NULL,2014-09-25 15:07:52.250
    CSR-3686M02,Offline,NULL,2014-09-25 15:08:17.250
    CSR-36C2M02,9/25/2014 10:56:55 AM,NULL,2014-09-25 15:08:39.120
    CSR-36L5M02,Offline,NULL,2014-09-25 15:09:04.757
    CSR-34J1M02,9/25/2014 7:12:03 AM,NULL,2014-09-25 15:09:24.123
    CSR-35Y4M02,9/25/2014 2:35:30 AM,NULL,2014-09-25 15:09:44.747
    CSR-3692M02,9/25/2014 2:01:50 AM,NULL,2014-09-25 15:10:03.857
    CSR-34M4M02,9/25/2014 2:01:49 AM,NULL,2014-09-25 15:33:38.300
    CSR-3542M02,9/25/2014 6:17:04 AM,NULL,2014-09-25 15:33:57.437
    CSR-35R6M02,6/10/2014 9:05:08 AM,NULL,2014-09-25 15:34:31.080
    CSR-3615M02,9/16/2014 12:19:05 PM,NULL,2014-09-25 15:34:57.917
    CSR-35P4M02,9/25/2014 7:55:17 AM,NULL,2014-09-25 15:35:19.560
    CSR-34S1M02,9/25/2014 2:01:48 AM,NULL,2014-09-25 23:05:13.580
    CSR-3632M02,9/25/2014 4:41:10 AM,NULL,2014-09-25 23:05:34.410
    CSR-35C4M02,9/25/2014 2:01:49 AM,NULL,2014-09-25 23:05:56.323
    CSR-34Q7M02,Offline,NULL,2014-09-25 23:15:30.360
    CSR-3542M02,9/25/2014 6:17:04 AM,NULL,2014-09-25 23:15:39.897
    CSR-36H7M02,9/25/2014 9:31:06 AM,NULL,2014-09-25 23:16:06.240
    CSR-35N5M02,9/25/2014 4:01:27 PM,NULL,2014-09-25 23:16:16.977
    CSR-34N4M02,8/4/2014 8:00:58 AM,NULL,2014-09-25 23:16:38.230
    CSR-3503M02,9/4/2014 3:03:16 PM,NULL,2014-09-25 23:16:47.820
    CSR-35D5M02,Offline,NULL,2014-09-25 23:16:59.857
    CSR-36F2M02,Offline,NULL,2014-09-25 23:17:13.857
    CSR-6K10J02,9/25/2014 11:41:49 AM,NULL,2014-09-25 23:17:28.983
    CSR-BCFQBZ1,9/26/2014 6:47:54 AM,NULL,2014-09-26 07:00:09.470
    CSRS-3HYKVY1,Offline,NULL,2014-09-26 07:00:13.443
    CSR-6K0YH02,9/26/2014 2:01:52 AM,NULL,2014-09-26 07:00:40.293
    CSR-34R3M02,9/26/2014 2:01:54 AM,NULL,2014-09-26 07:00:50.220
    CSR-34J5M02,Offline,NULL,2014-09-26 07:01:09.430
    CSR-35J4M02,9/26/2014 2:01:49 AM,NULL,2014-09-26 07:01:19.130
    CSR-34W3M02,9/26/2014 2:01:50 AM,NULL,2014-09-26 07:01:33.583
    CSR-34K5M02,9/26/2014 2:01:49 AM,NULL,2014-09-26 07:01:38.330
    CSR-34P3M02,9/26/2014 2:01:49 AM,NULL,2014-09-26 07:01:58.957
    CSR-35C2M02,9/26/2014 2:01:47 AM,NULL,2014-09-26 07:02:13.427
    CSR-34K3M02,Offline,NULL,2014-09-26 07:02:19.430
    CSR-34H1M02,9/26/2014 2:01:52 AM,NULL,2014-09-26 07:02:39.360
    CSR-35R4M02,9/26/2014 2:01:48 AM,NULL,2014-09-26 07:02:53.797
    CSR-35P6M02,9/26/2014 2:01:49 AM,NULL,2014-09-26 07:02:56.250
    CSR-35S4M02,9/26/2014 2:02:13 AM,NULL,2014-09-26 07:03:09.637
    CSR-35Z2M02,9/26/2014 2:01:50 AM,NULL,2014-09-26 07:03:16.773
    CSR-35H1M02,9/26/2014 2:01:50 AM,NULL,2014-09-26 07:03:19.150
    CSR-35T4M02,9/25/2014 1:33:10 PM,NULL,2014-09-26 07:03:21.520
    CSR-35M4M02,9/26/2014 2:01:50 AM,NULL,2014-09-26 07:03:27.900
    CSR-34H5M02,9/26/2014 2:01:48 AM,NULL,2014-09-26 07:03:33.883
    CSR-35H5M02,9/26/2014 2:01:48 AM,NULL,2014-09-26 07:03:36.360
    CSR-3656M02,Offline,NULL,2014-09-26 07:03:39.930
    CSR-34R7M02,Offline,NULL,2014-09-26 07:03:43.930
    CSR-3653M02,9/25/2014 9:21:02 AM,NULL,2014-09-26 07:03:46.380
    CSR-3652M02FORD,Offline,NULL,2014-09-26 07:03:52.587
    CSR-36N2M02,9/26/2014 2:01:51 AM,NULL,2014-09-26 07:03:57.433
    CSR-3663M02,9/26/2014 2:01:49 AM,NULL,2014-09-26 07:04:09.870
    CSR-36C2M02,9/26/2014 2:01:50 AM,NULL,2014-09-26 07:04:14.740
    CSR-36H2M02,Offline,NULL,2014-09-26 07:04:18.433
    CSR-35W6M02,Offline,NULL,2014-09-26 07:04:22.433


    Ganesh

    Sunday, October 26, 2014 4:52 PM

Answers

  • I've built out a workbook here. Please take a look at it and see if it is working appropriately for you.

    I will explain any measures in it once you confirm they are right.

    • Marked as answer by Ganesh Babu Tuesday, October 28, 2014 6:29 PM
    Tuesday, October 28, 2014 4:31 PM
  • I've modified the model just a bit:


    What I've done is make an [Uptime-DateTime] field. This field contains datetime data which has been extracted from the original [Uptime] field.

    The measures:

    RebootCount:=COUNTROWS(
        SUMMARIZE( DesktopActivity
            , DesktopActivity[Server_Name]
            , DesktopActivity[Uptime]
        )
    )

    Per your explanation above, this counts the unique values of [Uptime] for each [Server_Name]. At a total level, this will gracefully handle the case of two distinct servers sharing a single value in the [Uptime] field, unlike a distinct count on [Uptime] which would return too small a value.

    OfflineCount:=CALCULATE(
        COUNTROWS( DesktopActivity )
        , DesktopActivity[Uptime] = "Offline"
    )

    Not entirely sure if this is what you need, but it counts the occurrences of "Offline" values for [Uptime].

    LatestReboot:=MAX( DesktopActivity[Uptime-DateTime] )

    Self-explanatory.

    LatestRebootUser:=CALCULATE( 
        VALUES( DesktopActivity[RebootedUser] )
        , SAMPLE(
            1
            , DesktopActivity
            , DesktopActivity[Uptime-DateTime]
            , 0
        )
    )

    It's can be tricky to make a measure that behaves well in a pivot table that returns text.

    VALUES() returns the unique values in the table or column passed to it. We evaluate this in the context of the SAMPLE().

    SAMPLE() is returning a 1-row table made up of the most recent datetime in [Uptime-Datetime] in current context. Based on this 1-row table, it is easy to evaluate the unique values of [RebootedUser] since there should be only one. In the rare case of two [Server_Name]s sharing an identical [Uptime-Datetime] and that [Uptime-Datetime] being the most recent, then at the Grand Total level you will see one of the two unique values of [RebootedUser] chosen essentially at random.

    TOPN() is the alternative to SAMPLE() here, but it would return all tied rows, and thus VALUES() would return the two distinct [RebootedUser]s and would therefore fail at the Grand Total level.

    Here are the pivot tables implemented with a brief description of what I did.


    • Marked as answer by Ganesh Babu Tuesday, October 28, 2014 9:32 PM
    Tuesday, October 28, 2014 8:21 PM

All replies

  • You'll have to provide some more context on this data.

    Server name - unique key for servers.

    Uptime - I'd expect this to be a length of time since last reboot, but this is clearly not the case.

    Is this the time of the last reboot? Does offline mean it's offline at the time the row was loaded, or has been offline? How long has it been offline?

    RebootedUser - these are all null so you cannot have top rebooteduser. Is this an error or am I missing something?

    CreatedDate - Is this the insert time into the table? These are all strictly greater than the dates in Uptime (I had to create a new field and remove the 'Offline' entries and cast to datetime)

    Why do you have datetimes in a text field (Uptime)?.

    Help us answer the questions you've asked.

    How do I know how many time a server has been rebooted?

    Since RebootedUser is strictly null, how do I identify top rebooted users?

    Monday, October 27, 2014 4:15 PM
  • Hi Greg,

    I really appreciate your time to discuss it. Here are my answers.

    Server name - unique key for servers.
    Nope, since I run it 3 times a day it will have 3 entries for each of the server.

    Uptime - I'd expect this to be a length of time since last reboot, but this is clearly not the case.
    It is  datetime which is basically the last reboot time

    Is this the time of the last reboot? Yes
    Does offline mean it's offline at the time the row was loaded, or has been offline? Yes
    How long has it been offline? There is a specific set most of the time it is offline

    RebootedUser - these are all null so you cannot have top rebooteduser. Is this an error or am I missing something? If it is NULL, the computer is offline.

    CreatedDate - Is this the insert time into the table? Yes
    These are all strictly greater than the dates in Uptime (I had to create a new field and remove the 'Offline' entries and cast to datetime) I don't see this field would have a Offline value.

    Why do you have datetimes in a text field (Uptime)?. Because it will have either a datetime, Offline, Access Denied.

    How do I know how many time a server has been rebooted? 
    From the table data, for a given period of time, if I get the distinct Uptime for each server
    is the no. of times it got rebooted.

    Since RebootedUser is strictly null, how do I identify top rebooted users?
    It is strictly NULL only for offline computer.
    Let me give clean data and we will go from there.


    Thanks again for your time.




    Ganesh


    • Edited by Ganesh Babu Tuesday, October 28, 2014 1:59 PM correction
    Tuesday, October 28, 2014 1:52 PM
  • I have the text file put here.

    https://onedrive.live.com/redir?resid=3CD7A72F141C7761%21176

    please verify if that can be used.


    Ganesh

    Tuesday, October 28, 2014 2:09 PM
  • I've built out a workbook here. Please take a look at it and see if it is working appropriately for you.

    I will explain any measures in it once you confirm they are right.

    • Marked as answer by Ganesh Babu Tuesday, October 28, 2014 6:29 PM
    Tuesday, October 28, 2014 4:31 PM
  • Thank you very much. Are you using power query or office 13 i guess...so I am unable to figure out how you are doing in power pivot window.

    Let me confirm once I have a new version. 

    I really appreciate you help on this thank you....


    Ganesh

    Tuesday, October 28, 2014 6:29 PM
  • I have Excel 2013, and I used Power Query to load the csv data into Power Pivot. I don't have ready access to a copy of 2010. If you need, I can provide screen captures and copy/pastes of measures.
    Tuesday, October 28, 2014 6:50 PM
  • Guess I am asking too much, if possible please do it.

    Thanks again....


    Ganesh

    Tuesday, October 28, 2014 6:58 PM
  • I've modified the model just a bit:


    What I've done is make an [Uptime-DateTime] field. This field contains datetime data which has been extracted from the original [Uptime] field.

    The measures:

    RebootCount:=COUNTROWS(
        SUMMARIZE( DesktopActivity
            , DesktopActivity[Server_Name]
            , DesktopActivity[Uptime]
        )
    )

    Per your explanation above, this counts the unique values of [Uptime] for each [Server_Name]. At a total level, this will gracefully handle the case of two distinct servers sharing a single value in the [Uptime] field, unlike a distinct count on [Uptime] which would return too small a value.

    OfflineCount:=CALCULATE(
        COUNTROWS( DesktopActivity )
        , DesktopActivity[Uptime] = "Offline"
    )

    Not entirely sure if this is what you need, but it counts the occurrences of "Offline" values for [Uptime].

    LatestReboot:=MAX( DesktopActivity[Uptime-DateTime] )

    Self-explanatory.

    LatestRebootUser:=CALCULATE( 
        VALUES( DesktopActivity[RebootedUser] )
        , SAMPLE(
            1
            , DesktopActivity
            , DesktopActivity[Uptime-DateTime]
            , 0
        )
    )

    It's can be tricky to make a measure that behaves well in a pivot table that returns text.

    VALUES() returns the unique values in the table or column passed to it. We evaluate this in the context of the SAMPLE().

    SAMPLE() is returning a 1-row table made up of the most recent datetime in [Uptime-Datetime] in current context. Based on this 1-row table, it is easy to evaluate the unique values of [RebootedUser] since there should be only one. In the rare case of two [Server_Name]s sharing an identical [Uptime-Datetime] and that [Uptime-Datetime] being the most recent, then at the Grand Total level you will see one of the two unique values of [RebootedUser] chosen essentially at random.

    TOPN() is the alternative to SAMPLE() here, but it would return all tied rows, and thus VALUES() would return the two distinct [RebootedUser]s and would therefore fail at the Grand Total level.

    Here are the pivot tables implemented with a brief description of what I did.


    • Marked as answer by Ganesh Babu Tuesday, October 28, 2014 9:32 PM
    Tuesday, October 28, 2014 8:21 PM
  • Thanks a million.

    Ganesh

    Tuesday, October 28, 2014 9:33 PM