How can I build a data warehouse?

[TroyStory]

Hi All, looking for some guidance on the way to approach the below.

I had previously built a report that was importing data from excel spreadsheets to a report that was published in the powerBI service.

There were a fair amount of calculated columns and I eventually ran into several issues, in order to solve these, I believe getting all of my data into a data warehouse and then using direct query will remove my scalability issues.

The issues experienced as the data set grew larger:

• Report refresh failed, memory issue
• Manually updating of spreadsheets

For this reason, I want to hear everyone's thoughts on getting our data, from SQL, spreadsheets and API's of our partners, into a data warehouse to be able to build reports without data issues.

Looking forwards to your insights.

Troy

[edhans]

Stop using Calculated Columns for one. See these links for their disadvantages, including memory issues.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables

Do all of your "calculated columns" either in SQL Views, or custom columns in Power Query. Don't use Calculated Columns unless there is a very VERY good reason to. New columns are generally a modeling issue, and DAX is not designed to model data. Power Query is.

You should generally stick with IMPORT, not DIRECT QUERY. The latter absolutely has its uses, but if your data isn in Excel, DQ isn't a fix. DQ is for millions and billions of records that need to be refreshed often (hourly or less). DQ also has limitations on what DAX and visuals can be used.

Yes, move your data from Excel to SQL Server, but you can still use Power Query to fold queries back to SQL Server with Import mode and pull data in relatively quickly. I do millions and tens of millions of records from SQL Server with refreshes that take less than 2-3 minutes.

[TroyStory]

Thank you for your input and insight.

The only other question I have, to build the data warehouse, should we host it as a separate database or is there value to pushing this data into azure.

If there is value going the azure route, where should I read up to know how to push the data regularly from SQL / Excel in this data warehouse and to do my queries off of this data warehouse.

Your assistance is much appreciated.

[edhans]

Depends on your need. Advantages to Azure SQL are it is in the cloud, has insane uptimes, and is available from anywhere you have internet access. Advantages to SQL on prem is you control the server. 

You'd need to do some general googling and research on pros and cons of each. But you can download for free SQL Server 2019 and install as a developer and start tinkering around with it. 100% free forever, but you are limited to 10 connections at a time (I think - that number may have changed) and it is strictly for development work, not production. But if you decide to keep it, just buy and apply a license. Then it becomes your production server. Nothing to upgrade or reinstall.

[TroyStory]

Thanks @edhans 

Lastly, I already have data in a production SQL database as well as in some spreadsheets.

What would the recommended method be to regularly update this information to the Data warehouse? Ideally this would be in azure to not have any impact on the production environment.

Is there a tool that can be used to simply push the data across or what do I need to look into to do this?

Thank you for all your answers, I really appreciate your time.

[edhans · Accepted solution]

I know very little about actual data sync. MS has a tool for this, and I know there are third party tools to sync between various data sources, like SQL, SharePoint List, etc. to Azure. You'd just need to do a bit of searching, or head to the Azure community on the MS tech forums. I know someone there will have hands-on experience with this and can assist.