We heavily use BIML in our data projects for the development of SSIS packages, so BimlExpress is important to us. So far, it looks like BimlExpress does not provide an installation for Visual Studio 2022 yet. For Visual Studio 2019, it can install but will get an error saying "This extension was not loaded because it uses deprecated synchronous autoload APIs". See the screenshot below. **Our Question:** Is there a way we can still use Biml with Visual Studio, better to be one of the recent versions, e.g., 2022 or 2019. **Screenshot:**

Just a quick questions regarding DBAs, recently I was asked about the scope of DBA in terms of SSRS and BI, in your experience who handles and maintain/configure your SSRS, is this part of the DBA or Sysadmin. and for Datawarehouse do we need a DBA to develop that or a DW engineer? Thank you for all who will answer.

I'm new to Microsoft SQL Server Business Intelligence and Analysis Service (but I've been programming for years with SQL Server). Can any one describe Measures and Dimensions in Cubes in simple words (If it's possible with images)?

I have a Spring Boot webapp with a MySQL database. I'm trying to replicate this data into a MSSQL for running reports against. All rows are audited with created_at and modified_at datetimes so i can easily fetch new and changed rows. Problem is when a row is deleted this will not be reflected in the target database. From the top of my head there's a couple of ways to solving this - Triggers and a "history" table. Whenever there's an update to the table, it inserts a copy of the row and a column describing the action taken. - A deleted bit. 1 is a deleted row (this will have me redo all queries though, and now i see the advantage of having a class in between the controller and the repository) - And the very verbose method of querying all rows and check what's missing (will not do) Is there a *right way* of handling this?

My original query worked perfectly via SQL Developer: select * from [table] where DT_CURRENT_DT = '15-OCT-18' However when ran via freehand SQL in SAP BO I got no results. After running the query with no conditions I noticed that BO re-formats the date to 10/15/18

I realized that my company uses an ELT (extract-load-transform) process instead of using an ETL (extract-transform-load) process. What are the differences in the two approaches and in which situations would one be "better" than the other? It would be great if you could provide some examples.

I'm a newbie to data warehousing and I've been reading articles and watching videos on the principles but I'm a bit confused as to how I would take the design below and convert it into a star schema. In all the examples I've seen the fact table references the dim tables, so I'm assuming the questionId and responseId would be part of the fact table? Any advice would be much appreciated.

Take a SQL RDBMS data warehouse -- typical facts and dimensions type layout. Say you want Orders x Country. Maybe a date field, an orders field, a country field. And then if you want a report/ software that slices by Continent? Easy country x continent dimensional table, right? But what's the proper architecture for overlapping dimensions? For instance maybe you want "Greater Country" that includes the UK, British Isles, Island of Ireland -- ... these are larger groups that contain overlapping smaller pieces. Northern Ireland for instance is a component of both the UK and Island of Ireland. What's the best architecture so that if an end-user select "UK" or "Island of Ireland" -- that Northern Ireland pops up? Without duplication? I guess there are several ways to do this --- I don't actually care about the UK ha it's just an example -- You have a small component Dimension A. You have a larger grouping Dimension B but A is not unique to a single B. Every "cube" based reporting system is anti-thetical to this, but there are use cases. Nor do you want to create tons of dimensions. Is the best method simply a dimensions table that shows full membership -- aka Northern Ireland - UK, Northern Ireland - Isle of Ireland .... then do a join, return distinct? Doesn't seem efficient but maybe that's the best way.

I read SQL Server 2017 will now include PowerBI Server. They also moved SSRS to a different installer, so it will not come packaged in the original SQL Server installation. Does this mean Microsoft will eventually try to deprecate SSRS? Should our team try to build new reports in PowerBI and transition the previous SSRS reports?

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] DATETIME [Date] DATE [DayOfWeek_Number] TINYINT [DayOfWeek_Name] VARCHAR(9) [DayOfWeek_ShortName] VARCHAR(3) [Week_Number] TINYINT [Fiscal_DayOfMonth] TINYINT [Fiscal_Month_Number] TINYINT [Fiscal_Month_Name] VARCHAR(12) [Fiscal_Month_ShortName] VARCHAR(3) [Fiscal_Quarter] TINYINT [Fiscal_Year] INT [Calendar_DayOfMonth] TINYINT [Calendar_Month Number] TINYINT [Calendar_Month_Name] VARCHAR(9) [Calendar_Month_ShortName] VARCHAR(3) [Calendar_Quarter] TINYINT [Calendar_Year] INT [IsLeapYear] BIT [IsWeekDay] BIT [IsWeekend] BIT [IsWorkday] BIT [IsHoliday] BIT [HolidayName] VARCHAR(255) I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive. ie. parameters '2014-01-01' and '2014-01-03' would return: 2014-01-01 2014-01-02 2014-01-03 I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions. The fiscal data contains slightly more logic, some of which is dependant on each other. For example: Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3 Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6 I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function. I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered. What is the best way to write this in SQL? Currently its like this: SELECT [Date], CAST([Date] AS DATE) AS [Date], DATEPART(W,[Date]) AS [DayOfWeek_Number], -- First day of week is sunday DATENAME(W,[Date]) AS [DayOfWeek_Name], SUBSTRING(DATENAME(DW,[Date]),1,3) AS [DayOfWeek_ShortName], DATEPART(WK, [Date]) AS [WeekNumber], DATEPART(M, [Date]) AS [Calendar_Month_Number], DATENAME(M, [Date]) AS [Calendar_Month_Name], SUBSTRING(DATENAME(M, [Date]),1,3) AS [Calendar_Month_ShortName], DATEPART(QQ, [Date]) AS [Calendar_Quarter], YEAR([Date]) AS [Calendar_Year], CASE WHEN ( (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0) OR (YEAR([Date]) % 400 = 0) ) THEN 1 ELSE 0 END AS [IsLeapYear], CASE WHEN ( DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7 ) THEN 0 ELSE 1 END AS [IsWeekDay] FROM [DateListForRange] ('2014-01-01','2014-01-31') If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates. Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.

