Monthly Excel file import, diff between versions -> Table, Page, PageTable, Repeater Matrix?

[maba]

Hello,
I need to import regularly - every 15 or 30 days - a big .xslx file into my PW installation.
This file now has 14 columns, 5.000 rows and grows every month.

I'll need to group, order and work with these data to:

• analyse User monthly costs
• analyse User costs per Asset
• ...

User (real AD account) has to match with a PW user - I can't join to the domain - but as you can see I have some services users (start with sca_*) or no user at all. Those rows have to be assigned to a specific user, e.g. account100.

And:

• I would like to be able to have a kind of diff function to compare User assets between this and last month (and so on)
• other request is to have a notification when something change for a User between actual and latest import

First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?
Those are very repetitive data and I think a page reference is better than to import all the data every time but I have to understand how to manage those "dynamic" groups of software (AccType Det), hardware (Asset), ... For example Price will be imported and not stored with the description because it could be change in the future and I'll not have any control on it.

Thanks!

User,OE,productNmr,AccType1,AccType Det,Count,Price (€),Sum,ASNA,CC,AccType Info,Asset,AccGroup,,,,,,,,,,,,,

Spoiler

,IT,000410,SAP Service,Licenses,0,0,0,012345678,ABC1234,-,,-,,,,,,,,,,,,,
account1,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA3913,-,,,,,,,,,,,,,
account1,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA3913,-,,,,,,,,,,,,,
account1,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Adobe Flash Player ActiveX 27.0.0.183 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: viewer and player,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Adobe Shockwave Player (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: viewer and player,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),CDBurner XP 4.5.6.5844 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Copy Path to Clipboard (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),OS/M Standard 4.2 - 64bit (installation),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Firefox Windows x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Google Chrome (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Google Chrome x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Java (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: CASE,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Java 8u121 - 32/64 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: other,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Microsoft Office Professional Plus (machine license),1,25.66,25.66,012345678,ABC1234,-,AAA3913,Microsoft-products,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Microsoft Office Professional Plus 2013 SP1 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Microsoft-products,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Notepad++ (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),Notepad++ x.x (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: tools,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor (freeware license),1,0,0,012345678,ABC1234,-,AAA3913,Software: converter,,,,,,,,,,,,,
account1,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor 5.5 (installation),1,0,0,012345678,ABC1234,-,AAA3913,Software: converter,,,,,,,,,,,,,
account1,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account1,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC ITA (machine license),1,0,0,012345678,ABC1234,-,AAA3913,OS,,,,,,,,,,,,,
account2,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA2999,-,,,,,,,,,,,,,
account2,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA2999,-,,,,,,,,,,,,,
account2,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,,AAA2999,OS,,,,,,,,,,,,,
account2,IT,000010,Services & Software - CIC (Software I...),MATLAB Compiler Runtime (machine license),1,0,0,012345678,ABC1234,-,AAA2999,MathWorks-products,,,,,,,,,,,,,
account2,IT,000010,Services & Software - CIC (Software I...),MATLAB Compiler Runtime 2015b - 9.0 - 32bit (installation),1,0,0,012345678,ABC1234,-,AAA2999,MathWorks-products,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),Notepad++ (freeware license),1,0,0,012345678,ABC1234,-,AAA1825,Software: tools,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),Notepad++ 7.3.3 (installation),1,0,0,012345678,ABC1234,-,AAA1825,Software: tools,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor (freeware license),1,0,0,012345678,ABC1234,-,AAA1825,Software: converter,,,,,,,,,,,,,
account100,IT,000023,Services & Software - CIU (Software),PDF-XChange Editor 5.5 (installation),1,0,0,012345678,ABC1234,-,AAA1825,Software: converter,,,,,,,,,,,,,
account100,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA1825,OS,,,,,,,,,,,,,
account100,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC ITA (machine license),1,0,0,012345678,ABC1234,-,AAA1825,OS,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,Notebook ´Application E02200M48 (2000-2400)´,1,49.5,49.5,012345678,ABC1234,-,AAA2963,-,,,,,,,,,,,,,
account100,IT,000015,OS Windows Client,OS4 - Standard Group (Installation w Office),1,10.6,10.6,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000023,Software - OS Windows - Client - ABCC-EU\CI,OS/M for ABCC Europe (machine license),1,0,0,012345678,ABC1234,-,AAA2963,OS,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,O Notebook ´Standard´,1,0,0,012345678,ABC1234,-,AAA3508,-,,,,,,,,,,,,,
account100,IT,000016,Client - Computer - Hardware,Notebook ´Standard  (1100-1300)´,1,101,101,012345678,ABC1234,-,AAA3508,-,,,,,,,,,,,,,
sca_check-mk,IT,000010,Server - Hardware,Server - virtual ESX,1,42.96,42.96,012345678,ABC1234,-,BBB137,-,,,,,,,,,,,,,
sca_check-mk,IT,000014,Software - Dummy - ABCC-EU\CI,OS/M for any (machine license),1,0,0,012345678,ABC1234,-,BBB137,OS,,,,,,,,,,,,,
sca_other-ab,IT,000010,Software - OS Linux - ABCC-EU\CI,OS/X - RHEL Linux Server virtual (machine license),1,10.50,10.50,012345678,ABC1234,-,BBB137,OS,,,,,,,,,,,,,

 

[dragan]

I'm not sure I get the full picture...

Apart from analyses, are you doing anything else with this dataset in PW? Are those Excel fields also present as PW fields already?

How do you plan to do this analyses in PW? Do users see this in the backend somewhere? Are you going to build something like a custom dashboard where every user sees their data? Or is it for management overviews?

3 hours ago, maba said:

I can't join to the domain

I don't know what this means.

Depending on what exactly you need to do, you might even consider not using PW at all, i.e. use plain mySQL for data-storage / -update and analytics. PW has an excellent DB API you could use.

I would definitely take a closer look at @bernhard's RockFinder / RockGrid modules, which dramatically optimizes queries in PW, and offers state-of-the art data listing.

[maba]

Nope. No other needs beyond this costs analyses.
Some fields are already present in my PW installation as page - user (user pages), asset (clients and maybe servers page) - other as single like ASNA and CC (the same for all the rows).

Basically, I thought to analyse this dataset with ListerPro for quick checks (mainly on the latest import), create some charts and share them with my team and other with my boss. The need of a custom dashboard is possible but before I have to set the notification system for every import (and the "diff" features. I have no control of what HQ charges to the daughter companies.
So, at the first, no user interaction in the backend.

21 hours ago, dragan said:

I don't know what this means.

Not so related. It is just to say that I can't use LDAP/SAML for Active Directories authentication.

Yes, I know all the @bernhard's great modules but I don't know if I can create the "diff" features using RockGrid. I'll take a closer look, also for the import requests.

[bernhard]

14 hours ago, maba said:

Yes, I know the all the @bernhard's great modules but I don't know if I can create the "diff" features using RockGrid. I'll take a closer look

This sound like you don't know them well enough ? If using RockFinder I'd definitely go with RF2 and not RF1.

16 hours ago, dragan said:

Depending on what exactly you need to do, you might even consider not using PW at all, i.e. use plain mySQL for data-storage / -update and analytics. PW has an excellent DB API you could use.

8 lines of code when using RockTabulator. Maybe 10 more for applying the diff (if you have a library for that). I bet you'll save hours of work using RockTabulator, but as I don't fully understand your scenario that's just a guess...

20 hours ago, maba said:

First request: which is the best solution to store those data in your opinion? Page, Table, Repeater Matrix, ...?

If you take the RockGrid or RockTabulator route you'll definitely be better off using pages. I'm using pages for almost anything nowadays because it makes many things a lot easier (like finding data with RockFinder, creating joins and relations, creating fields programmatically etc.; One could create something very similar to Repeater Matrix using RockGrid/RockTabulator but with a lot more options regarding the listing/display part in the backend.

[maba]

4 hours ago, bernhard said:

This sound like you don't know them well enough ? If using RockFinder I'd definitely go with RF2 and not RF1.

"RockFinder2 is the successor to RockFinder v1", so.. I'll use RockFinder ?
I follow your works after saw this awesome Custom Office Management CRM/Controlling Software.

4 hours ago, bernhard said:

8 lines of code when using RockTabulator. Maybe 10 more for applying the diff (if you have a library for that). I bet you'll save hours of work using RockTabulator, but as I don't fully understand your scenario that's just a guess...

Diff has to be done on previous/last imports for a User or Asset. I won't compare the xlsx/csv files.
E.g.: take all the pages about asset ABC1234 from the import of 02 Sep., compare them with ABC1234 pages of today's import and highlight which items changes (pages and their fields).
I don't think this is possible with RockTabulator. Is it possible?

I'll try to be more clear with an example (that I still have to set up ?).

Thanks for your time and help guys!

[bernhard]

RockTabulator is just a way to present tabular data. Which data is completely up to you. You can list text, images, html code, whatsoever. So you can definitely also show diffs... as it's just html. The great thing about tabulator is that it supports multiline rows out of the box (aggrid has troubles with that). And this might especially be handy in your situation.

http://tabulator.info/docs/4.4/format