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.
Thanks Greg,
As we are using Power BI to present the data (as I am here ;)) my thinking is cloud based. With that in mind I have been considering Azure SQL for its integration with Power BI - but am checking how easy it is to interact with from a local server running PowerShell.
My rationale is we can schedule PowerShell commands on a timer to pull the data and then push that data to its new home. It is data wrangling at its finest ...
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
- Greg_Deckler
Community Champion
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?
- tonysellars
Advocate II
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.