cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
auBusinessDaD Frequent Visitor
Frequent Visitor

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

Accepted Solutions
tonysellars Frequent Visitor
Frequent Visitor

Re: 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. 

Super User
Super User

Re: Choosing a Database

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


8 REPLIES 8
Super User
Super User

Re: Choosing a Database

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

 

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


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


auBusinessDaD Frequent Visitor
Frequent Visitor

Re: Choosing a Database

Thanks Greg,

 

As we are using Power BI to present the data (as I am here Smiley Wink) 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 ...

Moderator v-yuezhe-msft
Moderator

Re: Choosing a Database

@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.
auBusinessDaD Frequent Visitor
Frequent Visitor

Re: Choosing a Database

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

Super User
Super User

Re: Choosing a Database

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


tonysellars Frequent Visitor
Frequent Visitor

Re: 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. 

auBusinessDaD Frequent Visitor
Frequent Visitor

Re: Choosing a Database

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 Smiley Wink This conversation has been great to see ideas come forward.

 

If you are reading this with similar queries, stay tuned Smiley Wink I will have more updates with how we progress over the coming month.

 

Great stuff community, already paying off

Super User
Super User

Re: Choosing a Database

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 180 members 1,747 guests
Please welcome our newest community members: