Tuy nhiên, còn việc kết nối trực tiếp từ Excel với một phiên bản Dịch vụ Phân tích từ xa mà không sử dụng Windows Authentication thì sao? . Tôi đang nói rằng bạn có thể kết nối trực tiếp từ Excel của riêng mình với máy chủ SSAS từ xa mà không cần sử dụng xác thực cửa sổ. Chà, về mặt kỹ thuật, không có chế độ Xác thực máy chủ SQL nào khả dụng cho Dịch vụ phân tích. Vậy điều đó thực sự có nghĩa là gì khi tôi nói “không sử dụng xác thực cửa sổ”?
Kịch bản
Bạn đang làm tư vấn BI, bạn được biết có khách hàng cần làm một số báo cáo đơn giản trên Excel như sau
· Bạn nên kết nối với máy chủ của khách hàng bằng kết nối VPN được cung cấp
· Kết nối VPN có thể được thiết lập thông qua Windows VPN, Cisco VPN, v.v. vì vậy máy khách VPN hoặc cổng và giao thức được sử dụng không thực sự quan trọng
· Microsoft Excel KHÔNG được cài đặt trên máy chủ của khách hàng
· Bạn KHÔNG được phép cài đặt Excel trên máy chủ
· Vì đây là một quy trình tốn kém nên khách hàng sẽ không thiết lập máy ảo trong mạng của họ để bạn có thể kết nối từ xa với máy ảo đó và
· Không có mối quan hệ tin cậy giữa mạng của bạn và mạng của khách hàng nên không thể xác thực tên người dùng và mật khẩu miền của bạn trên
· Khách hàng cần có một số báo cáo trên Excel trên cơ sở dữ liệu SQL Server và khối OLAP trên Dịch vụ phân tích [SSAS] Định dạng dưới dạng bảng, thao tác này sẽ cung cấp cho bạn định dạng rõ ràng hơn và thêm bộ lọc vào dữ liệu của bạn
Sau khi bạn sử dụng Trang đầu > Định dạng dưới dạng Bảng hoặc cách khác là Chèn > Bảng, bạn có thể đặt tên cho bảng đã tạo, nhưng điều đó không có nghĩa là bây giờ bạn có một bảng cơ sở dữ liệu [chưa nói đến một cơ sở dữ liệu đầy đủ]
Menu Dữ liệu cung cấp cho bạn một số khả năng giống như cơ sở dữ liệu, chẳng hạn như thiết lập Mối quan hệ giữa các bảng
Ngoài ra, về mặt công thức, Excel rất giàu khả năng. Nó cho phép bạn sử dụng các hàm tra cứu [VLOOKUP, HLOOKUP] hoặc thậm chí các hàm cơ sở dữ liệu [DGET, DAVERAGE, DMAX, v.v. ], nhưng thậm chí tất cả những điều đó cùng nhau không có nghĩa là Excel là một cơ sở dữ liệu
Nếu Excel không phải là cơ sở dữ liệu, tôi nên xem xét các tùy chọn nào?
Không chỉ Excel không phải là một công cụ cơ sở dữ liệu;
Có một vài dấu hiệu cho thấy bạn đang sử dụng Excel làm cơ sở dữ liệu và bạn không nên
- Bảng tính của bạn chậm. mất nhiều thời gian để tải và chỉnh sửa còn lâu hơn nữa
- Bạn có các công thức trong bảng tính của mình mà bạn không chắc chúng hoạt động như thế nào hoặc thậm chí tại sao chúng lại ở đó
- Thông tin được lặp đi lặp lại ở nhiều nơi. Ví dụ: tên công ty được viết lặp lại cho mỗi liên hệ trong cùng một công ty. Điều này có nghĩa là có nhiều nơi mà lỗi đánh máy hoặc lỗi cập nhật có thể len lỏi vào. Những sai lầm nhỏ có thể khiến bạn gặp nhiều rắc rối
Nếu bất kỳ dấu hiệu nào trong số này nghe có vẻ quen thuộc, vui lòng kiểm tra xem bạn có “cơ sở dữ liệu trong Excel” trước mặt không. Nếu bạn làm như vậy, giải pháp thay thế tốt nhất để xem xét là sử dụng phần mềm cơ sở dữ liệu thay vì bảng tính. Chúng ta sẽ thấy một số cách tiếp cận khả thi để đạt được sự thay đổi này
Trong bài viết này, chúng tôi sẽ giới thiệu cho bạn một số tình huống tích hợp có thể xảy ra trong một tổ chức dạng FAQ. chúng tôi sẽ bao gồm
- Tôi có thể nhập tệp Excel vào SQL Server không?
- Làm cách nào để kết nối Microsoft Excel với SQL Server?
- Tôi có thể liên kết Excel với Dynamics 365 không?
- Làm cách nào để kết nối Excel với danh sách SharePoint?
- Làm cách nào để kết nối Excel với SQL Server từ xa?
Xin lưu ý rằng chúng tôi sẽ đề cập đến SQL Server, công cụ cơ sở dữ liệu, nhưng chúng tôi cũng sẽ đề cập đến SQL, ngôn ngữ mà bạn viết các truy vấn cơ sở dữ liệu của mình [SQL = Ngôn ngữ truy vấn có cấu trúc]. Ngoài ra, trong phần còn lại của bài viết này, chúng ta sẽ xem xét cơ sở dữ liệu quan hệ, nhưng bạn cũng có thể xem xét cơ sở dữ liệu NoSQL nếu muốn. Chúng tôi sẽ đặc biệt tập trung vào Microsoft SQL Server, nhưng bạn có thể nhận được kết quả tương tự với MySQL, MariaDB hoặc các cơ sở dữ liệu khác
Tôi có thể nhập tệp Excel vào SQL Server không?
Bạn chắc chắn có thể lấy dữ liệu bạn hiện có trong một hoặc nhiều bảng tính Excel vào Microsoft SQL Server
Nếu bạn muốn làm điều đó, điều đầu tiên bạn nên tự hỏi mình là liệu việc nhập chỉ diễn ra một lần hay bạn sẽ cần lặp lại quy trình nhập này từ Excel sang SQL
Cách nhập dữ liệu từ Excel sang SQL Server – Quy trình đơn giản
Nếu bạn chỉ nhập Excel vào SQL Server một lần, đây là hướng dẫn từng bước cho việc đó
- Mở Microsoft SQL Server Management Studio [SSMS]
- Kết nối với máy chủ của bạn
- Trong Object Explorer, định vị cơ sở dữ liệu bạn muốn nhập vào. Nếu bạn chưa có cơ sở dữ liệu, hãy tạo một cơ sở dữ liệu bằng cách bấm chuột phải vào Cơ sở dữ liệu rồi chọn Cơ sở dữ liệu mới…
- Nhấp chuột phải vào tên cơ sở dữ liệu và chọn Tác vụ > Nhập dữ liệu… Thao tác này sẽ mở Trình hướng dẫn Nhập và Xuất SQL Server
- Chọn Excel làm Nguồn dữ liệu và đảm bảo rằng bạn cũng đã chọn đúng phiên bản Excel [nếu phiên bản của bạn không được hiển thị trong danh sách, hãy chọn phiên bản gần nhất có sẵn]
- Chọn SQL Native Client làm đích
- Cho biết tên của máy chủ là Máy chủ [localhost hoặc. nếu nó ở trên cùng một máy tính]
- Đối với Xác thực, hãy sử dụng cùng một phương thức bạn đã sử dụng ở bước 2 để kết nối với máy chủ
- Chọn cơ sở dữ liệu SQL của bạn trong Cơ sở dữ liệu [nếu không có cơ sở dữ liệu nào được liệt kê, hãy nhấn nút Làm mới ở bên phải]
- Giờ đây, bạn có thể chọn giữa Sao chép dữ liệu từ một hoặc nhiều bảng hoặc dạng xem hoặc Viết truy vấn để chỉ định dữ liệu cần chuyển. Trừ khi bạn không muốn chuyển tất cả dữ liệu mình có, tùy chọn bạn nên chọn là tùy chọn đầu tiên. Cái thứ hai cho phép bạn chọn dữ liệu bạn muốn. Sử dụng nó, bạn có thể nhập dữ liệu từ Excel sang SQL Server bằng truy vấn. Giả sử tùy chọn đầu tiên đã được chọn, hãy tiếp tục
- Trên Select Source Tables and Views, bạn có thể chọn một hoặc nhiều bảng dựa trên tên bảng tính và tên của chính bảng đó
- Sử dụng nút Chỉnh sửa ánh xạ bên dưới để xác nhận rằng mỗi cột đang được ánh xạ như bạn mong đợi
- Trên màn hình Lưu và chạy gói, hãy chọn tùy chọn Chạy ngay lập tức và nhấp vào Kết thúc
Nếu bạn cần nhập dữ liệu từ Excel sang máy chủ SQL tự động, lặp đi lặp lại nhiều lần, hãy xem phần tiếp theo
Làm cách nào để kết nối Microsoft Excel với SQL Server?
Trong phần này, chúng tôi sẽ giả định rằng bạn muốn kết nối Excel với máy chủ SQL theo cách lâu dài và tự động hơn
Sử dụng loại kết nối tự động này nếu
- Dữ liệu mới được nhập vào tệp Excel cần được đưa vào cơ sở dữ liệu mới mà không cần sự can thiệp của người dùng
- Dữ liệu có thể được thay đổi trong tệp Excel và những thay đổi đó cần được phản ánh tự động trên cơ sở dữ liệu
- Dữ liệu được thay đổi trong cơ sở dữ liệu cần tự động chuyển đến bảng tính Excel, cho mục đích báo cáo hoặc mục đích khác
Cách tốt nhất để đạt được điều này là có kết nối Excel ODBC. Sau khi bạn thiết lập, mọi thứ đều tự động, như đã mô tả ở trên
Điều quan trọng cần lưu ý là trong phần này, chúng tôi sử dụng một sản phẩm thương mại có tên là Connect Bridge. Connect Bridge là một nền tảng tích hợp được phát triển bởi Connecting Software, cho phép bạn kết nối bất kỳ phần mềm nào thông qua trình điều khiển ODBC, trình điều khiển JDBC hoặc Dịch vụ Web. Chúng ta sẽ thấy rằng bạn có thể sử dụng nó để kết nối không chỉ với Excel mà còn với Dynamics 365 và phần mềm khác trong các phần sau. Có bản dùng thử miễn phí, vì vậy bạn có thể dùng thử tất cả những thứ này và xem liệu nó có giúp ích gì trong tình huống cụ thể của bạn không
Dưới đây là quy trình từng bước để tích hợp dữ liệu Excel
- Yêu cầu bản dùng thử miễn phí và cài đặt Connect Bridge
- Chạy Connect Bridge Management Studio
- Thêm tài khoản cho Excel [Tài khoản > Tạo tài khoản]. Chọn Trình kết nối CB Excel và sau đó sử dụng Trình hướng dẫn xác thực để hoàn tất việc tạo tài khoản
- Bấm vào Truy vấn trong menu rồi vào Trình duyệt kết nối. Tìm Trình kết nối CB Excel và mở nó cho đến khi bạn thấy Kết nối mặc định. Nhấp chuột phải vào nó và chọn Nhận chuỗi kết nối. Sau đó sao chép chuỗi kết nối ODBC. Bạn sẽ cần nó hơn nữa cùng
- Sử dụng tùy chọn Truy vấn để kiểm tra một truy vấn sẽ truy cập những gì bạn cần trong Excel. Trước tiên, bạn sẽ cần lấy id của thư mục gốc. Đối với điều đó, hãy mở phần Bảng, nhấp chuột phải vào bảng RootFolder và chọn tùy chọn Tạo Chọn. Khi bạn có id đó, bạn có thể vào bảng FolderItems và tạo một lựa chọn ở đó, sử dụng dưới dạng items_id id của thư mục gốc. Sau đó, bạn nên điều chỉnh truy vấn của mình để có được thông tin mong muốn
- Tạo Máy chủ được liên kết Microsoft SQL Server kết nối với Excel bằng cách sử dụng chuỗi kết nối từ bước 4 và chèn mật khẩu thực được sử dụng cho Connect Bridge. Video hướng dẫn tại https. //youtube. be/rkx8vUITJ_U có thể giúp bạn thực hiện quy trình từng bước.
Xin lưu ý rằng mặc dù bây giờ có vẻ như chúng tôi đang sử dụng bảng tính Excel làm cơ sở dữ liệu và thực hiện truy vấn Excel SQL, nhưng tất cả điều này chỉ là một bản tóm tắt. Nó trông giống như một cơ sở dữ liệu, bạn vận hành nó như thể nó là một cơ sở dữ liệu, nhưng Connect Bridge trên thực tế đang thực hiện rất nhiều công việc nền cho bạn và dịch qua lại từ SQL sang lệnh gọi API
Trình kết nối CB Excel cho phép bạn chèn, cập nhật, xóa và chọn dữ liệu. Bạn cũng có thể sử dụng một số hàm và biểu đồ do Excel cung cấp
hoạt động được hỗ trợ
- Liệt kê các trang tính là một phần của sổ làm việc
- Thêm một trang tính mới
- Nhận một trang tính mới
- Nhận một bảng tính dựa trên tên
- Xóa một trang tính
- Liệt kê các biểu đồ là một phần của trang tính
- Lấy hình ảnh biểu đồ
- Thêm biểu đồ
- Cập nhật biểu đồ
- Cập nhật dữ liệu nguồn biểu đồ
- Lấy danh sách các bảng
- Tạo một bảng
- Cập nhật một bảng
- Lấy danh sách các hàng của bảng
- Lấy danh sách các cột trong bảng
- Thêm một hàng của bảng
- Thêm cột bảng
- Xóa hàng của bảng
Tôi có thể liên kết Excel với Dynamics 365 không?
Bây giờ bạn đã biết cách kết nối Excel với SQL Server, bạn sẽ rất vui khi khám phá ra rằng bạn có thể sử dụng logic tương tự để kết nối với nhiều loại phần mềm kinh doanh
Ví dụ: giả sử bạn muốn kết nối Excel với Dynamics 365. Bạn có thể kết nối Excel với SQL Server theo cách được hiển thị trong phần trước, sau đó kết nối Dynamics 365 với SQL Server theo cách tương tự. Sau đó, dữ liệu có thể chuyển hai chiều từ Excel sang Dynamics hoặc từ Dynamics sang Excel
Đối với điều này, bạn cần sử dụng trình kết nối cho từng phần mềm kinh doanh mà bạn muốn kết nối và tất cả những phần mềm đó sẽ hoạt động cùng nhau bằng cách sử dụng nền tảng tích hợp [Connect Bridge] làm cơ sở. Phần tốt nhất là loại tích hợp này được đảm bảo tiếp tục hoạt động bất kể nâng cấp Excel hay Dynamics. Khi nâng cấp xảy ra, bạn không phải làm gì cả hoặc bạn chỉ cần cài đặt phiên bản tiếp theo của trình kết nối đó. Miễn phí và đơn giản
Làm cách nào để kết nối Excel với danh sách SharePoint?
Kết nối Excel với SharePoint từng khá phổ biến, mặc dù bạn phải chọn xem bạn muốn lấy dữ liệu từ Excel sang SharePoint hay từ SharePoint sang Excel vì nó chỉ hoạt động một chiều. Tuy nhiên, đây là một tính năng gốc trong Excel 2003 và sau đó là một khả năng với Phần bổ trợ Đồng bộ hóa Danh sách SharePoint của Microsoft Excel 2007
Các tùy chọn này hiện không được dùng nữa, nhưng tin vui là nếu bạn muốn kết nối Excel với SharePoint, bạn có thể sử dụng lại Connect Bridge. Bạn sẽ cần trình kết nối CB Excel và trình kết nối CB SharePoint
Sử dụng cách tiếp cận này, bạn có thể có hai chiều miễn là bạn có thể xác định các quy tắc cho những gì sẽ xảy ra trong từng trường hợp xung đột có thể xảy ra
Làm cách nào để kết nối Excel với SQL Server từ xa?
Kết nối Excel với SQL Server bằng cách sử dụng phương pháp Connect Bridge được mô tả trong các chủ đề trước giúp bạn có thể thực hiện điều đó từ xa
Hạn chế duy nhất là một phần của Connect Bridge, cụ thể là trình điều khiển ODBC của Connect Bridge, phải được cài đặt trên cùng một máy có máy chủ SQL. Nếu bạn có thể đảm bảo điều đó, việc thực hiện kết nối từ xa sẽ không thành vấn đề
Mang đi
Nếu công ty của bạn đang tìm cách kết nối thực sự giữa Excel và SQL để khai thác toàn bộ sức mạnh của chúng, hãy xem xét điều này. xuất đơn giản là một cách tốt để bạn bắt đầu, nhưng cuối cùng có thể sẽ không đủ
Xem xét một giải pháp thay thế dài hạn như nền tảng tích hợp Connect Bridge và tích hợp Excel và SQL một cách linh hoạt và tự động
Bạn có tò mò muốn xem tất cả điều này trong hành động?