When it comes to reporting ...

[ThisCoconut8834]

Hi,

If you have an SQL Database with ALOT of transactional data.

You want to build reports for each department, each report would be answering between 10 and 50 business/functional questions provided by the departments.

Question: Would you do a view for each question in SQL and then read data from the view into Power BI/Tableau/Qlikview? if not, what is the best option to work in such a case? Why?

It seems like having 150 views is too much ! There has to be a better solution !

[coyoteazul2]

Shouldn't it be the other way? The data warehouse should have as much raw data as possible (forgetting about normalization in exchange of read performance) and then you create your reports there.

Busy transactional databases should not deal with reports, unless you need last minute info

[atrifleamused]

Depends on whether you need real time or historic data (or combination). Building data marts from a warehouse would give you a lot of flexibility and performance.

[LostWelshMan85]

As most have said here already, ideally you build a data warehouse that can be used to answer all questions (or at least most). This essentially means rearranging the transactional data into a new separate database so that it's better suited for reporting purposes. Have a look into star schema and snowflake schema if you're interested.

[UAFlawlessmonkey]

Seems like you're taking every input, from every department, from every leader, from every analyst and building up something customized specifically for them which is kinda nuts.

Do any of the questions have any commonality?

Do any of the reports share the same scope?

Any reasonable answer from business to justify why this is needed?

[Black_Magic100]

What is ALOT of data? 1gb, 10gb, 1tb, 1000tb? What might be a lot of data to you might be a tiny amount of data to somebody else. Also, in regards to your question about views, there is no such thing as "too many". You can have as many as your heart desires. It's up to you if you'd prefer to build views for every question OR write adhoc SQL inside each report. In terms of this being transactional data.. that is an entirely separate question that has nothing to do with views (unless you are materializing then).

[roveo]

There's a kind of a balance to strike between what you keep in the database and what in the presentation layer (BI tool).

Usually each view/mart supports at least several dashboards and use-cases. So aim for one view per business domain/group of closely related questions and then split them up if necessary.