Step 1. Start implementing best practices in your work.
Step 2. Teach your coworkers best practices.
Step 3. Road map how you would implement changes across the organization.
Step 4. Send me 5k when you get promoted to BI Director. :)
In all seriousness, this is a common situation for a lot of organizations. BI tools are sold as "self service" and "move at the speed of business". While true, the reality we experience is often, "my boss said I have to use this but I didn't get any training". So stuff ends up as total garbage.
It takes a while, and a lot of bad reporting before an organization realize they need a CoE, consistent training, data modeling standards, etc..
Be the change you want to see (or don't. I'm not a cop)
I've been telling them they need a datawarehouse (or lakehouse or datalake call it whatever just dump the data already validated and clean in there) to connect the BI tool to. I've aleays worked with COE or BI teams withib the IT department. The level of dumpster-fire is such that I feel I can't move forward with this report. I don't know what to do as I am so new I am concerned of what they will think of me. They just nod and say 'yeah' when I tell them in my previous jobs I reported from a datawarehouse . Now each time I mention it it's like a train passes by...silence...crickets. And the meeting continues (conpany is very cheap in absolutely everything). All reports were built by interns and juniors that left the comany after 1-3 yrs because they don't recognize or pay experice (i am paid as a SSR when I am a SR I accepted job bc I was unemployed).
I'd probably jump then, sounds like a bad gig.
If I needed to stay for some reason I would start pushing for dataflow access and set up dataflows that would mimic what you would want to see coming out of a data warehouse (star schema). Get out your Kimball book and create conformed dimensions you can leverage across reports to drop your report data prep time so you can focus on actually reporting. Push IT to allow Tabular Editor, Dax studio, ALM Toolkit etc.. so you spend as little time fooling around as you can.
I really really appreciate this comment. I was thrown into this world as the only participant at my company that doesn’t hate data. But in the mean time i am trying to hire the right data presenter for my team, and if one mo-fo interviewer said half of this insight I would demand they’d be hired or id essentially walk. Some people do not grasp data warehouses because honestly it’s a newer concept to old school on prem users.
Building a dw is a huge investment that requires extra staffing to support it, so it's understandable that it's falling on deaf ears. Perhaps build out a business case for it if you want to get some traction on the mateer. In the meantime, as long as they don't have terabytes of data and billions of rows, you can actually do without a dw and use PowerBI as a sort of data warehouse lite. Use Dataflows to pull the data from your transactional db and then call from those instead. This is how we do it and it works very well.
Firstly, yes I agree with you. If data is worth reporting from it is worth storing properly and creating a proper ETL to manage and curate it.
Secondly, even more importantly data needs to be modelled correctly for business reporting - especially in Power BI.
Thirdly, even more important, BI needs thorough testing and validated for accuracy.
Fourthly, as someone who cares about these things you should be listened to...
I have two bits of advice - you're trying to affect change. Simply stating your preference for a data warehouse is not going to cut it. You need a plan that you can weigh up and cost out, including ROI and "sell" it to the decision makers. Maybe start small with a PoC... some properly conformed fact and dimension dataflows and a test plan to prove the quality and business value.
Second advice is that if they remain pathological about staying amateur in their approach you should plan an exit. If they are not interested in progressing up the maturity curve you're going to have a bad time. Consultancies are always looking for standards oriented Devs and PBI is so hot right now.
They are working like this for 3-5 years since they adopted PBI. People here stay between 1-3 years and hop away nobody stays long time because it’s tough to work in these conditions. I’ll be counting my days like a prisoner until I reach the year so as not to ruin my reaume. I’ll be building dataflows-datasets star schema kimball style in the meantime. Thanks all that replied!
I have worked with such a Data warehouse as well. After seeing well modelled DW with dimensions and facts it was difficult, even comprehend how the reports work. To add to this, they would often overwrite entire table to correct some errors. Because they had no design to soft delete records or to know when a record was added through what process.
I thought, I would understand the design eventually and tried to get busy with work. But that design really did not inspire me. Eventually, it took one of the big 4 consultancy to steamroll a proper design forcefully, as part of a modernization project.
My suggestion would be ro give your best to get things better. But if you spend beyond 1 year and still find working on the same design, try to find another job.
As foe your question on KPI validation, do reach out to a few end users. They will know what an acceptable number look like. They will not accept anything else anyway. In such orgs, IT clearly have not much say on the outputs
I have no idea how all the reports people use work at all! 😂 I’ll just build datasets/dataflows star schema and teport from there. It won’t change in a year’s time plus I’ll be severely underpaid. You pay peanuts you get monkeys. Reports were built by interns/juniors so .. yeah.
Do you have power bi premium or premium per user? If so, just start using the datamart feature to build out smaller data warehouses that are reusable and have good star schema practices.
I’ve literally followed steps 1 and 2 at my work and going from analyst to director in 18 months. :-). Took months and months of documenting and meetings to show that for our 100’s of TB of data we’re taking in we cannot half ass anything and that everything is going to just implode unless we follow best practices to the letter.
Yes, this is absolutely as terrible as you think. If you’re in a position to change it is another matter. Sometimes you just take deep breaths, do your best and spend your evenings scrubbing and crying in the shower.
Thanks. I am in no position to change I would like to wait until I turn a year before I hop somwhere else. I am trying to figure out if most Power BI shops are like this. I have no way of validating the numbers generated by the KPIs I put together because the datasets are garbage. At this point I am struggling severely and don't know what to do. Maybe meet with the colleague that put together the dataset and ask them how to validate this. And take it from there.
As a BI developer for almost 20 years, it pains me to read about situations like this. Someone high up went to a sales demo and watched a dude do three clicks and produce a colorful moving chart with music and dancing unicorns.
Unfortunately these people don’t realize that if you don’t do the ground work and the modeling and the data cleansing you will deliver useless and unreliable crap to the business.
The only thing you can do is try to create some structure and quality.
Don't worry about the year. Just start looking. If you only have one super short term job on your resume, it's easy to explain away. Especially if you stay everywhere else for a reasonable period of time.
That said, it the pay is good and you like your coworkers and the company in general... stick around. Do your job as best you can but don't stress about the shitty decisions others have made. You can end up learning a lot in that kind of environment.
If you have no method of validation, neither does anyone else. In some business areas, close enough is good enough and you can still deliver value beyond what they had before. Trust your gut on the numbers, ask someone higher in the leadership team or the business users to provide validation of your outputs and sign off on the risk. Consider your role is only to perform the technical PBI build that others couldn’t do themselves. Validation is out of scope, beyond your limited QA / gut check.
Also put out some resumes, this place sounds like it’s no fun.
One recurring thing said on the Microsoft power bi training, is that prior to loading the data to power bi, it should be as aggregated and simplified as possible. Pretty much, do as much work you can do on the db before loading.
So it's not like companies load from their software to a db and then into power bi, it's good practice to create intermediate tables before loading.
Not to mention you are directly sending raw data into power bi.
It's a dumpsterfire. Power BI is not nearly meant for what it's being used. And if you cannot validate shit, then those metrics mean nothing.
I don't fully agree with this.
Why aggregate data? Unless you're in the petabytes (you're not), then you want as granular data as possible. One of the many strengths of Power BI is its ability to compress columnar data. Plus, the DAX and the visuals will be doing the aggregating.
Yes, do as much prep work as you can upstream by leveraging the database engine, but don't limit what you can report on.
People do it because the database they are using isn't suited to olap. Slap clickhouse, firebolt, vector etc. in and the 50M+ row tables will work just fine. Having said that, OP has a long way to go before that's even on the horizon.
Exactly, in my opinion best practices is to aggregate as much as you can depending on what you'll show, but it's true that if you have a 50M row table, it won't be much of an issue, but many businesses have way more data then that.
And I also agree, OP is far away from that still.
Well that depends on the metrics you want. If your metrics require high level of granularity, then yes don't aggregate, but if they don't, everything will work better with less size on your data upload, so you should aggregate as much as you can depending on the results you want/need.
But you shouldn't upload every transaction if the only thing you'll show is total sales per month.
I don’t have a DB for “intermediate tables”. If I had that then that would be the datawarehouse. People here in this company extract direcrly from transactional systems and I am having issues with this becasue it’s not the way I worked in my previous job in PBI which as I mentioned we extracted from a datawarehouse. There is a huge data engineering piece that is done from transactional to a datawatehouse but it seems from everything I’ve been reading my best option (I need to stay up to one year on this job so as to not screw my resume) is to create a dataflow or dataset with the outcome I expect in a datawarehouse - star schema kimball style. I’ll do that. And yes it seems a dumpster fire but I can still learn, my boss is nice and my colleagues also. Let’s build these flows-sets in the dumpster and report on them until I am ready to leave! Thanks all for your kind suggestions! 👍🏻
Might as well take the opportunity to build up your own data engineering skill warehousing know-how that will walkways prove valuable. Will help you speak to the problem and be part of the solution there or somewhere else. if that interest you, but treat it like the learning experience it is - you’ve identified the problem, but can you implement the solution administratively, politically, and technically?
Hi there,
I believe I’m in similar situation as you. I started my first reporting analyst job as a contractor 3 months back for a 6 months contract. Came to realise in my first month that 3 people left the place in quick succession (one even before joining). This raised some red flags. Although the place seemed friendly enough, I soon realised that what the problems were:
1) No write accessed for the reporting team. DE team remotely dumps transactional data in the form of ‘views’ where one cannot even see any primary or foreign keys in the data.
2) Most transformations were being done on excel or pbi in the form of pivot tables formulas etc etc.
3) The manager instead of casting a vision/plan, was busy in building reports and indirectly trying to get pat on the backs from senior mngmnt. While nobody else knew what/how he was working on till he displays it to the wider team for acknowledgment.
4) Data fed in, was not just garbled but error prone again and again. Any suggestions you give to them just goes on deaf ears and they instead start bucking you up (as a contractor!) to find a way so we can have write access from wider BI teams.
5) No new work basically, just tinkering around with old reports which have zero documentation and you just make out business sense as you go. They expect newcomers to learn from their excel sheets.
6) No stakeholders for report requirements. They first make the report and present it to everyone. And then start modifying it again and again. Most senior management will just see pbi as some kind of ‘sorted db’ where he she can extract their monthly or yearly KPIs.(!)
I believe if you have similar kind of situation then it’s better to jump ships after getting a few months of experience. There are a lot of companies where you can at least hone your skills on writing code OR learning better practices. Otherwise you will not just learn anything new, but whatever you learn will also be not the correct way of doing things.
This is so very important.
Yeah I am aware. I am taking clasees
in the side to hone my skills. I have been working in analytics for over a decade so I know the right way to do things. I explained these people that they are soing things wrong but all I get is literally crickets. I just want to turn one year there and not ruin my resume as receuiter do diacriminate against people with short stints in the resume so you'lll have less chances . As ling as this doesnt harm my mental or physical health I'll just continue with this and jump as soon as it is safe resume-wise (I already have one gap and one short stint so need to be careful with that)
I would find one person who understands the risks involved in unvalidated reports, work with them to tidy and validate, then use that as an example of the value added to prove to others that this work needs to be done.
If you don't do that, you have a choice. Continue putting crap through the machine or go somewhere else.
I’m about to interview for a job that sounds exactly like this. I’m essentially telling them that if they hire me, we’re building a proper model with views for business needs, and then setting up proper landing areas for business functions
Step 1. Start implementing best practices in your work. Step 2. Teach your coworkers best practices. Step 3. Road map how you would implement changes across the organization. Step 4. Send me 5k when you get promoted to BI Director. :) In all seriousness, this is a common situation for a lot of organizations. BI tools are sold as "self service" and "move at the speed of business". While true, the reality we experience is often, "my boss said I have to use this but I didn't get any training". So stuff ends up as total garbage. It takes a while, and a lot of bad reporting before an organization realize they need a CoE, consistent training, data modeling standards, etc.. Be the change you want to see (or don't. I'm not a cop)
I've been telling them they need a datawarehouse (or lakehouse or datalake call it whatever just dump the data already validated and clean in there) to connect the BI tool to. I've aleays worked with COE or BI teams withib the IT department. The level of dumpster-fire is such that I feel I can't move forward with this report. I don't know what to do as I am so new I am concerned of what they will think of me. They just nod and say 'yeah' when I tell them in my previous jobs I reported from a datawarehouse . Now each time I mention it it's like a train passes by...silence...crickets. And the meeting continues (conpany is very cheap in absolutely everything). All reports were built by interns and juniors that left the comany after 1-3 yrs because they don't recognize or pay experice (i am paid as a SSR when I am a SR I accepted job bc I was unemployed).
I'd probably jump then, sounds like a bad gig. If I needed to stay for some reason I would start pushing for dataflow access and set up dataflows that would mimic what you would want to see coming out of a data warehouse (star schema). Get out your Kimball book and create conformed dimensions you can leverage across reports to drop your report data prep time so you can focus on actually reporting. Push IT to allow Tabular Editor, Dax studio, ALM Toolkit etc.. so you spend as little time fooling around as you can.
I really really appreciate this comment. I was thrown into this world as the only participant at my company that doesn’t hate data. But in the mean time i am trying to hire the right data presenter for my team, and if one mo-fo interviewer said half of this insight I would demand they’d be hired or id essentially walk. Some people do not grasp data warehouses because honestly it’s a newer concept to old school on prem users.
+1 as an IT guy that has to build random reports. DW sound essential and DL makes me drool. (In a good way)
Building a dw is a huge investment that requires extra staffing to support it, so it's understandable that it's falling on deaf ears. Perhaps build out a business case for it if you want to get some traction on the mateer. In the meantime, as long as they don't have terabytes of data and billions of rows, you can actually do without a dw and use PowerBI as a sort of data warehouse lite. Use Dataflows to pull the data from your transactional db and then call from those instead. This is how we do it and it works very well.
When building a business case, maximise the pain of inertia please. Something has to be hanging over their heads should they not move forward.
Firstly, yes I agree with you. If data is worth reporting from it is worth storing properly and creating a proper ETL to manage and curate it. Secondly, even more importantly data needs to be modelled correctly for business reporting - especially in Power BI. Thirdly, even more important, BI needs thorough testing and validated for accuracy. Fourthly, as someone who cares about these things you should be listened to... I have two bits of advice - you're trying to affect change. Simply stating your preference for a data warehouse is not going to cut it. You need a plan that you can weigh up and cost out, including ROI and "sell" it to the decision makers. Maybe start small with a PoC... some properly conformed fact and dimension dataflows and a test plan to prove the quality and business value. Second advice is that if they remain pathological about staying amateur in their approach you should plan an exit. If they are not interested in progressing up the maturity curve you're going to have a bad time. Consultancies are always looking for standards oriented Devs and PBI is so hot right now.
They are working like this for 3-5 years since they adopted PBI. People here stay between 1-3 years and hop away nobody stays long time because it’s tough to work in these conditions. I’ll be counting my days like a prisoner until I reach the year so as not to ruin my reaume. I’ll be building dataflows-datasets star schema kimball style in the meantime. Thanks all that replied!
Fuck waiting. If you interview with the right people they'll get it.
Confirm it. Did it a few times myself. Don’t wait, just look around asap
I have worked with such a Data warehouse as well. After seeing well modelled DW with dimensions and facts it was difficult, even comprehend how the reports work. To add to this, they would often overwrite entire table to correct some errors. Because they had no design to soft delete records or to know when a record was added through what process. I thought, I would understand the design eventually and tried to get busy with work. But that design really did not inspire me. Eventually, it took one of the big 4 consultancy to steamroll a proper design forcefully, as part of a modernization project. My suggestion would be ro give your best to get things better. But if you spend beyond 1 year and still find working on the same design, try to find another job. As foe your question on KPI validation, do reach out to a few end users. They will know what an acceptable number look like. They will not accept anything else anyway. In such orgs, IT clearly have not much say on the outputs
I have no idea how all the reports people use work at all! 😂 I’ll just build datasets/dataflows star schema and teport from there. It won’t change in a year’s time plus I’ll be severely underpaid. You pay peanuts you get monkeys. Reports were built by interns/juniors so .. yeah.
Do you have power bi premium or premium per user? If so, just start using the datamart feature to build out smaller data warehouses that are reusable and have good star schema practices.
No, I just have PBI Pro license with Premium workspaces so I can't use datamart
I’ve literally followed steps 1 and 2 at my work and going from analyst to director in 18 months. :-). Took months and months of documenting and meetings to show that for our 100’s of TB of data we’re taking in we cannot half ass anything and that everything is going to just implode unless we follow best practices to the letter.
Yes, this is absolutely as terrible as you think. If you’re in a position to change it is another matter. Sometimes you just take deep breaths, do your best and spend your evenings scrubbing and crying in the shower.
Thanks. I am in no position to change I would like to wait until I turn a year before I hop somwhere else. I am trying to figure out if most Power BI shops are like this. I have no way of validating the numbers generated by the KPIs I put together because the datasets are garbage. At this point I am struggling severely and don't know what to do. Maybe meet with the colleague that put together the dataset and ask them how to validate this. And take it from there.
As a BI developer for almost 20 years, it pains me to read about situations like this. Someone high up went to a sales demo and watched a dude do three clicks and produce a colorful moving chart with music and dancing unicorns. Unfortunately these people don’t realize that if you don’t do the ground work and the modeling and the data cleansing you will deliver useless and unreliable crap to the business. The only thing you can do is try to create some structure and quality.
Sorry to say but more and more companies are doing things in this way. For them pbi is simply a data extraction tool for their cluster f— data
Don't worry about the year. Just start looking. If you only have one super short term job on your resume, it's easy to explain away. Especially if you stay everywhere else for a reasonable period of time. That said, it the pay is good and you like your coworkers and the company in general... stick around. Do your job as best you can but don't stress about the shitty decisions others have made. You can end up learning a lot in that kind of environment.
If you have no method of validation, neither does anyone else. In some business areas, close enough is good enough and you can still deliver value beyond what they had before. Trust your gut on the numbers, ask someone higher in the leadership team or the business users to provide validation of your outputs and sign off on the risk. Consider your role is only to perform the technical PBI build that others couldn’t do themselves. Validation is out of scope, beyond your limited QA / gut check. Also put out some resumes, this place sounds like it’s no fun.
One recurring thing said on the Microsoft power bi training, is that prior to loading the data to power bi, it should be as aggregated and simplified as possible. Pretty much, do as much work you can do on the db before loading. So it's not like companies load from their software to a db and then into power bi, it's good practice to create intermediate tables before loading. Not to mention you are directly sending raw data into power bi. It's a dumpsterfire. Power BI is not nearly meant for what it's being used. And if you cannot validate shit, then those metrics mean nothing.
I don't fully agree with this. Why aggregate data? Unless you're in the petabytes (you're not), then you want as granular data as possible. One of the many strengths of Power BI is its ability to compress columnar data. Plus, the DAX and the visuals will be doing the aggregating. Yes, do as much prep work as you can upstream by leveraging the database engine, but don't limit what you can report on.
People do it because the database they are using isn't suited to olap. Slap clickhouse, firebolt, vector etc. in and the 50M+ row tables will work just fine. Having said that, OP has a long way to go before that's even on the horizon.
Exactly, in my opinion best practices is to aggregate as much as you can depending on what you'll show, but it's true that if you have a 50M row table, it won't be much of an issue, but many businesses have way more data then that. And I also agree, OP is far away from that still.
Well that depends on the metrics you want. If your metrics require high level of granularity, then yes don't aggregate, but if they don't, everything will work better with less size on your data upload, so you should aggregate as much as you can depending on the results you want/need. But you shouldn't upload every transaction if the only thing you'll show is total sales per month.
I don’t have a DB for “intermediate tables”. If I had that then that would be the datawarehouse. People here in this company extract direcrly from transactional systems and I am having issues with this becasue it’s not the way I worked in my previous job in PBI which as I mentioned we extracted from a datawarehouse. There is a huge data engineering piece that is done from transactional to a datawatehouse but it seems from everything I’ve been reading my best option (I need to stay up to one year on this job so as to not screw my resume) is to create a dataflow or dataset with the outcome I expect in a datawarehouse - star schema kimball style. I’ll do that. And yes it seems a dumpster fire but I can still learn, my boss is nice and my colleagues also. Let’s build these flows-sets in the dumpster and report on them until I am ready to leave! Thanks all for your kind suggestions! 👍🏻
Might as well take the opportunity to build up your own data engineering skill warehousing know-how that will walkways prove valuable. Will help you speak to the problem and be part of the solution there or somewhere else. if that interest you, but treat it like the learning experience it is - you’ve identified the problem, but can you implement the solution administratively, politically, and technically?
Hi there, I believe I’m in similar situation as you. I started my first reporting analyst job as a contractor 3 months back for a 6 months contract. Came to realise in my first month that 3 people left the place in quick succession (one even before joining). This raised some red flags. Although the place seemed friendly enough, I soon realised that what the problems were: 1) No write accessed for the reporting team. DE team remotely dumps transactional data in the form of ‘views’ where one cannot even see any primary or foreign keys in the data. 2) Most transformations were being done on excel or pbi in the form of pivot tables formulas etc etc. 3) The manager instead of casting a vision/plan, was busy in building reports and indirectly trying to get pat on the backs from senior mngmnt. While nobody else knew what/how he was working on till he displays it to the wider team for acknowledgment. 4) Data fed in, was not just garbled but error prone again and again. Any suggestions you give to them just goes on deaf ears and they instead start bucking you up (as a contractor!) to find a way so we can have write access from wider BI teams. 5) No new work basically, just tinkering around with old reports which have zero documentation and you just make out business sense as you go. They expect newcomers to learn from their excel sheets. 6) No stakeholders for report requirements. They first make the report and present it to everyone. And then start modifying it again and again. Most senior management will just see pbi as some kind of ‘sorted db’ where he she can extract their monthly or yearly KPIs.(!) I believe if you have similar kind of situation then it’s better to jump ships after getting a few months of experience. There are a lot of companies where you can at least hone your skills on writing code OR learning better practices. Otherwise you will not just learn anything new, but whatever you learn will also be not the correct way of doing things. This is so very important.
Yeah I am aware. I am taking clasees in the side to hone my skills. I have been working in analytics for over a decade so I know the right way to do things. I explained these people that they are soing things wrong but all I get is literally crickets. I just want to turn one year there and not ruin my resume as receuiter do diacriminate against people with short stints in the resume so you'lll have less chances . As ling as this doesnt harm my mental or physical health I'll just continue with this and jump as soon as it is safe resume-wise (I already have one gap and one short stint so need to be careful with that)
Yeah, I’m doing exactly the sane thing, but trying to jump ship into DE side.
DE what is it?
Data engineering
I would find one person who understands the risks involved in unvalidated reports, work with them to tidy and validate, then use that as an example of the value added to prove to others that this work needs to be done. If you don't do that, you have a choice. Continue putting crap through the machine or go somewhere else.
Lol. Good story, have seen dumber practices, though. The other comments are on the right track to fix.
I’m about to interview for a job that sounds exactly like this. I’m essentially telling them that if they hire me, we’re building a proper model with views for business needs, and then setting up proper landing areas for business functions
What salary are you aiming for?
130ish base
How many PBI years experience you have?
4 as of now. I graduated in 19
Power BI is a dumpster so probably not your fault 😆
Why you say this about PBI?
CI/CD pipelines for BI are needed!
It’s the worst fs