Issue: How to send the same report to a different client.

[Aanya]

Description.
We are currently using the SSRS to generate and send the reports.
We have a report that we send to multiple clients.
The report pulls out the transaction data for that particular client.
The query that pulls the transction data from the Database uses a parameter @clientID and pulls the data for that client.

Now we want to achieve the same using Power BI.
In power BI, we are not sure how to use variables (@clientID) in our queries that hits the database.
At the moment we are able to run the procedures from powerBI without parameters.

Thanks

[ianbarker]

Do you want the client to log in and only see data relevent to them? 

Or are you looking for a way to run a report for a particular client?

If the report is being filtered by one of your users, then surely all you need is a Client dimension.

You then filter your report by a particlar client and then Power BI will filter all the data so it's only relevent to that client.

If you are somehow allowing your client to log in and see the report, then you can use row level security where their user id is filtered to a particular client id. More info about that can be found here: https://powerbi.microsoft.com/en-us/documentation/powerbi-admin-rls/

[Aanya]

Thanks for replying ianbarker.

We want our users to filter the report as per the @clientID. This is a parameter to the Stored procedure which brings the data for all the clients. Does the client dimesnion work in the same way as the paarmeters work in SQL stored procedure?

Also can we have multiple dimensions such mutiple filters can be applied. 

Thanks

[ianbarker · Accepted solution]

Hi @Aanya

Does the client dimesnion work in the same way as the paarmeters work in SQL stored procedure?

More or less, yes. There are parameters in Power BI but you don't need them for what you are trying to achieve.

Also can we have multiple dimensions such mutiple filters can be applied.

Absolutely.

SSRS is quite different to Power BI. Power BI is based heavily on SSAS Tabular and so you need to get your head around slightly different concepts.

In data warehouses and cubes, we refer to Dimensions and Fact tables.

A dimension is basically a master table, such as Product, Customer, Supplier, etc.

The Fact tables usually contain the transactional type records, such as order information.

You haven't said what data source you are connecting to but I'm guessing your data is already structured in a similar manner to what I have described above. Most databases structures are.

As such, you would then bring through your clientid master table to Power BI and ensure there is a relationship set up between the client master table and the other related tables.

If you haven't already, I'd recommend working through the Guided Learning. This will help you a lot and doesn't take long to do.

Particularly relevent to you would bethe Getting Started guide which covers filters and Managing Relationships to ensure your client table is linked to the other appropriate tables.

[Aanya]

Thanks @ianbarker for such great insights into power BI. I have recently jumped into this new technology.

These inputs are surely going to help me and other beginers too. I will proceed with dimensions now.

Thanks,

Aanya

[ianbarker]

Glad I could help @Aanya

Honestly, I thouroughly recommend the guided learning. It's very easy to follow and your existing background in reporting will help you pick it up very quickly.

Good luck.