I'm learning dimensional modeling and have a question. I understand those basic examples such as a FactSale has DimItem, DimLocation. But when it comes to complicated cases with many-to-many relationships, how should I design the dimension table? For example, in an example of Author-Book-Reviewer, a book can be written by multiple authors and can be reviewed by multiple reviewers; One author can write multiple books; One reviewer can review multiple books. In a relational model it looks like this  But how do I transform this (OLTP) model into a (OLAP) star schma model? Can I make "Book" another dimension? and we have DimBook, DimReviewer, DimAuthor?  But if I model it like this, the single fact seems lost its meaning. Because the same book id can appear multiple times, and the one author and one reviewer doesn't make sense in the fact table. So if I wanted to make Book a fact, should I use "Bridge table" technique from Ralph Kimball's book?

Where can I find about tutorial of datamining in SQL server 2012 or 2008 R2? The purpose is to improve my knowledge in datamining.

I would like to know if there are specific recommended settings when creating a new DB (in SQL Server) which will serve as a staging area DB for BI data flow. In terms of DB use behavior this means: - quite large tables (from above a mln rows) - fast growing tables (1-2 mln rows every 4-6 months) - massive insertion/deletion operation made by ETL There is something that need to be changed in the default DB properties? Can you specify it carefully? Thank you

One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article : https://www.holistics.io/blog/the-three-types-of-fact-tables/ . Imagine we have an accumulating fact table which has three important date column in it 'Order_Datekey , Manufacturing_Datekey , Ship_Datekey' When the table is being inserted for the first time ,there is no data for columns Manufacturing_Datekey and Ship_Datekey'. We only know the date in which the product has been ordered (Order_date). What I need to know is that how we can handle null values for Date columns ? The important point I read in Kimbal book is that null should be avoided in a foreign key column. 'Referential integrity is violated if you put a null in a fact table column declared as a foreign key to a dimension table' On the other hand we don't have any value for these two date columns at first. What do you suggest? Thanks in advance

TL;DR; Is the difference between ETL and ELT just whether or not the transformed data is a concretion vs abstraction before being loaded into the warehouse? So forgive me the broadness of the subject title, as it glosses over details, but I'm trying to understand how ETL and ELT differ in a definitive way. They seem very similar, to the point that you could describe either as ELTL. In both cases you start with raw data and end up with concrete transformed data in tables. It seems to be what happens in between is the difference, which can take many forms depending on the number/complexity of transformations from start to end state. From what I know of ETL, the steps are as follows: 1. Extract the data from source systems and load into raw tables 2. Transform the data from raw tables into staging tables 3. Load the data from the staging ***tables*** into your warehouse In some definitions of ELT I've seen, the steps seem to be: 1. Extract the data from source systems and load into raw tables 2. Transform the data notionally from the raw tables by creating views that represent the staging tables 3. Load the data from the staging ***views*** into your warehouse But then I look at other articles about ELT, and it seems to be: 1. Extract the data from source systems and load into raw tables 2. Transform the data notionally from the raw tables by creating views that represent the staging tables 3. Congratulations, no more hard duplication of data from raw to transformed state, everything is views! Users just fire off their query and views nested *n*-levels deep instantly* return data! *Results may vary, depending on whether you have millions of dollars to throw at cloud processing and/or large-scale quantum computing becomes available

First time posting but have been a reader for over 10 years. I have a need to show when a loan was paid off regardless of the rolling balance from refinancing/taking out additional loans. In the other column I want to show pay down balance like 950.00, 850.00, 800.00, 720.00 until I get to zero balance for the LOAN_ID 215, After which I want to then apply remaining payments to LOAN_ID 431 I'm using SQL Server 2005/2008. Here is my table: CUST_ID LOAN_ID PMNT_ID PMNT_AMT PMNT_DT LOAN_AMT LOAN_FUND_DT PMNT_RUN_TOT 1155 215 100 100.00 02/15/2015 1050.00 01/15/2015 100.00 1155 215 101 100.00 03/15/2015 1050.00 01/15/2015 200.00 1155 215 102 50.00 03/31/2015 1050.00 01/15/2015 250.00 1155 215 103 90.00 04/15/2015 1050.00 01/15/2015 340.00 1155 215 104 150.00 04/25/2015 1050.00 01/15/2015 490.00 1155 215 105 120.00 05/15/2015 1050.00 01/15/2015 610.00 1155 431 106 100.00 05/25/2015 2100.00 05/20/2015 710.00 1155 431 107 100.00 06/15/2015 2100.00 05/20/2015 810.00 1155 431 108 100.00 06/30/2015 2100.00 05/20/2015 910.00 1155 431 109 100.00 07/15/2015 2100.00 05/20/2015 1010.00 1155 431 110 100.00 08/15/2015 2100.00 05/20/2015 1110.00 1155 431 111 100.00 09/15/2015 2100.00 05/20/2015 1210.00 1155 431 112 100.00 09/30/2015 2100.00 05/20/2015 1310.00 1155 431 113 100.00 10/15/2015 2100.00 05/20/2015 1410.00 The first loan was paid off with PMNT_ID 110 and 60.00 was rolled over into the pay down of 2100.00 loan. Here is how the output should look cust_id|loan_id | loan_amount | Loan Bal |paid_off_date ---------------------------------------------------- 1155 | 215| 1050 | 0.00 |2015-08-15 1155 | 431| 2100 | 1800.00 |null Any suggestions on how to show this in another column called "Bal_Pay_Down" ?

Which websites, books etc. can you recommend me to improve my basic knowledge in Microsoft Business Intelligence 2008, SSAS, SSIS and SSRS? Please remember that I'm a newbie in technical BI tool. If possible, it would be great to retrieve any material for task work that would be similiar as doing lab task at university. // Fullmetalboy

We've been using BI software and a repository database that are installed on Oracle Enterprise 11gR2. Some of these batch reports will try to access a database table which may still be locked. How can I find out if an Oracle table is locked or not? Is there any SQL statement that displays like history details to analysis?

This may not be the appropriate forum, but its the closest I could find. I write a lot of source to target mappings for work. These documents just describe the operations/logic/transformations/etc. needed to get the data from A to B. I find myself phrasing the same thing different ways, or sometimes the descriptions get convoluted if the logic is complex. Are there any best practices for writing the transformation logic for relational databases? For example, how to say 'Join tables A,B, and C for each combination of fields D,E, and F select the maximum value between fields G and H.' I feel that somebody has probably standardized the natural language for relational database transformation logic, but I can't find anything to that effect.

I'm a developer with apps backed with SQL databases. I want to give my CEO access through BI self-serving tools such as [PowerBI](https://powerbi.microsoft.com) or [Tableau](https://www.tableau.com/) . My issue is that the affordable tools all seem to require for someone to define the model in an interactive application leaving no means of automization. In practice, that means that every time I add some column somewhere I need to fire up some sluggish desktop application, manually add the column in the model there too and then trigger some deployment process. I'm a developer, I need to automate such things in order to not be overwhelmed with complexity. I've looked at PowerBI's file format, but it's binary. I've looked at both theses product's REST APIs, but all they allow is upload that binary and undocumented file format. I'm looking for - a way to create a model programmatically with these tools or - affordable alternatives that allow that. I'm otherwise satisfied with both these tools. I also had a look at [Looker](https://looker.com/) , but that already costs thousands of dollars per month.