Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
auBusinessDaD
New Member

Choosing a Database

Some of you are lucky enough to ignore the below. For the rest of us, we don't have the luxury of accessing data directly. In this instance we have been looking at how we can pull data from peripheral systems into one accessible location.

 

The first step in this was working with each peripheral provider to (hopefully) have them provide us with a connection directly from Power BI. As you probably guessed, this was usually met with "not yet". So, we were left with one option. Use their API to pull the data down and store it ourselves for Power BI to access.

 

After confirming, and testing, our peripheral providers API to ensure we were able to pull the data we can to a very important question. How do we store this data for Power BI to access?

 

We have so many options, and biases, to push through. Staff will have their favourites, and I am sure Power BI will have its favourites. So how do we progress?

2 ACCEPTED SOLUTIONS

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. 

View solution in original post

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

Well, couple ways to narrow this down but the first question is

 

1. Store the data in the cloud or on-premises?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

What solution did you end up going with?

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors