Forum Discussion
Impactful Individualcrossroads in fabric
hi we are at a crossroads in fabric. a peer of ours who is a novice to anything BI is asking if...
1) fabric can connect to OneStream in an economical way
2) fabric can connect to our warehouse which is already known to our gateway and already the source for various semantic models
the onestream connection would be to extract various gl accounts, their values and various attributes associated with them.
the warehouse connection would be to put the data extracted from onestream into the warehouse.
Eventhough he is a .net coder/architect by trade, he is averse to using (rest) api's. I'm thinking if the/a onestream connector requires use of api's who cares. Its probably still a low code approach.
He does have a gateway he can use to push OneStream data wherever he wants but i told him "pull" in our business is more desirable. Less people in the kitchen if there is a problem.
I also told him that if we do go to push, onestram can probably push to ftp where we can pick up thge data.
looking for guidance from the community. I know there is a very expensive onestream connector out there so we are ignoring that option for the moment.
i also explained to our peer that spark is a code based (for good reasons) approach in fabric to "pipeline" data but that i believe there is an alternative using i think a no code Copy task in DF. I did tell him though that AI will write/suggest code for you if you choose the more verbose spark mode. i aslo told him that some data platform products wouldnt be able to do this in one pipeline (eg snowflake), perhaps needing a LH like staging area but that i'd need to find out.
i'm going to search onestream connectors again.
Hi db042190,
Thank you Thomaslleblanc GilbertQ tayloramy, fro your insights.
In Microsoft Fabric, integrating with OneStream depends on how accessible the data is. If APIs are available, REST can be used. Otherwise, exporting the data and importing it through a pipeline is a good option. For your on-premises data warehouse, you can connect using the existing gateway.
Connector overview - Microsoft Fabric | Microsoft Learn
How to access on-premises data sources in Data Factory - Microsoft Fabric | Microsoft Learn
REST connector overview - Microsoft Fabric | Microsoft Learn
Thank you.
14 Replies
- GilbertQ
Super User
Hi db042190
With regards to your onestream, if there is a connector in Power BI you can definitely then use this with the Dataflow Gen 2CI CD version which I would recommend using at. This consume significantly less capacity units as you mentioned. Your other alternative is to use a Spark notebook with the Apis. It all depends on the rate limits you might get with regards to the Apis, but this will once again be even a lot cheaper than a dataflow Gen 2.
And finally with regards connecting to your data warehouse from Fabric, you can use a copy job activity which will use the gateway to connect to your on Prem database.
db042190thx gilbert for completeness i'm mentioning here thta we are on onestream 8.4.? so we passed that prereq.
- db042190
thx gilbert it sounds like you are saying dataflow gen 2 is cheaper in one sentence but more expensive in another. Do you have a typeo? is it possible i dont know what CI CD is in this context?
With regards to your onestream, if there is a connector in Power BI you can definitely then use this with the Dataflow Gen 2CI CD version which I would recommend using at. This consume significantly less...
...but this will once again be even a lot cheaper than a dataflow Gen 2.
- db042190
Hi Gilbert, i forgot to ask , we have an on prem dw (gateway knows about this) and a vm dw also, one im sure no gateway knows about. Will there be any challenge connecting to the vm?
- Thomaslleblanc
You can use the Copy data activity to connection and query a RESTAPI without being a programmer or use a notebook to code it all. I am sure you can get examples from OneStream.
You can also ask for dmps to SFTP and then pick those files up with fabric dataflows or Copy data/job Activities
- db042190
thx Thomaslleblanc. I do see a onestream connector in pbi desktop which i'll try to use after getting the environment url and an auth acct 9credentials). I will be surprised if its free. we'll see.
According to this post https://learn.microsoft.com/en-us/power-query/connectors/onestream , we'd use (probably be limited to) Dataflow Gen2.
I'd have to go back to my notes to see what that is (maybe data factory in saas?) vs spark notebook.
I sent our peer a request for the url and some auth creds of some kind. I may need to submit an "erp login request" to our service desk. Maybe only after the url is shared.
what about connecting to our dw from fabric? all i know right now is that our gateway is well aware of the server and probably db its on.
BTW, what are dmps to sftps? "dumps" to ftp that originate on onestream?
- db042190
thx amy why do they call it ...CI/CD. I just read about dataflow gen2 and now understand its a way in fabric to use the familiar powerquery experience to transform data. So where does the CI/CD come in?
- db042190
thats what i thought. guessing whether we use ci/cd or not, data flow gen2 is fair game for what we are doing.
- db042190
we just leatrned that api's in onestream MIGHT NOT be enabled, not unless we want to spend $'s. we'll see. at the moment we believe whether its data flow gen 2 or notebooks, we'll need the api's to succeed. I dont know if web requests are a possibility here...but maybe they use api's anyway/too. no matter what i'll post back here how we did and mark one of these posts as the answer.
v-saisrao-msftCommunity Support
Hi db042190,
Thank you Thomaslleblanc GilbertQ tayloramy, fro your insights.
In Microsoft Fabric, integrating with OneStream depends on how accessible the data is. If APIs are available, REST can be used. Otherwise, exporting the data and importing it through a pipeline is a good option. For your on-premises data warehouse, you can connect using the existing gateway.
Connector overview - Microsoft Fabric | Microsoft Learn
How to access on-premises data sources in Data Factory - Microsoft Fabric | Microsoft Learn
REST connector overview - Microsoft Fabric | Microsoft Learn
Thank you.
- v-saisrao-msft
Hi db042190,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
- v-saisrao-msft
HI db042190,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
