April 6, 2020
I am facing a problem that I hope can be solved with your knowledge/input.
My company uses a ticket system to handle IT service requests and incidents. The basic information (like ticket ID, country code, ticket category, date, ticket status, ...) is processed in the background by a SQL server. Currently the SQL database contains +600.000 tickets of several internal customers. In this case, customers are countries that use the ticket system for their purposes (First Level Support).
In addition, there is the headquarter, which operates the Second Level Support and is responsible for the underlying services (e.g. e-mail, printer management, client software, client hardware).
So far we were not aware...
- how many tickets are processed by the countries
- how large the number of tickets of the individual categories (e.g. client software, server, e-mail) of these tickets are and
- how good the centrally offered services actually are, as only tickets that could not be solved locally were forwarded to the 2nd level support (so we know how much effort we have to put in - but not the countries).
Therefore I built a dashboard based on Excel 365 and Power Pivot. But now I am facing a problem...
I started to create the report for one country/customer only. A special, restricted view of the entire data basis was created for me from a central department.
- Faster retrieval of data (since fewer tickets have to be processed, ~6,000 vs. +600,000)
- Secure query. This means that the country/customer only sees its data and cannot manipulate the query
- Difficult to create a new report for another customer, because...
- A fast exchange of the database (SQL database) is not possible without further effort, because links to other help tables (e.g. merging of categories, locations) exist. An update of all links takes ~4h per each new customer.
- Due to separate files/reports per customer, changes must be transferred in all "copies" (with +30 customers this is not a rewarding job)
So my question is this:
What do you think is the best way to create a report for different customers?
Is there a way to create a report for all customers (this makes it easier to make changes because everyone benefits at the same time) and then limit it so that each customer can only see their tickets and reports?
I would also be happy to use a drop-down menu on a hidden sheet where I can select a customer. In this case, however, I would have to make sure that no customer can change the database (= select other customers).
July 16, 2010
Welcome to our forum!
The only way you can securely restrict access on a customer basis is with Power BI and row level security. This requires all users to have at least a Power BI Pro licence at US$10/month. Security in Excel is simply a deterrent that is easily thwarted by users.
If you go down the Power BI path you will use Power Query to get the data, then Power Pivot for the modelling and lastly the Power BI visuals for the dashboard.
I hope that points you in the right direction.