Systemd is providing limited compatability for sysv scripts. Traditional init scripts will in this case get wrapped by a generated unit file via systemd-sysv-generator, once systemd has found such a script. But this won't happen automatically and that's why you won't be able to start Cassandra using systemctl start cassandra after installing the RPM.
The most straight forward way to make this happen is by running systemctl daemon-reload. We could either run the command from the post-install script or ask the user to do this. My suggestion would be to add installation instructions to the download page specifically for RPM based packages that would stick with init.d terminology and ask the user to start Cassandra with service cassandra start. This will transperantley trigger systemctl daemon-reload and start the service.
We should also mention to enable starting Cassandra on boot (if desired) by using chkconfig cassandra on.