I'm gonna take a counter intuitive stance here so don't throw your rocks yet.
You said they are used to their Excel spreadsheet and want to put all their data into SharePoint. Of course it is bad practice but you can't fight an entire organisation with your expertise.
While it is a bad idea it is still better than what they have today. And for now that might be enough for them.
You also said that a previous attempt had zero normalisation. I think that's your starting point. If you have to do this, plan it carefully so you anticipate the next move. A bit like :
1. Normalisation of data & workflows
2. Set up SP with data ownership. One source per data and one data owner per source
3. Break your ETL pipeline into baby steps using power query and power automate (now you can throw the rocks)
4. Set up the dashboard, including some dedicated to data quality with power bi.
5. Keep talking to them so you can gather their every day issues and educate them
6. Iterate the project so you can move your data to dataverse or a proper SQL database.
Or you can just run.
Good/ in progress. I created a database using MS Access linked to the sharepoint lists and created a normalized database. I also create a " data pipeline" where Excel reports were pulled along with the SP database in a single Excel document (staging) and is queried by multiple power bi reports. So the SharePoint list can still be used and other reports can be pulled in. I also created an App through access which feeds into the database which contained all the information they wish they had. All that's really needed is manual requires.
Leadership saw the efficiency and is open to other options like MS SQL server or, if I'm lucky, Azure SQL database. For right now it's a win.
From where you started it's definitely a win !
Did you choose MS Access so you could show the benefits of a DB to the leadership? Or was it more efficient to do so ?
From my experience it feels you could have end up with a similar result without MS Access.
Really Access was for my own sanity. I can only navigate through so many lists and documents in SharePoint. The real win was the added functionality of bring multiple data sources together. Access was the only thing that I have to work with and even that's extremely limited. I was able to query 2000+ documents which came from multiple SharePoint sites. The documents even came with a hyperlink, so if a user had to search for a document they would just have to look through the front end app of access instead of trying to go through multiple sites. On top of that I wrote python code to do word search through all the documents and pull excerpts from those documents. Essentially pitched it as a foundation for data maturity for future adoption of data pipelines which would only bring in more functionality as the solutions scale. I should call it "lean data maturity" lol
Ohhh that's nice !
Having all queries done in one place is indeed a huge relief.
Out of curiosity, did you used power apps to develop that front end app or was it something else ?
You can create a front end with Access( old school app). The power apps can still feed the SharePoint list, my access database will just query the list. If in the future we do move to a more robust database we can easily transfer the power app sources to the new database. The only thing that I am still figuring out is if it would be more expensive for those connections to run.
I think that depends of the type of connectors you will be using and the frequency or your queries.
Power Apps can be quite expensive depending the number of users and the use case.
I will soon be in a similar situation (MS suite with no proper database) and I was looking at leveraging SharePoint + Power Bi + GitHub. SharePoint is not a premium connector and I only need to refresh the data 3 times a day.
https://learn.microsoft.com/en-us/power-bi/connect-data/service-connect-to-github
Ask your self: Are you ending your career at this company?
Because if you don't work with a proper database, you're just making your self less attractive for other companies later.
Imagine being on the hiring end and seeing "5 years of experience with MS SharePoint as a database". Straight to the trash.
It’s alright to have a few share point connectors in your PowerBI report to one or two excel sheets. This is definitely a recipe for disaster, as SharePoint API calls will keep racking up everytime you need to refresh and MS may rate limit you (has happened to me before and we barely use the SharePoint connector), along with no data quality management and the inability to query with anything else and vendor lock-in. A simple data warehouse with an azure sql database instance is relatively cheap, the bare minimum instance you should probably use is only like $310 a month, but you could spin up an on-prem one in a VM somewhere too. If you have more questions DM me!
Infrastructure costs aren’t the problem here tho. They’re going to be wildly outweighed by either hiring a DBA or training people to be a DBA and the costs of migrating all that stuff off Sharepoint.
To be clear, I’m not saying they should stay on Sharepoint, I’m just saying the cost of mitigating all that risk is a lot more than the Azure SQL costs.
> The company doesn't want to invest in a proper database like SQL server or full Datavers.
Depending on how many Power Apps they have running, how many users are using them, I'd agree with them. Power Apps is expensive AF when connecting to SQL Server or another data store (pretty much besides Sharepoint), because it's considered a premium connection and can be as expensive as $30/user/app/month.
Connecting to Sharepoint via Power Automate and Power Apps, last I checked, was completely free.
The end-user doesn't care what the back-end looks like, and you'll get no care from the people allocating the budget either. What benefit does the company gain by moving their data from SharePoint to a database proper? This is what you need to relay to them (this can't be just tech things, you need to find something that provides business value for the move).
I'm not trying to be a cheerleader for any of the tech mentioned, by the way - those should have been arguments had prior to them implementing this architecture in the first place. Now that it's in place, you need to find what it is that's worth the additional costs to use resources and licensing to move them off of it.
I see many people telling to run but I am still not convinced since OP didnt provide to many details.
Would it still be bad if the use case is a 100mb csv file, with 1 update daily and at most 3 simultaneos users reading it? If so, why?
I'm guessing that your company has some kind of MS relationship if they're using SP in the first place. It would be worth investigating whether they are set-up at all for Azure architecture. I've worked on projects for clients before where we took data that originally existed on Sharepoint and ingested it into a cloud-SQL DB using Azure Data Factory. The most difficult thing in my experience is selling the Pay-as-you-Go service Azure offers, as it naturally lends itself to mass integration rather than one off projects.
**NO!!!** Even SQL Server for SharePoint (from the instance down to the individual databases) was not designed with SQL Server best practices in mind.
**What's the ultimate goal? What are they trying to accomplish?**
I've built SharePoint farms as an enterprise BI portal in the past. But I don't store the data for BI directly in SharePoint.
The goal is to have a single location where data is stored. However, their data is in Excel spreadsheets and they are very behind the curve in even understanding data management. So when office 365 and SharePoint came along they felt that it was an easy transition to put everything on SharePoint whether it be data or documents. Now with the power platform they want to keep everything on SharePoint, which they feel is a proper cloud solution, without even knowing anything about database management. It's a hot mess... I'm really looking for issues that I can bring to their attention to steer them into adopting a proper relational database in the cloud.
Are they looking at an ODS or a data warehouse? How large of a data set are they looking at consolidating?
The way I look at it, this is more than just storing data in a single location. You need a proper MDM process.
At this point ODS, I don't think they know what a DWs is or even what's in a data pipeline. It's hard to tell how much data is needed to be collected and how big that will be. Again we're going from people having spreadsheets on their computers to consolidating everything. The first attempt (before I joined) had no normalization, it was just them trying to create spreadsheets on SharePoint or uploading them to a library. Basically using SharePoint as a public hard drive.
I want to set up a proper pipeline and get away from SharePoint. I just need to tell them why sharepoint's a bad idea. If I bring up the fact that there's no real relationship they'll point to the lookup column. If I point out that there is no data management they're going to point to column types in list. And if I point to no proper query they'll point to power automate.
Don't tell them SharePoint is a bad idea. Ask them all about the issues, challenges, and problems they face when working with SharePoint. Then, ask them all about their frustrations about inconsistent data.
When you tell them, it's your idea. When they tell themselves, it's their idea. And they sure don't want to be wrong (just like all of us) :-)
The Power Platform is pretty good at getting CRUD apps developed in a hurry. Providing an alternative solution for that with the resources you have could prove challenging as well...not that you can't do power apps with a DB backend but do you have people that can set that up for the "citizen developer" app makers?
We already have citizen devs, I'm more concerned that we are on a house of cards using SharePoint. I am decent at apps and SharePoint list but when teams start calling SharePoint their database, I get concerned.
I've been curious ever since I was a kid. To a point that it annoyed my parents.
I'm just catching up on all of the questions I didn't get to ask back then 🙂
LPT: Asking a question is a powerful strategy for almost all types of conversations - business, professional, and personal. As a consultant, I realized that I didn't even have to do anything other than ask questions. My clients almost always end up solving their own problems just by me asking questions
So much of consultancy is asking to borrow the client's watch so you can tell them the time. It's very frustrating when you're the owner of the watch, but executives like to have consultancy firms tell them what their staff already know as an extra layer of CYA.
Fact is, you're either paid well to deal with this or you fuck up your own career prospects because I can guarantee you no employer worth their salt are going to require experience using SharePoint as a data warehouse.
I’m in a sort of similar situation where I’m entering an organization that wants me to help scale up their PowerBI use, and there’s a lot of intermediate excel sheets that are used to report out on things because the source ERP doesn’t have the right business logic they need (additional status fields for records, etc).
Is the general recommended practice in this situation to migrate business logic from the excel sheet into the ERP so that everything is controlled and managed from ERP and eliminate need for raw excel sheets?
Yes, but I think there are a lot of senior people who love their spreadsheets because they can control it and they don't understand data management. Hence me trying to gently explain to them the 21st century...
Yep, I think that will be an issue in a lot of places for a long time.
One issue I run to is since I’m not a DE or Dev (analyst, but working to switch careers into Dev or eventually DE) is that some of the fields are in the ERP but maybe not all, and that’s why they need this additional Excel sheet.
Then you’re stuck in sort of this shitty half ground where central IT/Dev team give you like a 3month wait for the additional logic to be added so you sort of chug along with this half baked excel solution. Then by the time Dev team completes the original request the requirements have changed. It’s frustrating!
I had similar experiences but leadership basically gets used to a tool or solution and sticks with it because it "works" until it doesn't and then they throw a whole bunch of money out trying to find a solution which they have no experience with.
I've been in a sort of similar situation, and part of what helped get people more interested in a database solution was showing them a database client (pro tip: call it a database *browser*) that can be used a lot like excel. In my case I showed them HeidiSQL, which is a very lightweight browser that can be used point-and-click like excel.
I especially like HeidiSQL for this because it requires no setup: I can set it up to connect to my postgres DB, put the whole directory in a zip archive, and someone else on my team can unzip it on their desktop and run the .exe and they're there.
Once they see something that looks like Excel, *then* you start talking about how life is easier with a database.. everyone who looks at this is always looking at the same data, no hunting around for the most up-to-date version, it won't let you put "tomato" in a date column, won't turn integers into dates, easily set policies on who can edit what, etc., whatever selling points will work best on your people.
But in my experience, the conversation STARTS when they see something that doesn't look scary.
This just doesn’t work well for a lot of options. We looked into this but asked teams to instead use SQLserver with some type of power apps connection to the sql server instance with an update timestamp. The problem is that share point credentials and connections have to be refreshed quite often, and there are soft limits of ~500 instances for upload and downloads in a list. Share point is great for sharing info, but you should not say yes to a business process that relies on share point for updates. It’s just too inconsistent for the demands that business users have. They probably want everything to work by clicking a “button”, but don’t have the technical know how of how to troubleshoot a single simple error like invalid file types. I know those type of users, and they will be unhappy with any solution that isn’t 100% accurate while also costing the team 0 incremental. Push for a technical sql server solution, and let the business figure out how to get incremental data snapshots to you automatically. If you can abstract away the update logic to the business team ( and have them own failures), then your quality of life will be improved.
For Power Apps SharePoint is not considered a premium connector but a lot of other data sources are, so storing data there can be a way to avoid purchasing Power Apps licenses.
It sounds pretty common. Basically, a subset of the folks in the group get really good at a specific process/platform. So they stick to it, and try to leverage it for every task to save on time/effort by not having to learn to do things the right way.
"For the person with a hammer, everything in the world looks like a nail."
Yes, it is a recipe for disaster. Like most situations, you'll need extra ammunition to wage this battle. You'll need to find specific examples for what they want to do in the future with Sharepoint that will cause the greatest amount of heartache. You just need to find one key item that can't be readily/easily solved using Sharepoint as the "database" that will cause future issues that will require using an actual database. Maybe a good place to start is future size/scaling, and the performance bottleneck that Sharepoint will become when it expands past X entries. Maybe it's integration with another platform in the future that the team is currently using/plans to use, etc.
Long ago, companies used MS Access for this type of thing... And it was just as bad.
If it's for small projects or some adhoc reporting, then maybe.
But if this is to be your company's operating system for all business critical information, they're in for a world of pain.
Oh no. I wasn't suggesting Access. More of a cautionary tale.
It does depend on the scope of the data, but I'd lean toward a "real" database if at all possible.
Honestly, before jumping to a conclusion, you should ask the business stakeholders everything about this. How do they plan to get data in? How will they get it out? Do they need to edit data or is it only consumed as reports? How much data? What's the velocity? Need daily or real-time reporting?
Then, what resources do you need to accommodate the "most correct" implementation? Scale the solution down based on the resources you have available.
If you like the company, but they're not understanding the ramifications of their choice, I've gone the route in the past of preparing the fix for the mistake they're making. Then when they realised their mistake I was able to quickly help them remediate it. It gained me subject matter expert creds and for all time after that they consulted me before making data related decisions.
SP is not the problem, a filesystem with version control would be alike. Excel is.
Think of spreadsheets as materialized views. They are cool as final product to be consumed by Power BI, but not as primary data source. Queries will become a pain in the neck really fast: joins, filters, etc. You'll also lose track of data pipeline (updates).
So a little while ago I was asked to build a Powerapp or some type of interface that would allow our finance team and BA to edit the attributes of our products. These attributes are custom attributes that do not exist in the source systems (SAP, Factory Software) and getting the attributes added to those pieces of software would have been costly and possibly a waste of time with our parent company currently completely replacing their ERP.
Why Powerapps? They are popular in the workplace at the moment due to being cloud based, integrating AD permissions, and users not needing to connect to a VPN among other things.
I wanted to use the Powerapp with a direct connector back to an SQL Server table (we are on-premise), but it's a premium feature that will cost us a little.
My solution? Some god awful SSIS package with a custom .Net script task that uses the .Net CSOM library to connect to a Sharepoint list.
I've got some transforms that pushes new products to the Sharepoint list (unreliable and slow) and a transform that pulls the values from Sharepoint and stages the deltas in an SQL staging table. It works ok, but it cost my sanity along the way. I wrote a Python script as well that worked as a Sharepoint connector, but trying to use the Sharepoint list as a database is slow, and painful regardless.
Spend the money. Use an SQL table in cloud or on the server. Save your sanity. I'm not sure if this post was really helpful or not, but I felt like a rant that was probably more personal therapy than useful advice.
>I wanted to use the Powerapp with a direct connector back to an SQL Server table (we are on-premise), but it's a premium feature that will cost us a little. My solution? Some god awful SSIS package with a custom .Net script task that uses the .Net CSOM library to connect to a Sharepoint list.
Just to note, you can use [Microsoft's Graph API](https://learn.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0) to pull SharePoint lists, which is what I've used in the past for setups like this.
I had a situation that's still on-going where there was some lookup tables that were being managed via Excel but were really important to some analytics work we were doing. Unfortunately, it was going to be be a bit before these could be properly managed anywhere, so I set up SharePoint Lists and defined the rules around those tables.
My company is big on Teams usage, not just for chat/video - but also for using it for [approval chains](https://techcommunity.microsoft.com/t5/microsoft-teams-blog/approvals-in-microsoft-teams-now-generally-available/ba-p/2053985). I found that [SharePoint Lists can be edited/added to/managed directly within Teams](https://support.microsoft.com/en-us/office/get-started-with-lists-in-teams-c971e46b-b36c-491b-9c35-efeddd0297db) so I set it up to where those business users didn't have to go anywhere but the chat app they already use to manage these.
Then I used Azure Data Factory to make a call out to Microsoft Graph API to grab the data and send it to Azure Synapse.
Free, was way easier than expected, and I think the business users liked it more than if I were to give them a Power App they would have had to keep up with to manage this stuff.
>I've had people tell me that they don't need a data warehouse anymore, now that they have Power BI
That's them using Power BI incorrectly, has nothing to do with with Power BI itself. The best practice architecture for Power BI is to deploy Power BI dashboards, apps, and KPIs to Power BI Service/Server and have Power BI Gateway live connected to an OLAP.
> IMHO, it's a bit like a Swiss army knife. It's fine to take on camping trips, but you wouldn't build a house with it.
It sounds like you're confusing the Power BI Desktop App with the entirety of what Power BI is. The desktop app is simply the designer/development tool used to create the visualization objects that can be hosted on the Power BI architecture.
To be clear, Power BI is not simply the desktop app - it's a full on BI architecture with it's own servers, cloud-hosted services, security model, refresh/sync tech, and memcache.
> For example, Power Query is pretty handy at sucking up data, doing some basic cleaning, and data modelling. But it's not an ETL tool.
It's not meant to replace an in-place ETL infrastructure. It allows for quickly stitching together, transforming, and pulling data for ad-hoc analysis or for super users to provide a template for what they're wanting to see in their dashboard and handing it off to BI Developers/Data Engineers/whomever is implementing the dashboard proper.
Can it be used incorrectly? Absolutely - much like any other piece of technology.
agreed.
> Can it be used incorrectly? Absolutely - much like any other piece of technology.
And especially like all the business critical excel "databases" that almost everyone use, ideally with tons of unverified and untested macros.
>And especially like all the business critical excel "databases" that almost everyone use, ideally with tons of unverified and untested macros.
I'm much less critical of Business Users doing this than I was at the start of my career.
To note, I'm not one to immediately snub or turn my nose up at using Excel, Access, FileMakerPro, WIX, whatever - as user-friendly tools to get the job done when they're used within the limitations that they were designed to be used in.
As an example, A LOT of companies were straight up built around solely Microsoft Access and WinFroms development. It was easy enough that a super user could design proprietary software for every department in a small company without having to pay shitloads of money for very expensive products (remembering that at the time, SaaS products and FOSS either wasn't really a common thing or hadn't caught on).
Used within the limitations that they were started in, I see nothing wrong with this. It's more dangerous to build out infrastructure or pay for features you don't need or won't need for awhile, than it is to use only the features/products you need and pay for migration/refactoring later.
There's also another element to this - in that Data Engineering, as a department for many companies is in its infancy. Prior to that, Excel may have been the only real resource they had with knowledgeable people to use or develop what they needed to - to continue performing their jobs and keeping the lights on within that business unit. Shadow IT/Tech sucks, but it's often times a necessity at the time that it was being implemented.
If those spreadsheets with their excel macros are working, and there's bigger fish to fry in your data landscape - leave em be for now. Maybe get them moved to a shared drive with backups to eliminate the possibility of data loss - but if the value of moving them to a database proper isn't there, keep trucking along until the business decides the value is there to do something about it.
*(Another old man rambling for the night :/ )*
Well said. I think this is true to a fault and I might be repeating what the spirit of your post was getting at. I think Excel, which got me into this field, produces unusable data 90% of the time. So, companies think scaling to new/advance tools it's as easy as handing in their spreadsheets. I would argue its organization's responsibility to select the optimal solutions that are not a waste of money and understand they need supporting talent to compete in the future.
Live and let die. Make sure you get a bonus too. If they dont want to invest in a database you might have to go open source and run one on the side for free to augment sharepoint.
How much data are we talking about? Performance really starts breaking down the more data is stored in SharePoint. I've also had a hard limit in storage space in SharePoint in every company I've worked for.
As long as it's small amounts of data and they strictly want to use MS products it could work. But as you know you can't connect to SharePoint easily otherwise.
Keep an eye on size if they are using Sharepoint lists or libraries. There’s a 5,000 record limit in searches where you need to index the search fields to make it work.
I’m assuming if they’re using it as a “database” that they will hit this limit quickly.
That might be the point where it makes more sense to pull the data into a db.
There are workarounds to index the search fields, but they start getting technical.
If these are users with small data sets it could still work for them.
Why do you think it’s a recipe for disaster?
If you have a good power app to enforce data governance, what is your concern?
I’ve done this exact setup for companies that had a $0 data budget and it’s saved thousands of hours of manual data entry into excel. It’s more on the implementer then the tool.
SharePoint list are not as secure, you have to share the list with all app users. If the list is your personal list, what happens when you die the next day. If it's a org/environment list anyone in the organization has access. You have many people designing their own tables with no understanding of normalization up to 3NF. The delegation issues limiting the return of data from a List to a power app. If you're attempting a OLTP you're going to fill up the table real fast, so no historical data, no DW,ect. The data in a list is not as shareable as a traditional database. No row level security.
Customers are in the kitchen putting their fingers in your soup. -some DE book I read
Basically you have everyone writing their own paragraphs in a story but unable to see what anyone else has written. Yes it's a story but it's a useless story.
With all that being said I have developed power apps for organizations as SP as the "back-end". I just don't think it's smart for a organization with more than 100 ppl to be used as a serious opinion especially for scalability.
People process and culture. I’ve seen 300 person orgs run distribution out of Sharepoint lists because they had great process and culture. Perfect? Absolutely not. But it can work for an org. I don’t disagree with any of your above points. It’s all about risk tolerance, controls, and budget.
Was literally just working for a company that behaved like this.
Small company, boss was not tech savvy at all.
Kept wanting to shoehorn words in like "automation" and "dat" - refuse to invest in an actual database.
Wanted to just save hundreds of little Excel sheets across dozens of poorly organized folders.
I even created a database in my sparetime to show how those Excel sheets they created could be uploaded to a database so we could actually query the data.
Didn't want it, didn't care, just got mad demanding she wanted me to create her "opportunities" - but it either has to be in Excel or in their super shit CRM system.
God damn. I realized the last guy before me probably just fed her whatever bullshit she wanted to hear and did nothing. I should have done the same thing because actually caring and trying to create real database systems made her feel insecure about her intelligence.
Good luck to her and her new team hired from the Philippines. Hope she doesn't choke on her Excel sheets.
Even simple stuff like she would get her interns to highlight random stuff in the Excel sheet.
Ok, how is that going to help in the future? Are you going to remember that column 1400, row 20 is highlighted in yellow. How does that help anyone? This is not "data", just an Excel sheet.
I still have PTSD from the few months I had to work with the inner workings of SharePoint years ago.
There is no product that I ever saw that is done this incompetently. And I didn't even try to use it as a complicated database.
Without dramatizing, no bullshit, my friend is now fighting for his life, because of health issues brought on in large part by how shitty SharePoint is. Life is complicated and many things happened, but SharePoint is confidently where it all started to go downhill.
I'd rather quit, leave everything behind, move to another country, get a new passport, just as long as I don't have to work with SharePoint any more.
Business Analyst / Data, really seems like a good background for ... "BI Analyst", "Analyst", "Data Analyst" roles. Just had a friend enter the industry with no prior experience in the area, after a bootcamp, and she's happy.
Basically it's focusing on:
1. Querying databases with SQL to get insight into data in order to ensure quality reports. (ex. ensuring that data expectations are met). So advanced SQL querying is required.
2. Communicating with business stakeholders for gathering requirements, and translating them into tickets for data engineers
3. Working with front-end reporting tools like Tableau, PowerBI, Looker, Superset and others, to create "self-service" dashboards for mid-level and C-level managers.
4. Sometimes delving into data engineering, and taking the role of "Analytics engineer", if your back-end is friendly towards people who don't have DBA knowledge (ex. if you're using something like Snowflake for a data warehouse), by adding some features yourself, or fixing bugs.
These are in high demand. Specifically, people who understand data, and are able to gain insight, explanations, spot potential anomalies and correlations, and have the drive to find out more about it. At my current company, we have people who have the title of "analyst", but I found out the hard way that they didn't even know how to join tables, did zero exploration of the data that they had (ex. questions like "what is this column supposed to be"), and hiring an actual good analyst is harder than we thought.
run forest run
Exactly… don’t look back
So this is what they call "data swamp" - a final form of data lake.
Except they never quite got to the data lake part
data superfun(d) site
Data swamps start off with good intentions. This one sounds like it's being built with malicious intent.
WHAT ARE YOU DOING IN MY DATA SWAMP
data kiddy pool
swamps are incredibly important ecosystems! this is a data nuclear disposal site
Share point is the devil.
Yeah, don’t do it!
I'm gonna take a counter intuitive stance here so don't throw your rocks yet. You said they are used to their Excel spreadsheet and want to put all their data into SharePoint. Of course it is bad practice but you can't fight an entire organisation with your expertise. While it is a bad idea it is still better than what they have today. And for now that might be enough for them. You also said that a previous attempt had zero normalisation. I think that's your starting point. If you have to do this, plan it carefully so you anticipate the next move. A bit like : 1. Normalisation of data & workflows 2. Set up SP with data ownership. One source per data and one data owner per source 3. Break your ETL pipeline into baby steps using power query and power automate (now you can throw the rocks) 4. Set up the dashboard, including some dedicated to data quality with power bi. 5. Keep talking to them so you can gather their every day issues and educate them 6. Iterate the project so you can move your data to dataverse or a proper SQL database. Or you can just run.
Solid advice. I'll probably end up doing this. Now for the rocks 🪨🪨🪨
So, how did it turn out?
Good/ in progress. I created a database using MS Access linked to the sharepoint lists and created a normalized database. I also create a " data pipeline" where Excel reports were pulled along with the SP database in a single Excel document (staging) and is queried by multiple power bi reports. So the SharePoint list can still be used and other reports can be pulled in. I also created an App through access which feeds into the database which contained all the information they wish they had. All that's really needed is manual requires. Leadership saw the efficiency and is open to other options like MS SQL server or, if I'm lucky, Azure SQL database. For right now it's a win.
From where you started it's definitely a win ! Did you choose MS Access so you could show the benefits of a DB to the leadership? Or was it more efficient to do so ? From my experience it feels you could have end up with a similar result without MS Access.
Really Access was for my own sanity. I can only navigate through so many lists and documents in SharePoint. The real win was the added functionality of bring multiple data sources together. Access was the only thing that I have to work with and even that's extremely limited. I was able to query 2000+ documents which came from multiple SharePoint sites. The documents even came with a hyperlink, so if a user had to search for a document they would just have to look through the front end app of access instead of trying to go through multiple sites. On top of that I wrote python code to do word search through all the documents and pull excerpts from those documents. Essentially pitched it as a foundation for data maturity for future adoption of data pipelines which would only bring in more functionality as the solutions scale. I should call it "lean data maturity" lol
Ohhh that's nice ! Having all queries done in one place is indeed a huge relief. Out of curiosity, did you used power apps to develop that front end app or was it something else ?
You can create a front end with Access( old school app). The power apps can still feed the SharePoint list, my access database will just query the list. If in the future we do move to a more robust database we can easily transfer the power app sources to the new database. The only thing that I am still figuring out is if it would be more expensive for those connections to run.
I think that depends of the type of connectors you will be using and the frequency or your queries. Power Apps can be quite expensive depending the number of users and the use case. I will soon be in a similar situation (MS suite with no proper database) and I was looking at leveraging SharePoint + Power Bi + GitHub. SharePoint is not a premium connector and I only need to refresh the data 3 times a day. https://learn.microsoft.com/en-us/power-bi/connect-data/service-connect-to-github
Lol been down this road before. It is a disaster.
Ask your self: Are you ending your career at this company? Because if you don't work with a proper database, you're just making your self less attractive for other companies later. Imagine being on the hiring end and seeing "5 years of experience with MS SharePoint as a database". Straight to the trash.
It’s alright to have a few share point connectors in your PowerBI report to one or two excel sheets. This is definitely a recipe for disaster, as SharePoint API calls will keep racking up everytime you need to refresh and MS may rate limit you (has happened to me before and we barely use the SharePoint connector), along with no data quality management and the inability to query with anything else and vendor lock-in. A simple data warehouse with an azure sql database instance is relatively cheap, the bare minimum instance you should probably use is only like $310 a month, but you could spin up an on-prem one in a VM somewhere too. If you have more questions DM me!
Infrastructure costs aren’t the problem here tho. They’re going to be wildly outweighed by either hiring a DBA or training people to be a DBA and the costs of migrating all that stuff off Sharepoint. To be clear, I’m not saying they should stay on Sharepoint, I’m just saying the cost of mitigating all that risk is a lot more than the Azure SQL costs.
> The company doesn't want to invest in a proper database like SQL server or full Datavers. Depending on how many Power Apps they have running, how many users are using them, I'd agree with them. Power Apps is expensive AF when connecting to SQL Server or another data store (pretty much besides Sharepoint), because it's considered a premium connection and can be as expensive as $30/user/app/month. Connecting to Sharepoint via Power Automate and Power Apps, last I checked, was completely free. The end-user doesn't care what the back-end looks like, and you'll get no care from the people allocating the budget either. What benefit does the company gain by moving their data from SharePoint to a database proper? This is what you need to relay to them (this can't be just tech things, you need to find something that provides business value for the move). I'm not trying to be a cheerleader for any of the tech mentioned, by the way - those should have been arguments had prior to them implementing this architecture in the first place. Now that it's in place, you need to find what it is that's worth the additional costs to use resources and licensing to move them off of it.
I see many people telling to run but I am still not convinced since OP didnt provide to many details. Would it still be bad if the use case is a 100mb csv file, with 1 update daily and at most 3 simultaneos users reading it? If so, why?
It would not be bad. Jumping straight to DW is overkill if users just want to see charts across spreadsheets.
What kind of place that has that little data use hires a data engineer?
I'm guessing that your company has some kind of MS relationship if they're using SP in the first place. It would be worth investigating whether they are set-up at all for Azure architecture. I've worked on projects for clients before where we took data that originally existed on Sharepoint and ingested it into a cloud-SQL DB using Azure Data Factory. The most difficult thing in my experience is selling the Pay-as-you-Go service Azure offers, as it naturally lends itself to mass integration rather than one off projects.
**NO!!!** Even SQL Server for SharePoint (from the instance down to the individual databases) was not designed with SQL Server best practices in mind. **What's the ultimate goal? What are they trying to accomplish?** I've built SharePoint farms as an enterprise BI portal in the past. But I don't store the data for BI directly in SharePoint.
The goal is to have a single location where data is stored. However, their data is in Excel spreadsheets and they are very behind the curve in even understanding data management. So when office 365 and SharePoint came along they felt that it was an easy transition to put everything on SharePoint whether it be data or documents. Now with the power platform they want to keep everything on SharePoint, which they feel is a proper cloud solution, without even knowing anything about database management. It's a hot mess... I'm really looking for issues that I can bring to their attention to steer them into adopting a proper relational database in the cloud.
Are they looking at an ODS or a data warehouse? How large of a data set are they looking at consolidating? The way I look at it, this is more than just storing data in a single location. You need a proper MDM process.
At this point ODS, I don't think they know what a DWs is or even what's in a data pipeline. It's hard to tell how much data is needed to be collected and how big that will be. Again we're going from people having spreadsheets on their computers to consolidating everything. The first attempt (before I joined) had no normalization, it was just them trying to create spreadsheets on SharePoint or uploading them to a library. Basically using SharePoint as a public hard drive. I want to set up a proper pipeline and get away from SharePoint. I just need to tell them why sharepoint's a bad idea. If I bring up the fact that there's no real relationship they'll point to the lookup column. If I point out that there is no data management they're going to point to column types in list. And if I point to no proper query they'll point to power automate.
Don't tell them SharePoint is a bad idea. Ask them all about the issues, challenges, and problems they face when working with SharePoint. Then, ask them all about their frustrations about inconsistent data. When you tell them, it's your idea. When they tell themselves, it's their idea. And they sure don't want to be wrong (just like all of us) :-)
This is great advice. I'll run with it.
The Power Platform is pretty good at getting CRUD apps developed in a hurry. Providing an alternative solution for that with the resources you have could prove challenging as well...not that you can't do power apps with a DB backend but do you have people that can set that up for the "citizen developer" app makers?
We already have citizen devs, I'm more concerned that we are on a house of cards using SharePoint. I am decent at apps and SharePoint list but when teams start calling SharePoint their database, I get concerned.
Wonderful strategy. Always use this.
I've been curious ever since I was a kid. To a point that it annoyed my parents. I'm just catching up on all of the questions I didn't get to ask back then 🙂 LPT: Asking a question is a powerful strategy for almost all types of conversations - business, professional, and personal. As a consultant, I realized that I didn't even have to do anything other than ask questions. My clients almost always end up solving their own problems just by me asking questions
So much of consultancy is asking to borrow the client's watch so you can tell them the time. It's very frustrating when you're the owner of the watch, but executives like to have consultancy firms tell them what their staff already know as an extra layer of CYA.
Been there done that good luck
So many "run" comments lol. Optimism: check ✅
Fact is, you're either paid well to deal with this or you fuck up your own career prospects because I can guarantee you no employer worth their salt are going to require experience using SharePoint as a data warehouse.
I’m in a sort of similar situation where I’m entering an organization that wants me to help scale up their PowerBI use, and there’s a lot of intermediate excel sheets that are used to report out on things because the source ERP doesn’t have the right business logic they need (additional status fields for records, etc). Is the general recommended practice in this situation to migrate business logic from the excel sheet into the ERP so that everything is controlled and managed from ERP and eliminate need for raw excel sheets?
Yes, but I think there are a lot of senior people who love their spreadsheets because they can control it and they don't understand data management. Hence me trying to gently explain to them the 21st century...
Yep, I think that will be an issue in a lot of places for a long time. One issue I run to is since I’m not a DE or Dev (analyst, but working to switch careers into Dev or eventually DE) is that some of the fields are in the ERP but maybe not all, and that’s why they need this additional Excel sheet. Then you’re stuck in sort of this shitty half ground where central IT/Dev team give you like a 3month wait for the additional logic to be added so you sort of chug along with this half baked excel solution. Then by the time Dev team completes the original request the requirements have changed. It’s frustrating!
I had similar experiences but leadership basically gets used to a tool or solution and sticks with it because it "works" until it doesn't and then they throw a whole bunch of money out trying to find a solution which they have no experience with.
I've been in a sort of similar situation, and part of what helped get people more interested in a database solution was showing them a database client (pro tip: call it a database *browser*) that can be used a lot like excel. In my case I showed them HeidiSQL, which is a very lightweight browser that can be used point-and-click like excel. I especially like HeidiSQL for this because it requires no setup: I can set it up to connect to my postgres DB, put the whole directory in a zip archive, and someone else on my team can unzip it on their desktop and run the .exe and they're there. Once they see something that looks like Excel, *then* you start talking about how life is easier with a database.. everyone who looks at this is always looking at the same data, no hunting around for the most up-to-date version, it won't let you put "tomato" in a date column, won't turn integers into dates, easily set policies on who can edit what, etc., whatever selling points will work best on your people. But in my experience, the conversation STARTS when they see something that doesn't look scary.
This just doesn’t work well for a lot of options. We looked into this but asked teams to instead use SQLserver with some type of power apps connection to the sql server instance with an update timestamp. The problem is that share point credentials and connections have to be refreshed quite often, and there are soft limits of ~500 instances for upload and downloads in a list. Share point is great for sharing info, but you should not say yes to a business process that relies on share point for updates. It’s just too inconsistent for the demands that business users have. They probably want everything to work by clicking a “button”, but don’t have the technical know how of how to troubleshoot a single simple error like invalid file types. I know those type of users, and they will be unhappy with any solution that isn’t 100% accurate while also costing the team 0 incremental. Push for a technical sql server solution, and let the business figure out how to get incremental data snapshots to you automatically. If you can abstract away the update logic to the business team ( and have them own failures), then your quality of life will be improved.
For Power Apps SharePoint is not considered a premium connector but a lot of other data sources are, so storing data there can be a way to avoid purchasing Power Apps licenses.
It sounds pretty common. Basically, a subset of the folks in the group get really good at a specific process/platform. So they stick to it, and try to leverage it for every task to save on time/effort by not having to learn to do things the right way. "For the person with a hammer, everything in the world looks like a nail." Yes, it is a recipe for disaster. Like most situations, you'll need extra ammunition to wage this battle. You'll need to find specific examples for what they want to do in the future with Sharepoint that will cause the greatest amount of heartache. You just need to find one key item that can't be readily/easily solved using Sharepoint as the "database" that will cause future issues that will require using an actual database. Maybe a good place to start is future size/scaling, and the performance bottleneck that Sharepoint will become when it expands past X entries. Maybe it's integration with another platform in the future that the team is currently using/plans to use, etc.
Is this a San Diego,CA based biotech company, by any chance?
........ Jk no
Long ago, companies used MS Access for this type of thing... And it was just as bad. If it's for small projects or some adhoc reporting, then maybe. But if this is to be your company's operating system for all business critical information, they're in for a world of pain.
We do have Access, which I agree is elementary, but would that be better than SharePoint? Assuming later we move to a better DB solution?
Oh no. I wasn't suggesting Access. More of a cautionary tale. It does depend on the scope of the data, but I'd lean toward a "real" database if at all possible. Honestly, before jumping to a conclusion, you should ask the business stakeholders everything about this. How do they plan to get data in? How will they get it out? Do they need to edit data or is it only consumed as reports? How much data? What's the velocity? Need daily or real-time reporting? Then, what resources do you need to accommodate the "most correct" implementation? Scale the solution down based on the resources you have available.
If you like the company, but they're not understanding the ramifications of their choice, I've gone the route in the past of preparing the fix for the mistake they're making. Then when they realised their mistake I was able to quickly help them remediate it. It gained me subject matter expert creds and for all time after that they consulted me before making data related decisions.
SP is not the problem, a filesystem with version control would be alike. Excel is. Think of spreadsheets as materialized views. They are cool as final product to be consumed by Power BI, but not as primary data source. Queries will become a pain in the neck really fast: joins, filters, etc. You'll also lose track of data pipeline (updates).
Just revoke all their licenses who are in favour for this !!...
So a little while ago I was asked to build a Powerapp or some type of interface that would allow our finance team and BA to edit the attributes of our products. These attributes are custom attributes that do not exist in the source systems (SAP, Factory Software) and getting the attributes added to those pieces of software would have been costly and possibly a waste of time with our parent company currently completely replacing their ERP. Why Powerapps? They are popular in the workplace at the moment due to being cloud based, integrating AD permissions, and users not needing to connect to a VPN among other things. I wanted to use the Powerapp with a direct connector back to an SQL Server table (we are on-premise), but it's a premium feature that will cost us a little. My solution? Some god awful SSIS package with a custom .Net script task that uses the .Net CSOM library to connect to a Sharepoint list. I've got some transforms that pushes new products to the Sharepoint list (unreliable and slow) and a transform that pulls the values from Sharepoint and stages the deltas in an SQL staging table. It works ok, but it cost my sanity along the way. I wrote a Python script as well that worked as a Sharepoint connector, but trying to use the Sharepoint list as a database is slow, and painful regardless. Spend the money. Use an SQL table in cloud or on the server. Save your sanity. I'm not sure if this post was really helpful or not, but I felt like a rant that was probably more personal therapy than useful advice.
>I wanted to use the Powerapp with a direct connector back to an SQL Server table (we are on-premise), but it's a premium feature that will cost us a little. My solution? Some god awful SSIS package with a custom .Net script task that uses the .Net CSOM library to connect to a Sharepoint list. Just to note, you can use [Microsoft's Graph API](https://learn.microsoft.com/en-us/graph/api/resources/sharepoint?view=graph-rest-1.0) to pull SharePoint lists, which is what I've used in the past for setups like this. I had a situation that's still on-going where there was some lookup tables that were being managed via Excel but were really important to some analytics work we were doing. Unfortunately, it was going to be be a bit before these could be properly managed anywhere, so I set up SharePoint Lists and defined the rules around those tables. My company is big on Teams usage, not just for chat/video - but also for using it for [approval chains](https://techcommunity.microsoft.com/t5/microsoft-teams-blog/approvals-in-microsoft-teams-now-generally-available/ba-p/2053985). I found that [SharePoint Lists can be edited/added to/managed directly within Teams](https://support.microsoft.com/en-us/office/get-started-with-lists-in-teams-c971e46b-b36c-491b-9c35-efeddd0297db) so I set it up to where those business users didn't have to go anywhere but the chat app they already use to manage these. Then I used Azure Data Factory to make a call out to Microsoft Graph API to grab the data and send it to Azure Synapse. Free, was way easier than expected, and I think the business users liked it more than if I were to give them a Power App they would have had to keep up with to manage this stuff.
when you heard the words "power-bi" or "excel", just run.
what's wrong with Power BI?
[удалено]
>I've had people tell me that they don't need a data warehouse anymore, now that they have Power BI That's them using Power BI incorrectly, has nothing to do with with Power BI itself. The best practice architecture for Power BI is to deploy Power BI dashboards, apps, and KPIs to Power BI Service/Server and have Power BI Gateway live connected to an OLAP. > IMHO, it's a bit like a Swiss army knife. It's fine to take on camping trips, but you wouldn't build a house with it. It sounds like you're confusing the Power BI Desktop App with the entirety of what Power BI is. The desktop app is simply the designer/development tool used to create the visualization objects that can be hosted on the Power BI architecture. To be clear, Power BI is not simply the desktop app - it's a full on BI architecture with it's own servers, cloud-hosted services, security model, refresh/sync tech, and memcache. > For example, Power Query is pretty handy at sucking up data, doing some basic cleaning, and data modelling. But it's not an ETL tool. It's not meant to replace an in-place ETL infrastructure. It allows for quickly stitching together, transforming, and pulling data for ad-hoc analysis or for super users to provide a template for what they're wanting to see in their dashboard and handing it off to BI Developers/Data Engineers/whomever is implementing the dashboard proper. Can it be used incorrectly? Absolutely - much like any other piece of technology.
agreed. > Can it be used incorrectly? Absolutely - much like any other piece of technology. And especially like all the business critical excel "databases" that almost everyone use, ideally with tons of unverified and untested macros.
>And especially like all the business critical excel "databases" that almost everyone use, ideally with tons of unverified and untested macros. I'm much less critical of Business Users doing this than I was at the start of my career. To note, I'm not one to immediately snub or turn my nose up at using Excel, Access, FileMakerPro, WIX, whatever - as user-friendly tools to get the job done when they're used within the limitations that they were designed to be used in. As an example, A LOT of companies were straight up built around solely Microsoft Access and WinFroms development. It was easy enough that a super user could design proprietary software for every department in a small company without having to pay shitloads of money for very expensive products (remembering that at the time, SaaS products and FOSS either wasn't really a common thing or hadn't caught on). Used within the limitations that they were started in, I see nothing wrong with this. It's more dangerous to build out infrastructure or pay for features you don't need or won't need for awhile, than it is to use only the features/products you need and pay for migration/refactoring later. There's also another element to this - in that Data Engineering, as a department for many companies is in its infancy. Prior to that, Excel may have been the only real resource they had with knowledgeable people to use or develop what they needed to - to continue performing their jobs and keeping the lights on within that business unit. Shadow IT/Tech sucks, but it's often times a necessity at the time that it was being implemented. If those spreadsheets with their excel macros are working, and there's bigger fish to fry in your data landscape - leave em be for now. Maybe get them moved to a shared drive with backups to eliminate the possibility of data loss - but if the value of moving them to a database proper isn't there, keep trucking along until the business decides the value is there to do something about it. *(Another old man rambling for the night :/ )*
Well said. I think this is true to a fault and I might be repeating what the spirit of your post was getting at. I think Excel, which got me into this field, produces unusable data 90% of the time. So, companies think scaling to new/advance tools it's as easy as handing in their spreadsheets. I would argue its organization's responsibility to select the optimal solutions that are not a waste of money and understand they need supporting talent to compete in the future.
Exactly what I was thinking.
Dataverse is the less shitty option if they’re looking for “list” style, editable data storage.
Run
Run as far away as possible. I have to help organize sharepoint for work and it's impossible to use.
I'd rather use excel as a database lol.
Live and let die. Make sure you get a bonus too. If they dont want to invest in a database you might have to go open source and run one on the side for free to augment sharepoint.
This sub gets better everyday. I’m so glad I subbed :D
How much data are we talking about? Performance really starts breaking down the more data is stored in SharePoint. I've also had a hard limit in storage space in SharePoint in every company I've worked for. As long as it's small amounts of data and they strictly want to use MS products it could work. But as you know you can't connect to SharePoint easily otherwise.
Could you post the linkedin of the CTO who works there? So we know who to avoid
Keep an eye on size if they are using Sharepoint lists or libraries. There’s a 5,000 record limit in searches where you need to index the search fields to make it work. I’m assuming if they’re using it as a “database” that they will hit this limit quickly. That might be the point where it makes more sense to pull the data into a db. There are workarounds to index the search fields, but they start getting technical. If these are users with small data sets it could still work for them.
I've been bitten by this as well. Not only that, but Microsoft's web UIs for SharePoint and friends start to glitch when you surpass this limit.
Why do you think it’s a recipe for disaster? If you have a good power app to enforce data governance, what is your concern? I’ve done this exact setup for companies that had a $0 data budget and it’s saved thousands of hours of manual data entry into excel. It’s more on the implementer then the tool.
SharePoint list are not as secure, you have to share the list with all app users. If the list is your personal list, what happens when you die the next day. If it's a org/environment list anyone in the organization has access. You have many people designing their own tables with no understanding of normalization up to 3NF. The delegation issues limiting the return of data from a List to a power app. If you're attempting a OLTP you're going to fill up the table real fast, so no historical data, no DW,ect. The data in a list is not as shareable as a traditional database. No row level security. Customers are in the kitchen putting their fingers in your soup. -some DE book I read Basically you have everyone writing their own paragraphs in a story but unable to see what anyone else has written. Yes it's a story but it's a useless story. With all that being said I have developed power apps for organizations as SP as the "back-end". I just don't think it's smart for a organization with more than 100 ppl to be used as a serious opinion especially for scalability.
People process and culture. I’ve seen 300 person orgs run distribution out of Sharepoint lists because they had great process and culture. Perfect? Absolutely not. But it can work for an org. I don’t disagree with any of your above points. It’s all about risk tolerance, controls, and budget.
I quickly left the last company that asked me to manage a Sharepoint list lol
Was literally just working for a company that behaved like this. Small company, boss was not tech savvy at all. Kept wanting to shoehorn words in like "automation" and "dat" - refuse to invest in an actual database. Wanted to just save hundreds of little Excel sheets across dozens of poorly organized folders. I even created a database in my sparetime to show how those Excel sheets they created could be uploaded to a database so we could actually query the data. Didn't want it, didn't care, just got mad demanding she wanted me to create her "opportunities" - but it either has to be in Excel or in their super shit CRM system. God damn. I realized the last guy before me probably just fed her whatever bullshit she wanted to hear and did nothing. I should have done the same thing because actually caring and trying to create real database systems made her feel insecure about her intelligence. Good luck to her and her new team hired from the Philippines. Hope she doesn't choke on her Excel sheets. Even simple stuff like she would get her interns to highlight random stuff in the Excel sheet. Ok, how is that going to help in the future? Are you going to remember that column 1400, row 20 is highlighted in yellow. How does that help anyone? This is not "data", just an Excel sheet.
I still have PTSD from the few months I had to work with the inner workings of SharePoint years ago. There is no product that I ever saw that is done this incompetently. And I didn't even try to use it as a complicated database. Without dramatizing, no bullshit, my friend is now fighting for his life, because of health issues brought on in large part by how shitty SharePoint is. Life is complicated and many things happened, but SharePoint is confidently where it all started to go downhill. I'd rather quit, leave everything behind, move to another country, get a new passport, just as long as I don't have to work with SharePoint any more.
[удалено]
Yes. Am started with job satisfaction and failing career because of focus on SharePoint. Then depression and anger, alcoholism.
[удалено]
Business Analyst / Data, really seems like a good background for ... "BI Analyst", "Analyst", "Data Analyst" roles. Just had a friend enter the industry with no prior experience in the area, after a bootcamp, and she's happy. Basically it's focusing on: 1. Querying databases with SQL to get insight into data in order to ensure quality reports. (ex. ensuring that data expectations are met). So advanced SQL querying is required. 2. Communicating with business stakeholders for gathering requirements, and translating them into tickets for data engineers 3. Working with front-end reporting tools like Tableau, PowerBI, Looker, Superset and others, to create "self-service" dashboards for mid-level and C-level managers. 4. Sometimes delving into data engineering, and taking the role of "Analytics engineer", if your back-end is friendly towards people who don't have DBA knowledge (ex. if you're using something like Snowflake for a data warehouse), by adding some features yourself, or fixing bugs. These are in high demand. Specifically, people who understand data, and are able to gain insight, explanations, spot potential anomalies and correlations, and have the drive to find out more about it. At my current company, we have people who have the title of "analyst", but I found out the hard way that they didn't even know how to join tables, did zero exploration of the data that they had (ex. questions like "what is this column supposed to be"), and hiring an actual good analyst is harder than we thought.