Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CahabaData
Memorable Member
Memorable Member

SQL Server Express set up

On premise.  Just a Get Data scenario for now.  A commercial business application is platformed on SQL Server Express 2012. 

 

I believe therefore there are .mdf files with the tables.  For initial set up and modeling is it better to copy those .mdf files and set up the PBI Get Data to the copy files?  I don't see a strong reason to do this other than the initial Get could potentially bog down the production application until it completes.  Or is that too cautious? 

 

In the long run a permanent link with refresh via a gateway is planned so if the intial set up is off of copied files - that would need to change.

 

At Documentation area I don't find any info regarding credentials.  Server Name and Database (optional) is presented in the Get Data process - - but are there further credentials needed along the way? - just want all the ducks lined up going into this.

 

Would welcome input if you've done this Get Data scenario....

 

 

www.CahabaData.com
1 ACCEPTED SOLUTION

SQL Servers wont let just anyone query data.  You will need a username/password that the instance will be happy with.  These will be the credentials you will use for your data gateway.  

 

The only reason you might need SSMS is so you can log onto the instance as an administrator and create an account for you.

 

If you already know a username/password for your instance (eg, sa and <password>) then you can use those and there is no need for SSMS (allthough ideally you shouldn't be using sa).

 

Even if you do download SSMS you'll need to know the password for SA before you can configure your instance.

 

I take it the DB is installed on a different machine?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

8 REPLIES 8
Phil_Seamark
Employee
Employee

Hi @CahabaData

 

Are you thinking of trying to open and read the MDF files directly?  Or query a running instance of SQL Server Express?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Because it is the Express edition - I don't think the file size / record count is overly large - - and also in my searching I believe I saw that Power Query is not available to 2012 Express.  So my thinking was to pull in all the data.

 

 

www.CahabaData.com

Just connect to the db, but I recommend that you create parameters for the server and db so that it will be easy to change in the future.  When you connect, you will have to choose your authentication method, and if a different user opens the pbix file on their machine, it will ask for their credentials.  As for PBI Service, once you publish, the service identifies the user based on their Power BI log in, so you will have to set up RLS in PBI or at the source to create the desired views for your users.

well I'm glad I asked - thanks for your input;  unclear to me - -

 

1. "create parameters for the server and db"

 

2. "When you connect, you will have to choose your authentication method"

    - - this is specifically what I was searchng for in documentation - what are the choices? and what defines what is to be used?

 

3. "and if a different user opens the pbix file on their machine, it will ask for their credentials"

     - - am assuming you mean the regular credentials that an authorized user uses for the business application itself

 

no SQL DB admin people - - SQL Server Express is embedded inside of the commercial application - so not sure to what degree the current users have a handle on this info. 

www.CahabaData.com

Your instance of SQL Server will have at the very least, an Administrator login.  Often called 'sa' and you will need to know the password.

 

Once you know this, you can connect and administer the instance using SQL Server Management Studio.  

 

You can create new logins/passwords, assign permissions (eg, PBI account to be ReadOnly) etc.

 

Then you can use these credentials for Power BI.

 

SQL Express often has a named instance along the lines of <machinename>\SQLExpress or similar.  SSMS should help you find this.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

hmmm the authentication info I find as part of Gateway set up .... but nothing at all on this for your basic Get Data in PBI desktop....

 

surprised to learn one must install SSMS and use that....

 

 

www.CahabaData.com

SQL Servers wont let just anyone query data.  You will need a username/password that the instance will be happy with.  These will be the credentials you will use for your data gateway.  

 

The only reason you might need SSMS is so you can log onto the instance as an administrator and create an account for you.

 

If you already know a username/password for your instance (eg, sa and <password>) then you can use those and there is no need for SSMS (allthough ideally you shouldn't be using sa).

 

Even if you do download SSMS you'll need to know the password for SA before you can configure your instance.

 

I take it the DB is installed on a different machine?


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

the follow up is appreciated; to answer your last question: SQL Server Express 2012 is the embedded db within a commercial packaged Windows application - and it is running on its own server. The Power BI desktop resides on a development PC. 

 

In the long run once reports/dashboards have been developed & accepted - and trialed up at a PBI Service account - then the thought is to install a Gateway for a daily refresh, actually a nightly refresh, where that Gateway is to reside hasn't been considered but probably can co-reside on the same server as the application though that's not really in my realm.

 

thanks again on your further explanation on connection to sql server

www.CahabaData.com

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.