SQL Server connector for pivot tables, charts

SQL-compatible data source supports official SQL Server client which is suitable for connecting to Azure SQL as well.

SeekTable is great if you need to give non-technical users the ability to query and visualize SQL Server data in form of pivot tables or charts, at the same time leave an ability to use full power of SQL behind the scene for cube's creators. Also SeekTable may be considered as a modern lightweight replacement for SSRS tabular reports.

• SQL Server is used as a live data source: no need to refresh reports as users always get actual data (direct query mode).
• Reports may be shared with the team or published for embedding (or sharing via public link). Users can subscribe to reports in a self-service manner.
• The cube itself may be shared too: users can create own reports without having direct access to SQL Server database.
• Web API can be used for automated reports generation (export to Excel, multipage PDFs).

How to configure SQL Server cube

1. Click the "Connect to Database" to open a new data cube configuration form.
2. Choose Data Source Type: SQL-compatible database (this opens SQL Settings section).
3. Choose Database Connector: MS SQL Server, Azure SQL
   
4. Connection String should be a valid connection string for Microsoft SqlClient driver. For example:

   Data Source=hostName;Database=db;User=user;Password=password;

   Data Source or Server	this setting specifies the name of the server and the SQL Server instance on the server. The port number can be specified after the server name (optional): ServerName\Instancename,portNumber. Do not use ".", "(local)", "localhost" or LAN hostname as a server name until you deployed a self-hosted SeekTable instance; cloud SeekTable can connect only to public IP address or domain name of the server. If you omit the instance name, the default instance is assumed.
   Database	specifies the name of the database in the SQL Server instance.
   User	SQL Server login account. It is recommended to create special user with read-only rights.
   Password	The password for the SQL Server account logging on.

   Note: do not specify "Integrated Security", this option is irrelevant in SeekTable environment.

5. Configure other required settings as described in SQL-compatible data source. As a minimum you need to specify Select Query (which determine's the dataset) and keep Infer dimensions and measures by dataset checked.

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

1. add new dimension with Type=Field
2. fill Name with some unique value: say, "date_column_year"
3. add one Parameter which should be a date-part SQL Server expression:
   • For year: YEAR(date_column)
   • For month: MONTH(date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
   • For day: DAY(date_column)
   • For day-of-week: DATEPART(dw , date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
   • For quarter: DATEPART(q , date_column)
   • For week-of-year: DATEPART(iso_week, date_column)