Forum Discussion
Choosing a Database
To throw a couple more items into the mix
- How directly usable is the data?If the data needs some massaging or a little something, something to get it to match up with other sources, then pulling it into a Database has some advantages in so far as being able to slap a view layer in there to handle some of that.
- How many Power BI models are being built off this (and how similar are they)?
Adding in an Azure Analysis Services cube can help centralize the model and measures if it starts to get large or you find yourself repeating the same things Power BI model to model.
Looking forward to see what you come up with. Since you are looking for other ideas, here are the cloud-based options in Azure that you could consider that all already have a connector for Power BI
- Azure SQL Database
- Azure SQL Data Warehouse
- Azure Analysis Services database
- Azure Blob Storage
- Azure Table Storage
- Azure Cosmos DB
- Azure Data Lake Store
- Azure HDInsight (HDFS)
- Azure HDInsight Spark
- Azure Kusto
Azure Blob Storage is ubiquitous in Azure and super inexpensive. Provides a massively scalable, durable, and highly available storage for data on the cloud, and serves as the data storage solution for modern applications.
Azure Table storage is also very inexpensive, think key/value pairs. Azure Table storage is a service that stores structured NoSQL data in the cloud, providing a key/attribute store with a schemaless design. Sign into your Storage account to create, update, and query tables and more.
Kusto is a log analytics cloud platform optimized for ad-hoc big data queries. Read more about it here: http://aka.ms/kdocs
Azure Cosmos DB is a globally distributed multi-model database service designed to enable you to elastically and independently scale throughput and storage across any number of geographical regions with a comprehensive SLA.
Let me know if you want any additional information.
auBusinessDaD,
You are OK to use Azure SQL or on-premises SQL Server as store. The difference in Power BI is that when you refresh the dataset. on-premises SQL Server requires a gateway but Azure SQL doesn't require gateway.
Regards,
Lydia
Thanks Lydia,
We are testing Azure SQL and believe it is the way to go - just testing out the best methods of uploading the data (csv format) to the database. From there Power BI is already connected to our Azure SQL to start consuming the data once it is in there.
Our first data set is only accessible using cURL, so we are using the Invoke-RestMothd in PowerShell to generate the csv of the data and then (in theory so far) use PowerShell to upload that csv into the database.
Data wrangling (in a hackish way), but we have no other option when the vendor doesn't have any other method for us to utilise.
Regards,
Daniel
I'll throw one other thought out here and that is, have you looked at Azure Analysis Services? The thinking here is that instead of putting information into a SQL database, which you then live query or import into a tabular cube (Power BI model), why not just start with the tabular cube?
To throw a couple more items into the mix
- How directly usable is the data?If the data needs some massaging or a little something, something to get it to match up with other sources, then pulling it into a Database has some advantages in so far as being able to slap a view layer in there to handle some of that.
- How many Power BI models are being built off this (and how similar are they)?
Adding in an Azure Analysis Services cube can help centralize the model and measures if it starts to get large or you find yourself repeating the same things Power BI model to model.
Thanks Team!
Love the ideas. We are going to try both Azure SQL and Azure Analysis Server and see which works for our situation. Will keep you posted as it progresses.
If you are reading this and have other ideas, share them ;) This conversation has been great to see ideas come forward.
If you are reading this with similar queries, stay tuned ;) I will have more updates with how we progress over the coming month.
Great stuff community, already paying off