January 15, 2021 at 7:07 pm
Hi, First time poster, and hoping everyone goes easy on me.
I am very new at SQL Server, just getting my feet wet and really not even sure where to start and hoping some of you vets here can help a newbie get up and running a little bit.
My underlying goal/project at the moment is take an existing PowerPivot Model and load/deploy that into SQL Server. Then to connect to the Model in Excel and begin using Pivot Tables, Cube Formula's, etc.
I am currently doing everything on a local Windows 10 Machine. But I will later attempt to try and install SQL server in a Ubuntu VM on my home server at a later point, and eventually possibly an aws instance.
From my readings, I should be installing SQL Server 2019 in Tabular mode correct? But when I installed SQL Server, I do not see this option anywhere, and possibly the default install is already Tabular Mode?
I also installed VS Studio 19, as well as the Microsoft.DataTools.AnalysisServices.vsix addin. I was then able to create a project and import my Power Pivot Model.
I also have installed SSMS 18. Here I do see my SQL Server and can connect it. The server name is "PCNAME\SQLEXPRESS"
Back in VSStudio, in the projects settings, I did set the deployment server to be the server name. However, I did notice the edition defaults to 'Developer' and do not see an Express option.
I also created a Database in SSMS and then changed the name in Projects Properties to use that database. But unsure if when you deploy, you need an existing database or when deploying it will create it for you.
After all the above, when I do try and deploy I appear to always get a connection error of:
Severity Code Description Project File Line Suppression State
Error Cannot deploy metadata. Reason: An error occurred while connecting to the server.
So in starting to learn more, am I even going in the right direction?
Thank you,
PT
January 15, 2021 at 9:35 pm
Welcome to the world of SQL Server.
So a lot of fun things you are jumping into and probably a lot of unexpected headaches. I will try to address things as you brought them up.
SQL Server cannot operate in Tabular mode as that is not a feature of SQL Server. That is a feature of Analysis Services (SSAS). And I believe that SSAS is not a feature available in SQL Server Express (see - https://docs.microsoft.com/en-us/analysis-services/analysis-services-features-supported-by-the-editions-of-sql-server-2016?view=asallproducts-allversions and https://social.msdn.microsoft.com/forums/en-US/0495c3df-1508-4995-a099-3e056a2dbfce/ssas-with-sql-server-express) <-- experts correct me if I am wrong here.
I think that without SSAS though, the rest of your questions become irrelevant. Deployment server option doesn't let you pick Express because Express doesn't support SSAS.
SSAS is NOT the same thing as SQL Server or SSMS. SSAS is more like SSRS - a standalone feature of SQL Server that needs a SQL Server back end. If you have a visual studio subscription you should be able to install SQL Server developer edition. If you don't, your best bet is to bug someone for a test environment to work with that has a SQL Server license you can use OR grab a trial license which should give you 180 days to work with it.
Does that help?
My opinion, jumping from "no SQL experience" to "SSAS" is a BIG jump and then you are talking about adding in an Ubuntu VM and an AWS instance... that is a lot to learn.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 16, 2021 at 3:48 pm
Thank you!
You explained the above very thoroughly and has already helped me in progressing. I was already able to switch from Express to Developer, and set up a SSAS instance. After that (and some minor user permission issues) was able to get my get the VS 2019 project deployed into the server. And then after that got Excel to connect to it.
But yes, still very new to SQL Server and I know it will take some serious dedicated time and experimentation to learn more. But I am pretty familiar with PC's being self taught in VBA as well have some past experience with php and java and as well. And I already run a ProxMox Server (think its my 3rd home build server) with a few containers and VM's already. So a Ubuntu container and CLI interface is not new for me.
But it's baby step learning, and I think one of my next steps is going to be in working to 'by pass' the importing of the Excel Power Power Pivot Model, and try to import data directly from the various Json files I have. Or even better, to import the json data directly from our Company's 3rd party provider. Currently, I need to use PostMan to connect and download a json file, import that into excel, and then into VS 2019.
But I did learn in PowerQuery how to connect/query and get that json data directly and not need PostMan. But not sure yet if that is also possible/similar with VS 2019. I need to read up more on Dax and keep learning. 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply