My company is currently building an enterprise data warehouse in SQL server. We are looking at using PowerBI but I'm struggling to see how PowerBI works in the context of a data warehouse.
For instance what would it offer us, other than nicer looking reports, that Cognos, which we are using now, doesn't? How is it at handling immense amounts of data?
In the context of the Enterprise Data Warehouse Power BI has a number of options.
1) It can be the visualisation layer of your SSAS Data Models, users can connect and quickly create reports as it will sit over, not import data to the Power BI Report. Data processing is done on the server side, and can access huge data models/databases
2) Rather than create SSAS Data Models. Power BI can create a semi-semantic layer, as it is a branch of SSAS Tabluar technology. Your users can quickly deploy the reports, based directly on the database. You can use it in Direct Query mode, as with option 1, this sits over the database, and query processing is dome on the server side. You can import data, but it will be limited to 1GB dataset sizes. All report queries are served from the imported dataset, not the server. With Aggregation Mode you can combine import and direct query to sit over large databases
The real benefit is to enable self-service BI, to get the users to create their own reports. So you can mix strategic (built by the business) and tactical reports (user built). Power BI allows a quick process to mix and match data sources, for example data under your organisation domain, Databases, Cubes, Execl file etc, and data not under your domaim, webpages, API's, other sources. You can also have Power BI on-prem or in the cloud. On-prem will depend on the SQL Server license type, or it will be another cost. Power BI also fully integrates with O365, and Azure so depending on your application/tech stack, that may be a benefit. It also integrates very well with Power Apps, Power Automate so Power Users can build solutions without requests to IT or others.
This is from my personal experience. I have had a number of projects for enterprise scale customers, that have moved from Cognos (And other tech like Tableau), fully or in part, due to the cost and and the integration of Power BI into O365. End users liked the large knowledge base, the support from MS, and the rapid updating/roadmap of the technology. The most common question is, can it replace X tech. The answer is maybe, it will depend on your report requirements, and how it will integrate with your data sources. Other trends I've noticed, moved some work from IT/BI to the Power Users, particularity with Power Apps/Automate functionality.
Power BI is a lightweight ETL and modeling tool, so it is not just a visualisation tool. There are a number of blogs and articles that compare Power BI to Congnos, that seem biased, so it will be tricky to find a objective answer.