Some say that DBaaS (database as a service) -- aka cloud database -- is not suitable for Business Intelligence (BI), analytics (OLAP) or archiving.
Is this true? More generally, when DBaaS is the most effective technical choice?
It's effective technical choice when there's a lot of traffic, constant heavy usage.
The examples you listed make use of the database primarily as of storage facility, with search/processing being a far extra. Cloud is primarily about speed and CPU power, and this comes for a cost. There are far less expensive (and more reliable) archiving solutions that don't provide the kind of speed but surpass cloud in all other respects. BI, analytics etc are too not something you perform 5000 times a second 24 hours a day 7 days a week. It will be cheaper to build a dedicated server, then wait a couple seconds for each query to finish, than pay cloud maintenance for that purpose.
If you provide web applications - software-as-service over the net, for lots of clients, cloud is most welcome and gives most benefits, as it adapts to traffic that is fluctuating but always high. If the load is low, or zero most of the time, there are more cost-effective solutions.
Also if the data is very confidential, it's unwise to place it on 3rd party machines shared with random other companies, supervised by people out of your control...
One reason I use SQL Azure for my startup over a full install of SQL is that cloud databases typically let me focus on my application rather than focusing on keeping SQL Server up/updated/tuned/scaled/clustered/and every other admin work that would take me away from things.
Although we may need to do local SQL Server in the future depending on how much users trust other companies hosting their data, from a developer's standpoint it does a lot of things for me: